Counts of different data sets by week

Question

Counts of different data sets by week

Rob-350472

SSCertifiable

Points: 7197
More actions
November 15, 2012 at 10:14 am

#270307

Guys,
There's more and more demand for me to produce statistics based on a rolling week by week basis, for example counting the number of calls made, number of logins to our website - that sort of thing. The key is that they're entirely different data sets I'm producing the counts for.
The best way I've found of doing this is producing a series of temporary tables for each metric I need to capture, grouping the data based on a year and week number (both of which I have stored in a tally calendar table used for loads of things). I then combine all of these together by joining the temp tables to the tally calendar on week number and year (never thought I'd be joining on things like that!)
This seems to work fairly well and it's pretty fast - other methods - sub queries rather than temp tables for example, are far slower.
I can't help but think I'm missing a trick and there's some other way I could be doing this though. Does anyone have any suggestions?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 1

Rob-350472 (11/15/2012)
Guys,
There's more and more demand for me to produce statistics based on a rolling week by week basis, for example counting the number of calls made, number of logins to our website - that sort of thing. The key is that they're entirely different data sets I'm producing the counts for.
The best way I've found of doing this is producing a series of temporary tables for each metric I need to capture, grouping the data based on a year and week number (both of which I have stored in a tally calendar table used for loads of things). I then combine all of these together by joining the temp tables to the tally calendar on week number and year (never thought I'd be joining on things like that!)
This seems to work fairly well and it's pretty fast - other methods - sub queries rather than temp tables for example, are far slower.
I can't help but think I'm missing a trick and there's some other way I could be doing this though. Does anyone have any suggestions?

Sounds like you taken the correct steps in dividing this problem up to run fast. The only suggestion that I might make is to "pre-aggregate" the data by week on the way into the temp tables but don't know for sure because I don't know what your data is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 2

Thanks for the reply Jeff, if I'm understanding you correctly that's what I'm doing - for instance:

SELECT IsoWeek, YearNumber, count(communicationID)

FROM Comm c

JOIN TallyCal tc on tc.TheDate = c.Comm_Date

GROUP BY IsoWeek, YearNumber

Doing that is very quick - I basically do something similar a few times for different (totally unrelated) metrics I need to produce counts for, then it's a case of bringing them all together just by joining them to the main TallyCal again on IsoWeek and Year Number.

As I say, it's pretty quick (in one case I improved speed from something silly like half an hour to a few seconds from someone elses proc), however, I just wondered if there was a more 'elegant' or compact way to do it. It's quick, that's the main thing!