use SUM effectively - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

use SUM effectively

Author

Topic

gemini_shooter
Starting Member

12 Posts

Posted - 2008-11-19 : 01:48:24

This is a sample dataset of what I have:

entry no entry type amount open
23 payment -90 No
23 invoice 100 Yes
23 credit memo -5 No
24 payment -50 No
24 invoice 50 No

and

this is what I am trying to create:

entry no. entry type amount remaining amount open
23 invoice 100 5 Yes
24 invoice 50 0 No

I add a new column on the fly and do a sum (payment + invoice + credit) for a single entry no.

Can someone please help me with the TSQL for this ... I am totally confused

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 02:01:33

[code]SELECT entry_no,
MAX(CASE WHEN entry type ='invoice' THEN entry_type ELSE NULL END) AS entry_type,
MAX(CASE WHEN entry type ='invoice' THEN amount ELSE NULL END) AS amount,
MAX(CASE WHEN entry type ='payment' THEN amount ELSE NULL END) +MAX(CASE WHEN entry type ='invoice' THEN amount ELSE NULL END) +MAX(CASE WHEN entry type ='credit' THEN amount ELSE NULL END) AS remaining_amount,
MAX(CASE WHEN entry type ='invoice' THEN open ELSE NULL END) AS open
FROM YourTable
GROUP BY entry_no[/code]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-19 : 02:05:53

CREATE TABLE #TEMPTABLE (Nos INT, Etype VARCHAR(10),amount INT)

INSERT INTO #TEMPTABLE
SELECT 23,'payment',-90
UNION ALL
SELECT 23,'invoice',100
UNION ALL
SELECT 23,'credit',-5
UNION ALL
SELECT 24,'payment',-50
UNION ALL
SELECT 24,'invoice', 50

SELECT * FROM #TEMPTABLE

SELECT Nos,Etype,amount,(SELECT sum(amount) FROM #TEMPTABLE
GROUP BY Nos HAVING Nos=t.Nos) AS remaining_amount,
CASE WHEN (SELECT sum(amount) FROM #TEMPTABLE
GROUP BY Nos HAVING Nos=t.Nos) =0 THEN 'NO' ELSE 'YES' END AS 'open'
FROM #TEMPTABLE t
WHERE Etype ='invoice'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources