Help needed in resolving the following SQL query..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed in resolving the following SQL query..

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2014-10-28 : 22:33:17

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_id invoice_date invoice_id invoice_amount
100 2013-06-27 00:00:00.000 824 6700.00
100 2013-06-27 00:00:00.000 824 -6700.00
100 2013-10-31 00:00:00.000 839 4800.00
100 2013-10-31 00:00:00.000 839 -4800.00
100 2014-03-31 00:00:00.000 857 9400.00
100 2014-08-28 00:00:00.000 857 -9400.00
100 2014-08-18 00:00:00.000 868 16900.00
100 2014-08-20 00:00:00.000 868 -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,
raaj

AASC
Starting Member

24 Posts

Posted - 2014-10-29 : 02:42:48

@Raj This may help you.

SELECT inv.employee_id,
inv.invoice_date,
inv.invoice_id,
inv.invoice_amount,
revrinv.invoice_amount ReversedAmount
FROM @new inv
INNER JOIN @new revrinv ON inv.invoice_id = revrinv.invoice_id
AND inv.invoice_amount = -(revrinv.invoice_amount)
AND inv.invoice_date = revrinv.invoice_date
AND inv.employee_id = revrinv.employee_id
WHERE inv.invoice_amount>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources