Complex date problem - help 1

[icemel]

Hi,

Here's my problem:

Need to run weekly reports every Monday or Tuesday. However, regardless of which day we run the report on, it has to calculate sums of sales figures for the previous week starting on the previous SUNDAY.

So if we run the report today, June 27th, I need the sales figures from June 18th at midnight through June 25th at midnight. (I think that's exactly one week)

So, it's not enough to do GETDATE() -7. Currently, we are using DATEPART(wk, 'date')-1, which takes the current week number (this week is week 26 for the calendar year), minus one is 25, or the previous week starting on Sunday.

This works fine until you get to January 1, 2007. Then you're back to week 1. If you subtract 1 from week 1, you get zero, not week 53 (the last week of December - which in this case will only consist of one day - Dec. 31st).

So, even IF week one minus one = week 53, it still doesn't work, because week 53 only has one day... and we need the entire week for the sales figures... that is, Dec 31 to January 6.

See the problem?? Don't know how to write this at all!

Need help, please.

Thank you

 

[gmmastros]

First, copy/paste this code in to a query analyzer window and play around with the @RunDate value.

Declare @RunDate DateTime
Set @RunDate = '2007-01-01'

Declare @StartDate DateTime
Declare @EndDate DateTime

Set @RunDate = DATEADD(dd, DATEDIFF(dd, 0, @RunDate), 0)

Select @EndDate = @RunDate + 1 - DatePart(Weekday, @RunDate)
Select @StartDate = @EndDate - 7

Select DateName(Weekday, @StartDate),
       @StartDate As StartDate, 
       DateName(Weekday, @EndDate),
       @EndDate As EndDate

Notice that is always produces a startdate and end date that are sunday's. Assuming you want a report for a single week, going from sunday morning to saturday night, you could use this to filter your data like so...

Select field
from   table
where  SomeDateField >= @StartDate
       and SomeDateField < @EndDate

Assuming there is a time component to your date time fields, this will return the correct data for you.

Of course, it's entirely possible that I misunderstood your question, but hopefully you'll find something in here that is valuable.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

Wow, I think this actually works! I'll have to take a look at it more closely to understand it, but this could solve the problem.

THANKS VERY MUCH

 

[gmmastros]

Let me know if you want me to explain any part of it. I'd be glad to.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

Hi George,

Yes, I find this part confusing:

Set @RunDate = DATEADD(dd, DATEDIFF(dd, 0, @RunDate), 0)

Select @EndDate = @RunDate + 1 - DatePart(Weekday, @RunDate)
Select @StartDate = @EndDate - 7

Thanks!

 

[gmmastros]

ok.

Set @RunDate = DATEADD(dd, [blue]DATEDIFF(dd, 0, @RunDate)[/blue], 0)

DateDiff returns a integer representing whatever units we want (in this case dd representing days). Since it's an integer, the time component is stripped off. Then, we use DateAdd to add the integer number of days back on (to day number 0). This effectively strips off the time component.

In SQL Server dates are stored internally as a float/real/whatever data type (soemthing that has decimal points). Day 0 happens to be Jan 1, 1900. Using SQL Server notation, today is day: Select DateDiff(dd, 0, GetDate()) -- 38894

Select @EndDate = @RunDate + 1 - DatePart(Weekday, @RunDate)

DatePart returns an integer. Since the first argument is Weekday, it returns an integer representing the day of the week. Sunday = 1, Monday = 2, etc...

So today is wednesday (4) Do the math. Assuming RunDate = Today, we end up with:
@EndDate = '2006-06-28' + 1 - 4
So we effectively subtract 3 days from today, ending up with '2006-06-25' (the most recent Sunday that passed). Again, remember that dates are stored internally as a number so you can simply add a subtract dates.

And then, this...
Select @StartDate = @EndDate - 7
just subtracts 7 more dates.

Does this make sense now? If there's still anything about this exmplanation that you don't understand, let me know and I will exmplain more.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

Hi George,

Sorry I should have saved you the effort of writing all that out - I just ran each section and saw the results and figured it out. But I'm sure other people will benefit from this explanation.

Very cool. The fact that you can utilize the weekday number to do math makes this a "universal" solution.

Thanks again!!

 

[gmmastros]

Now that you mention it, you should be a little careful because not all weekday numbers are consistent.

For example:

Set DateFirst 7
Select DatePart(Weekday, GetDate())

Set DateFirst 1
Select DatePart(Weekday, GetDate())

Look up the DateFirst command in Books on line for a full explanation. You should probably add: Set DateFirst 7 to the query to ensure consistent results.

It appears as though many languages default to Sunday = 1, but not all. You can run [!]sp_helplanguage[/!] to see what the various DateFirst's are for different languages.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[icemel]

DateFirst is 7 by default in English... which works for us... but thanks for the tip - sp_helplanguage is good to know.