Re: AW: Embedded SQL formatting dates

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Chris

Is the code something you are able to post on the Midrange code site? I 
don't know the exact page, and midrange.com seems to be down at the 
moment, so can't help.

Vern

On 1/4/2016 1:28 PM, Hiebert, Chris wrote:
> -- The problem I am facing is that I want to group daily data by week
>
> Another way to accomplish this type of group by is to use a helper file that contains the date in multiple formats.
> The "Group By" can be based on columns from multiple tables.
>
> You can research the DATE_DIM table name and find references for many data warehousing systems.
>
> The general idea is that you would put every date you could ever use into one table and then load associated data for each date.
> Each date row would also contain a column identifying the week. Another column could contain the date in CYMD numeric format.
> Thus allowing you do join to this table directly on the format from both tables, group by the week column and generate the desired result.
>
> The method of using this type of database for dates can be extremely fast. A lot faster than requiring the SQL engine to do date conversions before its able to join data.
>
> I have the sql needed to generate a DATE_DIM table, but didn't want to post it as it is about 600 lines of text.
>
> Chris Hiebert
> Senior Programmer/Analyst
> Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.