CODEWITHSUNDEEP
sqlsql-serverdatabase
STampa
STampa

Reputation: 341

SQL Query to show the same data but based on different dates in the same view

I have an SQL query that I use to show the # of work orders created by day within a designated date range. It works well when I query a single date range, such as 3/1/2016 to 3/31/2016.

SELECT DATEADD(dd, DATEDIFF(dd, 0, dateCreated), 0) the_date,
COUNT(*) work_order_count
FROM WorkOrder
where active = 1 and
dateCreated >= '3/1/2016' and
dateCreated <= '3/31/2016 23:59:59'
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, dateCreated), 0)
order by 1;

I want to take this same query a step further so that I can query the same data but show results for multiple date ranges. My specific purpose is to show data side-by-side for previous year comparison. Ideally I want to show the # of work orders created for 3/1-3/31 in 2014, 2015 and 2016, but all within the same view/result.

Is this possible? I've looked into joins but they appear to be when you are using different tables, not the same one.

Upvotes: 0

Views: 484

Answers (4)

paparazzo
paparazzo

Reputation: 45096

SELECT datepart(dd, 'yyyy'), datepart(dd, 'MM'), datepart(dd, 'dd'),
       COUNT(*) work_order_count
FROM WorkOrder
where active = 1 
  and dateCreated >=  '3/1/2016' 
  and dateCreated <   '4/1/2020' 
GROUP BY datepart(dd, 'yyyy'), datepart(dd, 'MM'), datepart(dd, 'dd')     
order by datepart(dd, 'yyyy'), datepart(dd, 'MM'), datepart(dd, 'dd');

Upvotes: 0

David Aman
David Aman

Reputation: 291

How about something like:

SELECT
    SUM(
        CASE
            WHEN dateCreated BETWEEN '3/1/2014' AND '3/31/2014 23:59:59'
            THEN 1
            ELSE 0
        END) AS March2014,
    SUM(
        CASE
            WHEN dateCreated BETWEEN '3/1/2015' AND '3/31/2015 23:59:59'
            THEN 1
            ELSE 0
        END) AS March2015,
    SUM(
        CASE
            WHEN dateCreated BETWEEN '3/1/2016' AND '3/31/2016 23:59:59'
            THEN 1
            ELSE 0
        END) AS March2016
FROM WorkOrder
WHERE active = 1;

Upvotes: 0

FLICKER
FLICKER

Reputation: 6683

Use pivot table:

;with cte1 as (
  select dateCreated, datepart(year, dateCreated) as Y
  from WorkOrder
  where datepart(month, dateCreated) = 3 and datepart(day, dateCreated) between 5 and 25
)
SELECT *
FROM cte1
  PIVOT
  (
    count(dateCreated)
    FOR Y in ([2014], [2015], [2016])
  ) as pv

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Just use conditional aggregation (or a pivot):

SELECT CAST(dateCreated as DATE) as the_date,
       SUM(CASE WHEN YEAR(dateCreated) = 2014 THEN 1 ELSE 0 END) as cnt_2014,
       SUM(CASE WHEN YEAR(dateCreated) = 2015 THEN 1 ELSE 0 END) as cnt_2015,
       SUM(CASE WHEN YEAR(dateCreated) = 2016 THEN 1 ELSE 0 END) as cnt_2016
FROM WorkOrder
WHERE active = 1 and
      MONTH(dateCreated) = 3 and
      YEAR(dateCreated) in (2014, 2015, 2016)
GROUP BY CAST(dateCreated as DATE)
ORDER BY the_date;

Upvotes: 1

Related Questions