CODEWITHSUNDEEP
sql
RobHurd
RobHurd

Reputation: 2061

SQL Count Records, Group By Date and fill in missing date?

I have the following SQL Query that counts Orders and groups them by each Day (Date).

Thus the following results:

01/02/2014 = 10
02/02/2014 = 2
05/02/2014 = 7
07/02/2014 = 4

Query:

SELECT TOP(@NumberOfRecords) DATEADD(dd, 0, DATEDIFF(dd, 0, AddedDate)) AS Date,       COUNT(DISTINCT ID) AS Count 
          FROM OrderSpecs
          GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, AddedDate)) 
          ORDER BY Date DESC

My question is, how can I get my Query to fill in any of the in-between missing dates and set the Count value to 0?

Example Desired Results:

    01/02/2014 = 10
    02/02/2014 = 2
    03/02/2014 = 0
    04/02/2014 = 0
    05/02/2014 = 7
    06/02/2014 = 0
    07/02/2014 = 4

Many thanks for you time taken out to read this.

Upvotes: 0

Views: 3210

Answers (3)

Dipendu Paul
Dipendu Paul

Reputation: 2753

DECLARE @mindate DATETIME
DECLARE @maxdate DATETIME
DECLARE @diff INT
SELECT @maxdate = MAX(addeddate), @mindate = MIN(addeddate) FROM OrderSpecs
SET @diff = DATEDIFF(DAY,  @mindate,@maxdate)
;WITH cte(dt,level)
AS
(
    SELECT  @mindate AS dt, 0 AS level 
    UNION ALL
    SELECT DATEADD(day,1,cte.dt),level + 1 from cte WHERE level < @diff
)
SELECT dt,c FROM cte
LEFT JOIN 
(
   SELECT DATEADD(dd, 0, DATEDIFF(dd, 0,AddedDate)) AddedDt, COUNT(ID) AS c  
   FROM OrderSpecs 
   GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0,AddedDate))
) tab
ON cte.dt = tab.AddedDt 
OPTION (MaxRecursion 32767);

CTE is written to generate all dates in a date range. With this a LEFT JOIN of aggregation resultset is done.

Disclaimer: this query would not work if the date range is more that 32767 days

Upvotes: 3

peterm
peterm

Reputation: 92845

If you issue a lot of such queries then you can create a calendar table that contain all calendar dates

CREATE TABLE calendar([date] DATE NOT NULL PRIMARY KEY);

And then use an outer join

SELECT TOP(@NumberOfRecords) 
       c.Date, COALESCE(o.Count, 0) Count
  FROM Calendar c LEFT JOIN 
(
  SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, AddedDate)) AS Date,
         COUNT(DISTINCT ID) AS Count 
    FROM OrderSpecs
   GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, AddedDate)) 
) o
    ON c.Date = o.Date
 ORDER BY Date DESC

Output:

|       DATE | COUNT |
|------------|-------|
| 2014-02-07 |     4 |
| 2014-02-06 |     0 |
| 2014-02-05 |     7 |
| 2014-02-04 |     0 |
| 2014-02-03 |     0 |
| 2014-02-02 |     2 |
| 2014-02-01 |    10 |

Here is SQLFiddle demo

Upvotes: 1

user2046117
user2046117

Reputation:

You could do something along the lines of this. I've not tested it thoroughly but in principle if you're unioning it'll ditch extras. You may need to do an order on the output to put it in the right order

SELECT SS1.Date, Count
FROM
( 
DECLARE @mindate Date
DECLARE @maxdate Date
WITH DateTable
AS
(
     SELECT CAST(@mindate as Date) AS [DATE]
     UNION ALL
     SELECT DATEADD(dd, 1, [DATE]) FROM DateTable 
     WHERE DATEADD(dd, 1, [DATE]) BETWEEN @mindate AND @maxdate
)
SELECT dt.[DATE], 0 [Count] FROM [DateTable] dt
UNION ALL

TOP(@NumberOfRecords) DATEADD(dd, 0, DATEDIFF(dd, 0, AddedDate)) AS Date,           COUNT(DISTINCT ID) AS Count 
      FROM OrderSpecs
      GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, AddedDate)) 
      ORDER BY Date DESC
) SS1
ORDER BY [Date]

Is it possible that you know the start and end date that you could pass in as seed dates?

Upvotes: 0

Related Questions