Help needed in resolving the below SQL

Question

Help needed in resolving the below SQL

grkanth81

Ten Centuries

Points: 1194
More actions
October 28, 2014 at 8:36 pm

#294581

Declare @new table
(employee_id int,
invoice_date datetime,
invoice_id int,
invoice_amount money
)
INSERT into @new
Select 100,'2013-06-27',824,6700
union
Select 100,'2013-06-27',824,-6700
union
Select 100,'2013-10-31',839,4800
union
Select 100,'2013-10-31',839,-4800
union
Select 100,'2014-03-31',857,9400
union
Select 100,'2014-08-28',857,-9400
union
Select 100,'2014-08-18',868,16900
union
Select 100,'2014-08-20',868,-16900
Select * from @new
order by invoice_id,invoice_amount desc
When i run the above query the result set would be:
employee_idinvoice_dateinvoice_idinvoice_amount
1002013-06-27 00:00:00.0008246700.00
1002013-06-27 00:00:00.000824-6700.00
1002013-10-31 00:00:00.0008394800.00
1002013-10-31 00:00:00.000839-4800.00
1002014-03-31 00:00:00.0008579400.00
1002014-08-28 00:00:00.000857-9400.00
1002014-08-18 00:00:00.00086816900.00
1002014-08-20 00:00:00.000868-16900.00
As you can see from the above result set, invoice id's 824 and 839 are reversed on the same date.
So, I would like to write a SQL query to get the information of the invoices that are reversed on different date. (i.e i would like to get invoices 857 and 868 details as output)
Thanks,
grkanth81

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

I think I had to read this about 5 times before i figured out what you were asking... Took a bit to realize that the second invoice is a refund.

How about:

SELECT invoice_id

, IssueDate

, ReturnDate

, Amount

FROM

(SELECT invoice_id

, MIN(invoice_date) AS IssueDate

, MAX(invoice_date) AS ReturnDate

, ABS(invoice_amount) AS Amount

FROM #invoices

GROUP BY invoice_id, ABS(Invoice_amount)) x

WHERE x.ReturnDate>x.issueDate;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Maybe you're looking for something like this:

Select *

from @new n

WHERE invoice_id IN( SELECT i.invoice_id

FROM @new i

GROUP BY i.invoice_id

HAVING MAX( i.invoice_date) > MIN(i.invoice_date))

order by invoice_id,invoice_amount desc;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2