Finding duplicates

Here is what I have so far. Would anyone know if this query picks up all id
numbers that have the same amount on a certain settlment batch number? They
are also both credit sales that were approved. Thanks for any info.
Use WinPayment
GO
SELECT
pos_condition_code,
convert(char(11), retrieval_reference_number) RR,
message_type,
authorization_identification,
convert(char(8), card_acceptor_identification) SN,
convert(char(25), transaction_name) TransactionName,
isnull(convert(char(2), id_code_1), ' ') ID,
convert (char (20), id_number_1)CardNumber,
convert(char(20), time_stamp)POSTime,
convert (char (20), transmission_date_and_time)TransmissionT
ime,
convert(char(2), response_code) RC,
isnull(convert(char(2), host_response_code), '') HRC,
convert(char(20), host_response_string)Message,
convert(char(7), stan) STAN,
convert(char(12), transaction_amount) Amount,
settlement_data
FROM
financial_message as Sale (NOLOCK)
Where
settlement_batch_number = '773'
AND
transaction_name = 'Credit Sale'
And
host_response_string = 'Successful Approval'
AND EXISTS
(Select *
FROM financial_message AS Rev
WHERE Rev.id_number_1 = Sale.id_number_1
AND Rev.transaction_amount = Sale.transaction_amount
AND Rev.settlement_batch_number = sale.settlement_batch_number
AND Rev.transaction_name = sale.transaction_name
And Rev.host_response_string = sale.host_response_string)
order by
time_stampYour filter is based on this
Rev.id_number_1 = Sale.id_number_1
AND Rev.transaction_amount = Sale.transaction_amount
AND Rev.settlement_batch_number = sale.settlement_batch_number
AND Rev.transaction_name = sale.transaction_name
And Rev.host_response_string = sale.host_response_string
Thus, only row(s) that satisfy the requirements would be returned.
-oj
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EF69A8A-0019-4FF7-89BD-A31499D3C8EC@.microsoft.com...
> Here is what I have so far. Would anyone know if this query picks up all
> id
> numbers that have the same amount on a certain settlment batch number?
> They
> are also both credit sales that were approved. Thanks for any info.
> Use WinPayment
> GO
> SELECT
> pos_condition_code,
> convert(char(11), retrieval_reference_number) RR,
> message_type,
> authorization_identification,
> convert(char(8), card_acceptor_identification) SN,
> convert(char(25), transaction_name) TransactionName,
> isnull(convert(char(2), id_code_1), ' ') ID,
> convert (char (20), id_number_1)CardNumber,
> convert(char(20), time_stamp)POSTime,
> convert (char (20), transmission_date_and_time)TransmissionT
ime,
> convert(char(2), response_code) RC,
> isnull(convert(char(2), host_response_code), '') HRC,
> convert(char(20), host_response_string)Message,
> convert(char(7), stan) STAN,
> convert(char(12), transaction_amount) Amount,
> settlement_data
> FROM
> financial_message as Sale (NOLOCK)
> Where
> settlement_batch_number = '773'
> AND
> transaction_name = 'Credit Sale'
> And
> host_response_string = 'Successful Approval'
> AND EXISTS
> (Select *
> FROM financial_message AS Rev
> WHERE Rev.id_number_1 = Sale.id_number_1
> AND Rev.transaction_amount = Sale.transaction_amount
> AND Rev.settlement_batch_number = sale.settlement_batch_number
> AND Rev.transaction_name = sale.transaction_name
> And Rev.host_response_string = sale.host_response_string)
> order by
> time_stamp