Case Statements in MS 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

Other Forums

MS Access

Case Statements in MS query

Author

Topic

Elaussie09
Starting Member

3 Posts

Posted - 2008-08-08 : 06:38:10

Hi, i am not sure whether this is the right place to post this query, but i was hoping on of you could help. I am try to convert an SQL case statement to a MS Query case statement, but cannot get it to work.

SELECT AACT.FormatCode, AACT.AccountName,
Sum(Case When OJDT.RefDate <= '2008-01-31' Then (AJD1.Debit-AJD1.Credit) End) As Month1,
Sum(Case When OJDT.RefDate <= '2008-02-29' Then (AJD1.Debit-AJD1.Credit) End) As Month2,
Sum(Case When OJDT.RefDate <= '2008-03-31' Then (AJD1.Debit-AJD1.Credit) End) As Month3,
Sum(Case When OJDT.RefDate <= '2008-04-30' Then (AJD1.Debit-AJD1.Credit) End) As Month4,
Sum(Case When OJDT.RefDate <= '2008-05-31' Then (AJD1.Debit-AJD1.Credit) End) As Month5,
Sum(Case When OJDT.RefDate <= '2008-06-30' Then (AJD1.Debit-AJD1.Credit) End) As Month6,
Sum(Case When OJDT.RefDate <= '2008-07-31' Then (AJD1.Debit-AJD1.Credit) End) As Month7,
Sum(Case When OJDT.RefDate <= '2008-08-31' Then (AJD1.Debit-AJD1.Credit) End) As Month8,
Sum(Case When OJDT.RefDate <= '2008-09-30' Then (AJD1.Debit-AJD1.Credit) End) As Month9,
Sum(Case When OJDT.RefDate <= '2008-10-31' Then (AJD1.Debit-AJD1.Credit) End) As Month10,
Sum(Case When OJDT.RefDate <= '2008-11-30' Then (AJD1.Debit-AJD1.Credit) End) As Month11,
Sum(Case When OJDT.RefDate <= '2008-12-31' Then (AJD1.Debit-AJD1.Credit) End) As Month12

FROM MSPLC.dbo.AACT AACT, MSPLC.dbo.AJD1 AJD1, MSPLC.dbo.OJDT OJDT

WHERE OJDT.TransId = AJD1.TransId AND AACT.AcctCode = AJD1.Account

Group By AACT.FormatCode, AACT.AccountName

Thanks in advance

Elaussie09
Starting Member

3 Posts

Posted - 2008-08-08 : 06:59:11

Or maybe an if statement would work if thats easier

Thanks

georgev
Posting Yak Master

122 Posts

Posted - 2008-08-11 : 04:30:27

http://www.dbforums.com/showthread.php?t=1632866

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources