Reasons for missing records in a self-join in a rolling 12 month query?
Has anyone come across something similar? Why is this rolling twleve month query missing records from the first section (the insert into the temp table) when the rolling twelve month figures are calculated in the second section (by self-join)? All but one month seem to return the expected 12 month total.... Many thanks. CREATE TABLE #TempResults ( TheMonth datetime null, condition varchar(100) null, value int null ) --insert section INSERT INTO #TempResults SELECT dateadd(month, datediff(month, 0, admission_date),0) AS TheMonth ,condition ,sum(1) AS Value FROM dbo.MainTable GROUP BY dateadd(month, datediff(month, 0, admission_date),0), condition --Second section. Rolling twelve month total section SELECT tr2.TheMonth ,tr2.condition AS Condition ,sum(tr1.Value) FROM #TempResult AS tr1 INNER JOIN #TempResults AS tr2 ON (tr1.TheMonth > dateadd(year, -1, tr2.TheMonth) AND tr1.TheMonth <= tr2.TheMonth AND tr1.condition = tr2.condition) GROUP BY tr2.TheMonth, tr2.condition ORDER BY tr2.TheMonth, tr2.condition; --Tidy up DROP TABLE #TempResults;
I noticed you have a constant integer 0 for some of your date functions. That will implicitly convert to 1/1/1900. I don't know whether or not that 0 was just a placeholder in your code you posted, but I wanted to mention that detail. You didn't mention which month is missing. I was going to guess the first one due to the inner join, but the "