time dimension puzzle

[dsssh]

I'm trying to populate a time table with day of the year, day of the week, week of the year, and last year day (not DATE). This last value, last year day, is tricky because some weeks overlap years and actually make a 53rd week. Has anyone filled tables matching days with last year's days for the same week of the year? In other words, show me the day of the year (#1 through #365) for Monday, week 1 2003 and for monday, week 1 2004.

how did you do it?

 

[johnherman]

It's easy to populate using a cursor; every time you hit the beginning of a year, reset your counter. Loop thru (fetch) each row and update it. It's not worth the time trying to figure out how to do it in pure SQL since this is a one-time program and the data set is small anyway.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[Sridharan]

Which DB are you on??? Do you have any customized Weeks???? What does Last Year Day contain or what exactly you want for that column??? Fairly achievable using Plain SQL also...

Sri