Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:35 PM
Points: 15, Visits: 71
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
ETC

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.


SELECT

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
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 4,029, Visits: 3,463
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 http://www.sqlservercentral.com/articles/T-SQL/62867/ 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
ORDER BY 1 DESC;

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: Thursday, September 18, 2014 2:04 PM
Points: 238, Visits: 287
Another option is to use a tally table.
See http://www.sqlservercentral.com/articles/T-SQL/62867/

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