Stuck on 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

SQL Server 2005 Forums

Transact-SQL (2005)

Stuck on Query

Author

Topic

bdensmore
Starting Member

1 Post

Posted - 2009-03-30 : 21:50:14

Hi,
I have a view with a column named Invoice_State. This Column can 3 different values. "Late", "On Time" or "Early", but there won't necessarily always be a value. I need to get a count of records for each Invoice_State

I'm trying to write the query as one query rather than multiple queries so that I can display it in a chart. For some reason I'm really struggling to come up with the logic on this one, even though I think it should be pretty easy.

Basically I need to do something similar to below but for all three states:
Select count(BUYER_PROFILEID) as total, INVOICE_STATE
From Invoice_Performance
Where INVOICE_STATE = 'Early'
And Buyer_ProfileId = 'buyer'
And TransactionDate Between '1/1/2009' AND '12/31/2009'

Can anyone give me some ideas on solving this one?

Thanks,
Ben

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-30 : 22:42:51

try like this
Select count(BUYER_PROFILEID) as total, CASE WHEN INVOICE_STATE = 'Early' THEN 'Early'
WHEN INVOICE_STATE = 'Late' THEN 'Late'
WHEN INVOICE_STATE = 'On Time' THEN 'On Time' END AS INVOICE_STATE
From Invoice_Performance
Where
Buyer_ProfileId = 'buyer'
And DATEADD(d,DATEDIFF(d,0,TransactionDate),0) Between '1/1/2009' AND '12/31/2009'
GROUP BY
CASE WHEN INVOICE_STATE = 'Early' THEN 'Early'
WHEN INVOICE_STATE = 'Late' THEN 'Late'
WHEN INVOICE_STATE = 'On Time' THEN 'On Time' END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources