• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Week Ending Grouping in SQL/ColdFusion

New Here ,
Jul 16, 2009 Jul 16, 2009

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques

Views

765

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 16, 2009 Jul 16, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 17, 2009 Jul 17, 2009

Copy link to clipboard

Copied

LATEST

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 ..." )

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation