December 9, 2013 at 10:27 am
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)
December 9, 2013 at 10:51 am
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.
December 9, 2013 at 10:52 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply