Nick Nick - 15 days ago 5
SQL Question

SQL Join Multiple Subqueries - Count Open / Closed by Date

I'm trying to count all open tickets / closed tickets groups by date. Some dates will have 0 values for both but I'd still like to show the date. I feel like I'm close but can't seem to get the grouping correct, it's just giving a total.

DECLARE @DateFrom AS DATE = '11/16/2016'
DECLARE @DateTo AS DATE = GETDATE()

WITH DateRanges AS
(SELECT @DateFrom AS 'DateValue'
UNION ALL
SELECT DATEADD(DAY, 1, DateValue)
FROM DateRanges
WHERE DateValue < @DateTo)

SELECT CONVERT(varchar(10),DateValue, 101) AS "DateVal",

(SELECT
COUNT(OPENDATE)
FROM DateRanges AS a
LEFT OUTER JOIN MAINTABLE
ON a.DateValue = convert(varchar(10), DATEADD(hh, DATEDIFF(HH, GetUTCDATE(), GETDATE()), OPENDATE), 101)
) AS opn,


(SELECT
COUNT(CLOSEDDATE)
FROM DateRanges AS b
LEFT OUTER JOIN MAINTABLE
ON b.DateValue = convert(varchar(10), DATEADD(hh, DATEDIFF(HH, GetUTCDATE(), GETDATE()), CLOSEDDATE), 101)
) AS cls

FROM DateRanges
GROUP BY CONVERT(varchar(10),DateValue, 101)


This produces the following:

DateVal | opn | cls
11/16/2016 | 3 | 3
11/17/2016 | 3 | 3
11/18/2016 | 3 | 3


MainTable

ID | OPENDATE | CLOSEDDATE
123 | 11/16/2016 | 11/16/2016
124 | 11/16/2016 | 11/18/2016
125 | 11/18/2016 | 11/18/2016


Expected Output

DateVal | opn | cls
11/16/2016 | 2 | 1
11/17/2016 | 0 | 0
11/18/2016 | 1 | 2


Thank you for your help

Answer

You can use your approach, but you need correlated subqueries, instead of totals:

WITH DateRanges AS (
      SELECT @DateFrom AS 'DateValue'
      UNION ALL
      SELECT DATEADD(DAY, 1, DateValue)
      FROM DateRanges
      WHERE DateValue < @DateTo
     )
SELECT CONVERT(varchar(10), DateValue, 101) AS "DateVal",
       (SELECT COUNT(mt.REQDATE)
        FROM mt
        WHERE dr.DateValue = convert(varchar(10), DATEADD(hour, DATEDIFF(hour, GetUTCDATE(), GETDATE()), mt.OPENDATE), 101)
      ) AS opn,
      (SELECT COUNT(CLSDDATE)
       FROM MAINTABLE mt
       WHERE dr.DateValue = convert(varchar(10), DATEADD(hh, DATEDIFF(HH, GetUTCDATE(), GETDATE()), mt.CLOSEDDATE), 101)
      ) AS cls 
FROM DateRanges dr;

Also note that you should not need an aggregation in the outer query.

Comments