Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Display all months with no data Expand / Collapse
Posted Monday, December 9, 2013 10:27 AM


Group: General Forum Members
Last Login: Tuesday, November 10, 2015 5:54 PM
Points: 20, Visits: 85
Newbie...I've seen some suggestions on how to display query results for all months even if there is not data.

Ex. Jan 12, 4
Feb 12, 7
Sept 12, 9
Dec 12, 4

what I'd like

Jan 12, 4
Feb 12, 7
Mar 12, 0
April 12, 0

I created a dimDate table and left outer joined with my data table, but is still not populating all months. Any help would be appreciated. Below is a scaled down view of my query. Thank you.


month(da.calendar_dt) as month, da.year,
isnull(sum(p.psioutcome),0) as count

FROM [AnalystsEDSSandbox].[dbo].[dimDate] da
left outer join quality.dbo.fact_patientsafetyindicators p on CAST(p.dischargedatetime AS DATE)= da.calendar_dt

where da.calendar_dt>'2012-01-01'

group by month(da.calendar_dt),da.year

order by year,month(da.calendar_dt)
Post #1521219
Posted Monday, December 9, 2013 10:51 AM


Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 7,995, Visits: 7,282
I don't have your table structures, but I'd like to present an alternate way. Instead of relying on the data that's there, create a table of months you want to report and then update it from your data table. This will use a tally table to populate a temp table with one row per month going back 13 months from a base date. If you aren't familiar with tally tables yet, check out the article at and take the time to get familiar with them. They will change the way you look at data.

DECLARE @dtmBase datetime = '11/01/2013';

IF OBJECT_ID('tempdb.dbo.#tblMonths', 'u') IS NOT NULL DROP TABLE #tblMonths;
CREATE TABLE #tblMonths (
ID Integer not null identity (1, 1),
Start Datetime not null,
TheCount Integer);

INSERT INTO #tblMonths(Start, TheCount)
SELECT DATEADD(month, -t.N + 1, @dtmBase), 0
FROM Tally1K t
WHERE t.N <= 13

You can then update #tblMonths.TheCount from your data table using the dates in your #tblMonths table. If you have the date field indexed, I expect that it would perform acceptably.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1521230
Posted Monday, December 9, 2013 10:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 3, 2016 10:53 AM
Points: 298, Visits: 448
Another option is to use a tally table.

and go down to the section dozens of other uses. What you're looking for is in that section.
Post #1521231
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse