CODEWITHSUNDEEP
sql
Remedios
Remedios

Reputation: 1

Count of records created and closed by month/yy

Trying to pull dynamic list (and push to a visualization app) which shows how many support tickets are created and how many are closed each month. Need 3 columns - Count of cases created, count of cases closed and YYYYMM. the Created/Closed dates are both datetime fields. Can't get both created and closed on the same row. 

SELECT
COUNT (t1.id) AS Tickets_Created
,CAST(DATEPART(yy,t1.CreatedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),t1.CreatedAt,112),5,2)) AS YYYYMM
FROM [Ticket] AS T1
WHERE T1.Spam=0
GROUP BY CAST(DATEPART(yy,t1.CreatedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),t1.CreatedAt,112),5,2))
--ORDER BY YYYYMM

UNION

SELECT

COUNT (t.id) AS Tickets_Resolved
,CAST(DATEPART(yy,S.ResolvedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),S.ResolvedAt,112),5,2)) AS YYYYMM
FROM [Ticket] T
LEFT JOIN FS.TicketStatus S ON S.TicketNK=T.id
WHERE T.Spam=0 AND YEAR(s.ResolvedAt) > '2016'
GROUP BY CAST(DATEPART(yy,S.ResolvedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),S.ResolvedAt,112),5,2))

Expected Results:

YYYYMM    Created Closed
2017 11   50      30
2017 12   45      23
2018 01   90      56

Actual results (date closed appears on next row):

Tickets_Created YYYYMM
   1           2017 11
   2           2017 11
   16          2017 12
   25          2017 12
   34          2018 01
   54          2018 01

Upvotes: 0

Views: 53

Answers (2)

shawnt00
shawnt00

Reputation: 17915

Here's the basic approach. I'll let you put the rest of the filters back in:

WITH data as (
    SELECT
        1 as tag,
        DATEPART(yy, CreatedAt) as yy,
        DATEPART(mm, CreatedAt) as mm
    FROM Ticket
    UNION ALL
    SELECT
        2 as tag,
        DATEPART(yy, ResolvedAt) as yy,
        DATEPART(mm, ResolvedAt) as mm
    FROM Ticket
)
SELECT yy, mm,
    COUNT(CASE WHEN tag = 1 THEN 1 END) as Created,
    COUNT(CASE WHEN tag = 2 THEN 1 END) as Resolved
FROM data
GROUP BY yy, mm;

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

your rows merges because your using UNION, you need join to get your desired output

SELECT t1.YYYYMM, t1.Tickets_Created, t2.Tickets_Closed
FROM 
    (SELECT COUNT (t1.id) AS Tickets_Created
      ,CAST(DATEPART(yy,t1.CreatedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),t1.CreatedAt,112),5,2)) AS YYYYMM
    FROM [Ticket] AS T1
    WHERE T1.Spam=0
    GROUP BY CAST(DATEPART(yy,t1.CreatedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),t1.CreatedAt,112),5,2))) as t1
LEFT JOIN
    (SELECT COUNT (t.id) AS Tickets_Resolved
        ,CAST(DATEPART(yy,S.ResolvedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),S.ResolvedAt,112),5,2)) AS YYYYMM
    FROM [Ticket] T
    LEFT JOIN FS.TicketStatus S ON S.TicketNK=T.id
    WHERE T.Spam=0 AND YEAR(s.ResolvedAt) > '2016'
    GROUP BY CAST(DATEPART(yy,S.ResolvedAt) AS VARCHAR) +' '+ (SUBSTRING(CONVERT(NVARCHAR(6),S.ResolvedAt,112),5,2))) as t2 on t2.YYYYMM = t1.YYYYMM

Upvotes: 1

Related Questions