CODEWITHSUNDEEP
sqlcase
Trevor
Trevor

Reputation: 16116

SQL CASE Statement data organization into one query

I have the following query where I am breaking down Accidents by date.

select t.range as [Date Range], count(*) as Accidents
from (
    select case
        when (MONTH(accidentdatetime) >= 6 AND YEAR(accidentdatetime) = 2013) and (MONTH(accidentdatetime) <= 12 AND YEAR(accidentdatetime) = 2013) then 'Other'
        when MONTH(accidentdatetime) = 1 AND YEAR(accidentdatetime) = 2014 then 'January'
        when MONTH(accidentdatetime) = 2 AND YEAR(accidentdatetime) = 2014 then 'Feburary'
        when MONTH(accidentdatetime) = 3 AND YEAR(accidentdatetime) = 2014 then 'March'
        when MONTH(accidentdatetime) = 4 AND YEAR(accidentdatetime) = 2014 then 'April'
        when MONTH(accidentdatetime) = 5 AND YEAR(accidentdatetime) = 2014 then 'May'
        end as range
    from Accidents
    where accidentdatetime between '06/30/2013' and '05/31/2014'
    ) t
group by t.range

enter image description here

I am trying to comprehend how I can break this down further to where I can further specify types of accidents based on an OSHA field in the database. Something like:

select t.range as [Date Range],count(*) as [Accidents], (SELECT count(*) FROM Accidents WHERE OSHAClassificationID IN (2,4)) as [Lost Time]
from (
    select case
        when (MONTH(accidentdatetime) >= 6 AND YEAR(accidentdatetime) = 2013) and (MONTH(accidentdatetime) <= 12 AND YEAR(accidentdatetime) = 2013) then 'Other'
        when MONTH(accidentdatetime) = 1 AND YEAR(accidentdatetime) = 2014 then 'January'
        when MONTH(accidentdatetime) = 2 AND YEAR(accidentdatetime) = 2014 then 'Feburary'
        when MONTH(accidentdatetime) = 3 AND YEAR(accidentdatetime) = 2014 then 'March'
        when MONTH(accidentdatetime) = 4 AND YEAR(accidentdatetime) = 2014 then 'April'
        when MONTH(accidentdatetime) = 5 AND YEAR(accidentdatetime) = 2014 then 'May'
        end as range
    from Accidents
    where accidentdatetime between '06/30/2013' and '05/31/2014'
    ) t
group by t.range

enter image description here

But my attempt to create another column specifying just a type of accident is not filtered by date and is just displaying the count of all the Lost Time accidents in the database as per my query..

Is there a way to filter by date range and accident type in one query?

Upvotes: 0

Views: 72

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

You need to return a flag to indicate if the record is within your osha range but inside of the month filter. Then sum up the records with a 1 to get the count within your subset of codes.

select t.range as [Date Range],count(*) as [Accidents], 
    [Lost Time]=sum(OSHAFlag)
from (
    select 
       OSHAFlag=case when Accidents.OSHAClassificationID IN(2,4) THEN 1 ELSE 0 END,
       case
        when (MONTH(accidentdatetime) >= 6 AND YEAR(accidentdatetime) = 2013) and (MONTH(accidentdatetime) <= 12 AND YEAR(accidentdatetime) = 2013) then 'Other'
        when MONTH(accidentdatetime) = 1 AND YEAR(accidentdatetime) = 2014 then 'January'
        when MONTH(accidentdatetime) = 2 AND YEAR(accidentdatetime) = 2014 then 'Feburary'
        when MONTH(accidentdatetime) = 3 AND YEAR(accidentdatetime) = 2014 then 'March'
        when MONTH(accidentdatetime) = 4 AND YEAR(accidentdatetime) = 2014 then 'April'
        when MONTH(accidentdatetime) = 5 AND YEAR(accidentdatetime) = 2014 then 'May'
        end as range
    from Accidents
    where accidentdatetime between '06/30/2013' and '05/31/2014'
    ) t
group by t.range

Upvotes: 1

Related Questions