Skip to main content
F
fs22
Known Participant
July 16, 2009
Question

Week Ending Grouping in SQL/ColdFusion

  • July 16, 2009
  • 2 replies
  • 850 views

Greetings,

I am working on an analytics app and have been asked to create a chart based upon the results of every week. So for example, we have been gathering data since January. Managers want to see the results from each week in a chart format (I will probably put the results in a table for them as well.

Anyway, here is my conundrum, the db has a date field. I could write several QoQs from my main query and code them in such a way as to pull every week (something along the lines of assigning a date to a variable, then adding days to it for each week...perhaps in a loop). But I thought there must be a more efficient way. Does anyone have any thoughts on this?

Thanks in advance,

Clay

This topic has been closed for replies.

2 replies

T
TLC-IT
Inspiring
July 17, 2009

The suggestion to use a date-lookup table is an extremely good one:  I have found no more-satisfactory solution, because inevitably there are all sorts of "business specific" rules that come into dates ... and without a lookup-table your queries wind up having lots and lots of complicated formulas.

The SQL server, of course, will wind up grabbing just a few database-pages out of that table and cacheing them in memory for the entire duration of the query... so it's not "slow."  (In fact, I daresay it's faster.)  You can put any sort of pre-calculated figures that you might need into that table, calculated once using an arbitrary script or set of queries, add any new columns any time you need them, and eliminate all those calculations and potential errors.

Put two hundred years' worth of dates into the table.  That should do for a very long time.  Long enough, anyway.  (As I saw in one bit of source-code:  "dig me up and I'll fix it then ..." )

D
Dan_Bracuk
Inspiring
July 16, 2009

Consider creating a table called dates or something like that.  The primary key would be the date and other fields would include fiscal year info, week ending, holiday info, and anything else relevent to your organization.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices