show all months in current year

Question

show all months in current year

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

In your query, start with a table that is a list of month numbers/names and from that left join to your table. If you don't have any data for months, you'll still get the months listed.

Here is an example:

DECLARE @test TABLE (MyDate datetime, Qty int);

INSERT INTO @test

SELECT '20100101',5 UNION ALL

SELECT '20100201',12 UNION ALL

SELECT '20100301',8 UNION ALL

SELECT '20100401',15 UNION ALL

SELECT '20100501',16 UNION ALL

SELECT '20100601',10 UNION ALL

SELECT '20100701',7 UNION ALL

SELECT '20100801',9;

;WITH Months (MonthNbr, Name) AS

(

SELECT 1, 'January' UNION ALL

SELECT 2, 'February' UNION ALL

SELECT 3, 'March' UNION ALL

SELECT 4, 'April' UNION ALL

SELECT 5, 'May' UNION ALL

SELECT 6, 'June' UNION ALL

SELECT 7, 'July' UNION ALL

SELECT 8, 'August' UNION ALL

SELECT 9, 'September' UNION ALL

SELECT 10, 'October' UNION ALL

SELECT 11, 'November' UNION ALL

SELECT 12, 'December'

)

SELECT m.MonthNbr, m.Name, Qty = IsNull(t1.Qty,0)

FROM Months m

LEFT JOIN @test t1

ON m.MonthNbr = month(t1.MyDate);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

viper3098 Old Hand Points: 308 More actions · Answer 2

Hi Wayne,

Thank you for the suggestion.

I tried implementing the solution that you gave, but wasn't able to get the required results. The results were the same as using the method below. I have a little bit more information that may help the situation.

I have a static table called "Dates" within my database, that is basically a calendar table with every date listed from 2005 to 2030.

I tried doing a union all with that table for the year that I was working with and only the first day of every month. The result would give me my normal dataset plus a bunch of null rows at the end. Please view the attached image for clarification.

When I have the charts in a stand alone report, this work out well and gives me the desired result. But I have the chart within a table group, that allows the user to group the data based on either the Parent Company or the Company name.

Without the dates table attached, the report brings back a total of 2 groups (Big, Small) with charts for each if the parent company was chosen as the "Group By". Or 6 groups (AA, BB, CC, DD, EE, FF) with charts if company was chosen as the "Group By". All charts only display months in which they have data (Jan - Oct).

The the dates table attached, the report brings back a total of 3 groups (Big, Small, NULL) with charts for each if the parent company was chosen as the "Group By". Or 7 groups (AA, BB, CC, DD, EE, FF, NULL) with charts if company was chosen as the "Group By". All charts only display months in which they have data (Jan - Oct), except the null charts which has no data, but displays all months Jan - Dec.

I'm not sure if this helps, but I have attached two images.

1. What my sample dataset would look like.

2. My actual report parameters within my report with the group by.

Thanks,

Matt

WayneS SSC Guru Points: 95461 More actions · Answer 3

Can you post the table definition and sample data? Please see the first two links in my signature for how to do this. You should have, at a minimum, CREATE TABLE statements for each table, INSERT statements to put some sample data into the tables, and based on the sample data provided, what the expected results should be.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2