INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Print everyday of the year

Print everyday of the year

(OP)
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.

RE: Print everyday of the year

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...

CODE

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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close