1

I'm building a bar chart. I would like a query which counts the entries from a particular day and groups them together as one row with a count. I have the following query working and I have a working date dimension table which I would like to try to join with to fill in the gaps. It would also be nice to have min and max variables to easily alter the span (1 week, 1 month, 1 year, or from start of this year etc).

I got a variant working with a CTE query but it was painfully slow (1min+). I can't seem to figure this out, help!

The Date Dimension table was created following this guide: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

CREATE TABLE dbo.DateDimension
(
DateKey             INT         NOT NULL PRIMARY KEY,
[Date]              DATE        NOT NULL,
[Day]               TINYINT     NOT NULL,
DaySuffix           CHAR(2)     NOT NULL,
[Weekday]           TINYINT     NOT NULL,
WeekDayName         VARCHAR(10) NOT NULL,
IsWeekend           BIT         NOT NULL,
IsHoliday           BIT         NOT NULL,
HolidayText         VARCHAR(64) SPARSE,
DOWInMonth          TINYINT     NOT NULL,
[DayOfYear]         SMALLINT    NOT NULL,
WeekOfMonth         TINYINT     NOT NULL,
WeekOfYear          TINYINT     NOT NULL,
ISOWeekOfYear       TINYINT     NOT NULL,
[Month]             TINYINT     NOT NULL,
[MonthName]         VARCHAR(10) NOT NULL,
[Quarter]           TINYINT     NOT NULL,
QuarterName         VARCHAR(6)  NOT NULL,
[Year]              INT         NOT NULL,
MMYYYY              CHAR(6)     NOT NULL,
MonthYear           CHAR(7)     NOT NULL,
FirstDayOfMonth     DATE        NOT NULL,
LastDayOfMonth      DATE        NOT NULL,
FirstDayOfQuarter   DATE        NOT NULL,
LastDayOfQuarter    DATE        NOT NULL,
FirstDayOfYear      DATE        NOT NULL,
LastDayOfYear       DATE        NOT NULL,
FirstDayOfNextMonth DATE        NOT NULL,
FirstDayOfNextYear  DATE        NOT NULL
);

Current query:

select count(*) as total, dateadd(DAY,0, datediff(day,0, CreatedAt)) as created
from Table1
group by dateadd(DAY,0, datediff(day,0, CreatedAt))
order by dateadd(DAY,0, datediff(day,0, CreatedAt)) desc


total   created 
1       01/11/2017 00:00:00 
16      01/03/2017 00:00:00 
1       12/27/2016 00:00:00 
1       12/20/2016 00:00:00 
1       11/30/2016 00:00:00 
1       11/29/2016 00:00:00 
11      11/28/2016 00:00:00 
13      11/25/2016 00:00:00 
4       11/24/2016 00:00:00 
2       11/22/2016 00:00:00 

I want something like this

total   created 
1       01/11/2017 00:00:00 
0       01/10/2017 00:00:00
0       01/09/2017 00:00:00
0       01/08/2017 00:00:00
0       01/07/2017 00:00:00
0       01/06/2017 00:00:00
0       01/05/2017 00:00:00
0       01/04/2017 00:00:00
16      01/03/2017 00:00:00 
etc
  • Which version of SQL Server? It would also help to show us the definition of the date dimension table. Also are the results to be displayed or inserted into another table? – ypercubeᵀᴹ Jan 16 '17 at 11:26
  • It's Azure SQL and i've edited the post to show the date dimension table definition. The results would be used by a JS graphing library to produce line/bar graphs. – Sauceman Jan 16 '17 at 11:54
  • If you are still having performance issues after using @AaronBertrand's answer, you might want to consider adding a computed column to Table1 to hold a date datatype value so you can skip all of the conversions. e.g. alter table Table1 add CreatedAtDate as convert(date,CreatedAt) persisted; create nonclustered index ix_table1_CreatedAtDate on Table1([CreatedAtDate]); – SqlZim Jan 16 '17 at 16:58
4

First, get your list of dates from the dimension. Just specify the start and end you need - let's say all of January, since you should have at least a gap at the end:

DECLARE @Start date = '20170101',
  @End date = DATEADD(MONTH,1,'20170101');

SELECT [Date] FROM dbo.DateDimension
  WHERE [Date] >= @Start AND [Date] < @End;

You should see 31 rows here. Now, use an outer join to pull in the rows you have aggregates for:

DECLARE @Start date = '20170101',
  @End date = DATEADD(MONTH,1,'20170101');

SELECT created = d.[Date], total = COALESCE(s.total, 0)
  FROM dbo.DateDimension AS d
  LEFT OUTER JOIN
  (
     SELECT [Date] = CONVERT(date, CreatedAt), 
            total  = COUNT(*)
     FROM dbo.whatever
     WHERE CreatedAt >= @Start
       AND CreatedAt < @End
     GROUP BY CONVERT(date, CreatedAt)
  ) AS s ON d.[Date] = s.[Date]
  WHERE d.[Date] >= @Start 
    AND d.[Date] < @End
  ORDER BY d.[Date];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.