INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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

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

Bye, Olaf.

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

(OP)
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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close