Getting last 5 weeks data

[Lafor]

Hello Guys...

Have to write a query or stored proc that would (given the day of the week) grab last 5 weeks data.. A week defined as Sun - Sat
So, for example if today is Thursday, the stored proc would
get last saturday's date and do a query to get the data for the
last 5 weeks (i.e up to last saturday's date)

Hope I am clear...

Let me know if I need to clarify...

Any ideas?

Thanks to all in advance...

[antares686]

Look at DATEPART in SQL BooksOnline at and check out the weekday (dw) option. You should be able to go from there.

Also, you will be using GETDATE() most likely so look at CONVERT and possible formats to get rid of the time off the value.

If you need more help let us know.:)

[Lafor]

Thanks..
How do u get previous 5 weeks from a given date.. (algorithm)?

[antares686]

DATEADD(wk,-5,givendate)

[Lafor]

Thank you!! my friend....

I knew I was off a tangent..

Thanks... a lot...

[Lafor]

Hi!

Can one select into a table where tablename is built on the fly?

i.e select * into "tblName" + i

Is there a roundabout way to do it?

Thanks in advance...

[antares686]

No not directly. You will have to build the entire query string and use either EXECUTE/EXEC or sp_executsql.

Ex.

DECLARE @tbl varchar(255)

SET @tbl = 'Table1'

EXEC ('SELECT * FROM ' + @tbl)

Which runs SELECT * FROM Table1

[Lafor]

Thanks... much