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 );
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