Dynamically query past 8 weeks and current week based on a given date 1

[robsuttonjr]

I need to dynamically query past 8 weeks and current week based on a given date. I can't use the week function since I am on sql

So if I say in where clause for example:
EVENTDATE between cast('03/06/17' as datetime) and cast('03/06/17' as datetime) + 6

This will give me current week based on a static given date.

Now I need to go back 8 weeks or 56 days. Currently I have created 8 other union queries using subtraction to get what I need but this is very cumbersome to maintain and very slow.

Any examples of how someone has been able to create a query which will dynamically create an 8 week past and 1 week forward query using aggregates would help greatly.


Regards,

Rob

 

[Olaf Doschke]

getdate() is now (datetime). And there is dateadd with many dateparts to use, including weeks as wk:

EVENTDATE between [highlight #FCE94F]dateadd(wk,-8[/highlight], cast(getdate() as date)) and [highlight #FCE94F]dateadd(wk,+1[/highlight], cast(getdate() as date))

Bye, Olaf.

 

[robsuttonjr]

Thanks! Now let's say users can pick any day of the week. Now I want to be able to set the week start date so:
03/16/17 (Thursday) would set week start to Thursday through Wednesday
If 03/17/17 (Friday) would set week start to Friday through Thursday

Finally I need to group the data by weeks periods and also show the week begin/end for each set. The eventdate may not have an entry for a particular date so I need to get the begin/end date another way.

I plan to do this query in a stored procedure so I can change the week start day dynamically based on the specific date passed then return current week plus last 8 weeks.



Regards,

Rob

 

[Olaf Doschke]

dateadd(wk,-8, cast(getdate() as date)) and dateadd(wk,+1, cast(getdate() as date))

Is not fixed to calendar weeks, as the base date is (getdate() as date), which always is today, those weeks are already shifting along the current day with this expressions.

To get start and end date of week periods, well you have the overall start and enddates with the single date expressions
dateadd(wk,-8, cast(getdate() as date))
and
dateadd(wk,+1, cast(getdate() as date))

And to group by all 9 weeks you of course would need to calculate all inbetween dates. Then you need a tally table approach which creates a single field table variable with numbers -8 up to 1 and can take that as variable input for dateadd(wk,tallytablefield, cast(getdate() as date)).

Bye, Olaf.