Print everyday of the year 1

[wanzek1]

I am creating a report that will count how many forms were submitted on each day. I need to still have a column in my report for that day even if no form was submitted on that day. I can't figure out how to do this. Currently it omits every date that does not have any forms submitted.

 

[SgtJarrow]

Take a look at the below sample. Basically you need a calendar table of some sort to hold ALL the days you want to display. You then join that to the table that has dates with specific values for some days on a LEFT join. Using the COALESCE allows you to set the value to 0 when there is no specific information in the table with values. The cte I provided here can be adapted to be a larger date range....I just used a small sample for demonstration. Of course, please ask any specific questions about this example...

DECLARE @Forms TABLE
(
	DateSubmitted		DATE,
	NumberSubmitted		INT
);

INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-04', 5);
INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-09', 2);
INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-11', 4);
INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-12', 12);
INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-17', 1);
INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-18', 5);
INSERT INTO @Forms (DateSubmitted, NumberSubmitted) VALUES ('2014-11-25', 8);

WITH cteAllDates (ADate) AS
(
	SELECT
		CAST('2014-11-01' AS DATE) 'ADate'
	UNION ALL
	SELECT
		DATEADD(DAY, 1, ADate)
	FROM cteAllDates a
	WHERE DATEADD(dd, 1, ADate) < CAST('2014-12-01' AS DATE)
)
SELECT
	alldates.ADate 'SubmissionDate',
	COALESCE(specificdates.NumberSubmitted, 0) 'NumberSubmitted'
FROM cteAllDates alldates
LEFT JOIN @Forms specificdates
	ON alldates.ADate = specificdates.DateSubmitted;

Results:

SubmissionDate	NumberSubmitted
2014-11-01	0
2014-11-02	0
2014-11-03	0
2014-11-04	5
2014-11-05	0
2014-11-06	0
2014-11-07	0
2014-11-08	0
2014-11-09	2
2014-11-10	0
2014-11-11	4
2014-11-12	12
2014-11-13	0
2014-11-14	0
2014-11-15	0
2014-11-16	0
2014-11-17	1
2014-11-18	5
2014-11-19	0
2014-11-20	0
2014-11-21	0
2014-11-22	0
2014-11-23	0
2014-11-24	0
2014-11-25	8
2014-11-26	0
2014-11-27	0
2014-11-28	0
2014-11-29	0
2014-11-30	0

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer