Sum with dates in Case Satement

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

SQL Server Development (2000)

Sum with dates in Case Satement

Author

Topic

cardgunner

326 Posts

Posted - 2007-09-05 : 09:34:35

Good Morning.

I'm trying to get my bosses an open invoices list.
They want to know by customer, what their balances <30, 30<>60, 60<>90, >90.

I would like to use this:


Select invoice.custid, 
       case when datediff(d,invoice.dued,getdate())<'30' then sum(invoice.balc) else '0' end as 'Less then 30',
       case when datediff(d,invoice.dued,getdate())between '30' and '59' then sum(invoice.balc) else '0' end as '30 to 60',
       case when datediff(d,invoice.dued,getdate())between '60' and '90' then sum(invoice.balc) else '0' end as '60 to 90',
       case when datediff(d,invoice.dued,getdate())>'90' then sum(invoice.balc) else '0' end as 'more then 90'
from invoice
group by invoice.custid

where the results would be 

custid    Less then 30    30 to 60     60 to 90   more then 90
145          1234.00        24581.00   12456.00       .00

however I get the error
Server: Msg 8120, Level 16, State 1, Line 2
Column 'invoice.dued' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And when I add it to the group by it then seperates all the sums. Where it will sum all the balc for that specific date as a record in whichever column it would fall in. You know what I mean?

Any help would be great

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 09:43:41

[code]SELECT custid,
sum(case
when datediff(d, dued, current_timestamp) < 30 then balc
else 0
end) as 'Less then 30',
sum(case
when datediff(d, dued, current_timestamp) between 30 and 59 then balc
else 0
end) as '30 to 60',
sum(case
when datediff(d, dued, current_timestamp) between 60 and 90 then balc
else 0
end) as '60 to 90',
sum(case
when datediff(d, dued, current_timestamp) > 90 then balc
else 0
end) as 'more then 90'
from invoice
group by custid[/code]

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-05 : 09:52:16

Peso,

Thank you very much. I knew it had to be something simple. Thank you again. May I ask what current_timestamp is. I know there is a timestamp function in SQL although I have never used it. Is there a reason to use it or a time where I should use timestamp?

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 10:01:19

GETDATE() and CURRENT_TIMESTAMP are the same. I prefer CURRENT_TIMESTAMP since I get less paranthesis to write.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources