how to get this compare query - 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

General SQL Server Forums

New to SQL Server Programming

how to get this compare query

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-09 : 14:44:20

i have a table Called DailyTrans with columns(Acct#,TransDate, City, State, Amount)
as below (sample data...)

Acct#,TransDate, City, State, Amount
1001,Nov/01/2007,Herndon,VA,-120
1002,Nov/10/2008,Borofield,VT,-393
1004,Nov/01/2008,NYC,NY,190
1002,Nov/17/2007,Jacson,NJ,780
1002,Nov/21/2007,Elizabeth,NY,8493
1004,Nov/28/2007,Norway,WI,340
1002,Nov/09/2007,Dc,VA,-340
.....
.....
....
1002,Nov/29/2007,Metropk,OH,-540
.....
...

how can i get information as below
account#,Nov 2007 Debits, Nov 2008 Debits, Nov 2007 Credits,Nov 2008 Credits

here Nov 2007 debits (all negative transaction between nov 1 2007 and nov 30 2007)
here Nov 2008 debits (all negative transaction between nov 1 2008 and nov 30 2008)
here Nov 2007 credits (all +ve transaction between nov 1 2007 and nov 30 2007)
here Nov 2008 credits (all +ve transaction between nov 1 2008 and nov 30 2008)

Please help me

Thanks in advance
Best Regards
asini

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 14:52:15

[code]SELECT SUM(CASE WHEN Amount < 0 AND TransDate BETWEEN '20071101' AND '20071130' THEN Amount ELSE 0 END),
SUM(CASE WHEN Amount < 0 AND TransDate BETWEEN '20081101' AND '20081130' THEN Amount ELSE 0 END),
SUM(CASE WHEN Amount > 0 AND TransDate BETWEEN '20071101' AND '20071130' THEN Amount ELSE 0 END),
SUM(CASE WHEN Amount > 0 AND TransDate BETWEEN '20081101' AND '20081130' THEN Amount ELSE 0 END)
FROM DailyTrans
WHERE TransDate BETWEEN '20071101' AND '20071130'
OR TransDate BETWEEN '20081101' AND '20081130'[/code]

E 12°55'05.63"
N 56°04'39.26"

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-09 : 15:43:48

Hello Peso,

Thank you very much i worked on it, it is working as it what i am expecting

really appreciate you My Master

Best Regards
asin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources