new colum

[Jeff06]

I have a large table t1 contain record like that

Id purchase advance date
------
2345 $22333 $3455 12/25/2006
-----


I want to create a new column to label week information
if dec 11 2006 =<date <=dec 15, 2006 week=week4
if dec 18 2006 =<date <=dec 22, 2006 week=week3
if dec 25 2006 =<date <=dec 29, 2006 week=week2
if Jan,1 2007 =<date <=Jan 5, 2007 week=week1
if Jan, 8 2007=<date <=today, 2007 week=week0

How can i do that using sql?
Thanks in advance

 

[Bat17]

What are you trying to achieve?
to know how many weeks old from a certain date you data is?
Have alook at the DateDiff() function and see if this will do what you need

ps. not a good idea to call a field 'date' as it is a reserved word and may trip you up latter

Peter

 

[boblarson]

Plus, the way you are saying you want to do this is totally bad design and will only cause you problems later. You should be thinking in database terms (narrow and long) instead of spreadsheet terms (wide and short). Normalization, Normalization, ... I can't stress that enough. You should NOT be creating a new column per date.

 

[Jeff06]

Thanks for response.
In reality, I have table contain puchase date of some assets and some other financial information for many years. I can generate monthly summary for finanical performace for the date (by parseign month and year. But my boss want me to generate weekly summary info for the latest serveral weeks. This is why i want a column to indicate week. Do you have a better idea to fulfill this task?
Thank you very much for any suggestions and comments

Jefff

 

[Bat17]

creating a calculated column in a query is fine

for something like this I generaly create a compound serial of the week number and year*100 to keep things in order and group on this

WeekSer: (DatePart("yyyy",[ddate])*100)+DatePart("ww",[ddate])

look up the DatePart function in help and you will see how to adjust the week for the start of the year and which day the week starts.

HTH

Peter

 

[Jeff06]

Peter,
THX for your advice

Jeff