Post reply

Selecting data from table with multiple statuses

  • August 19, 2009 at 7:16 am

    #214007

    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

    #1041941

    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

    #1042044

    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