question

Moz avatar image
Moz asked

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;
jointotal
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moz, I formatted the code to make it easier to read, but unfortunately I don't have time to review it and try to answer this right now. I'll be back later to take a look if no one else gets to it before then.
0 Likes 0 ·
if you change: ON (tr1.TheMonth > dateadd(year, -1, tr2.TheMonth) to: ON (tr1.TheMonth >= dateadd(year, -1, tr2.TheMonth) does it work?
0 Likes 0 ·
@Scot, no that gives me one additional month in the sum aggregate function that I don't need and messes the totals. Thanks for the reply though.
0 Likes 0 ·

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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 "
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Tom, the code "dateadd(month, datediff(month, 0, admission_date),0)" with the zero constants is a method I've used to set date fields to all be the 1st of the month, so I can group by month. I can't see any issue there but will investigate more. The missing month is December. Thanks a lot for the reply.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.