August 19, 2009 at 7:16 am
Hello,
I'm just starting out with SQL Server reporting services and already have a question.
First of all some more info on my data.
I want to base my query on the table called Shipments. In this data I have a field called Status. This can be
0 new, 1 busy, 2 finished.
Now I want to create a chart in SSRS that will show the number of shipments per day and also how many were finished per day. Basically the X axis would be the dates and the Y axis would contain to bars. One with status 0,1,2 and one with status 2.
Because I can only use fields coming from one DataSet, I have to create a query for this that would give me all the necessary data.
This is how I would select all the shipments / day. But how do I include the ones that have status 2.
select COUNT(id) as 'AllShipments', DeliveryDate from Shipment group by DeliveryDate order by DeliveryDate ASC;
Kinda stuck,
Can anyone help me out?
August 19, 2009 at 11:25 pm
You can use a CASE statement in your query:
select COUNT(id) as 'AllShipments', DeliveryDate,
sum(case when status = 2 then 1 else 0 end) as 'FinishedShipments'
from Shipment
group by DeliveryDate
order by DeliveryDate ASC;
Hope that helps
-Marianne
August 20, 2009 at 6:12 am
Fantastic, that did it!
Never thought of a case, was messing with left outer joins.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply