September 3, 2008 at 12:55 pm
Paul Manning (9/3/2008)
Thanks Michael Valentine Jones, I can't say I understand it but that worked perfectly!! 😀
The basic algorithm is to find the difference in days from the earliest possible SQL Server Sunday, 1753-01-07, divide that by 7, multiply that by 7 to find the Sunday that starts the week, and then add 6 days to that to find the last day of the week. -53684 just represents 1753-01-07.
You may find these links of interest.
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
Links to many other datetime scripts here:
Date/Time Info and Script Links
September 3, 2008 at 6:14 pm
With a little less inline code, the query would look like this:set DateFirst 1; -- Set first day of week to Monday
select dbo.WeekBegins( SalesDate ) as WeekOf, Line, Count(*) as LineCount, Sum( Cost ) as LineCost
from #Daily
group by dbo.WeekBegins( SalesDate ), Line
order by dbo.WeekBegins( SalesDate ), Line;
The output for the first week is this:WeekOf Line LineCount LineCost
2008-06-30 00:00:00 8241 4 1.12
2008-06-30 00:00:00 19292 4 5.36
2008-06-30 00:00:00 28213 4 5.36
2008-06-30 00:00:00 28960 4 0.92
2008-06-30 00:00:00 38528 4 4.24
2008-06-30 00:00:00 46734 4 3.24
2008-06-30 00:00:00 72376 4 5.52
2008-06-30 00:00:00 88201 4 2.72
2008-06-30 00:00:00 88202 4 0.68
2008-06-30 00:00:00 88249 4 1.44
2008-06-30 00:00:00 88256 4 2.20
2008-06-30 00:00:00 88263 4 2.80
2008-06-30 00:00:00 98661 4 4.76Here is the WeekBegins function:/*
Author:
Tomm Carr
Create date:
10/25/2007
Description:
Returns the date of the first day of the week independent of the DATEFIRST setting.
For any date passed in, this returns the date of the preceding Sunday (or whatever day
is defined at the start of the week). If the date passed in is already Sunday, the same
date is returned (except that the time portion has been truncated).
*/
ALTER function [dbo].[WeekBegins](
@pDate datetime
)
returns smalldatetime
as begin
declare @Result smalldatetime;
set @Result = DateAdd( dd, DateDiff( dd, 0, @pDate ), 0 );
set @Result = DateAdd( dd, 1 - DatePart( dw, @Result ), @Result );
return @Result;
end
go
I had first thought of just using a week count for grouping, such as "DateDiff( ww, 0, SalesDate )". I tried that and it works fine if the first day of the week is Sunday. DateDiff apparently is not sensitive to the setting of DATEFIRST.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 3, 2008 at 8:13 pm
Tomm,
THe beauty of the solution posted by Michael is it does not depend on DATEFIRST settings.
Moreover, it allows to set required "datefirst" as a parameter.
So, what's the point to go for the "second best" solution?
_____________
Code for TallyGenerator
September 4, 2008 at 9:28 am
Oops! Sorry...a little late to the game I guess.
Well here's one way to create a calendar that I believe I got from this site:
with mycte as
(
select cast('2007-07-31' as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < '2007-08-31' -- increase this for a bigger range
)
select DateValue
, datepart(dy, DateValue) [day of year]
, datename(dw, DateValue) [day]
, datepart(dw, DateValue-1) [day of week]
, datepart(dd, DateValue) [day of month]
, datepart(ww, DateValue) [week]
, datepart(mm, DateValue) [month]
, datename(mm, DateValue) [month]
, datepart(qq, DateValue) [quarter]
, datepart(yy, DateValue) [year]
, datepart(HH, DateValue) [HOUR]
, datepart(MI, DateValue) [MIN]
, datepart(SS, DateValue) [SEC]
, datepart(MS, DateValue) [MILLISECOND]
from mycte
OPTION (MAXRECURSION 0)
September 4, 2008 at 11:19 am
Sergiy (9/3/2008)
Tomm,THe beauty of the solution posted by Michael is it does not depend on DATEFIRST settings.
Moreover, it allows to set required "datefirst" as a parameter.
So, what's the point to go for the "second best" solution?
I'm a bit confused. I went back and reread the original post to refresh my memory. Sure enough, the end of the week is specified as Sunday:
weekly contains a summary of daily data for each week, by 'productnumber' (weekending Sunday).
This would make the beginning of the week a Monday (set DATEFIRST 1).
Micheal's solution, while I fully acknowledge its brilliance in every other way, has the week ending on Saturday hard-coded into it. Sure, it's possible to make it into a sproc and pass a DATEFIRST-type parameter into it but, as written, it ends the week on the wrong day and is unresponsive to the DATEFIRST setting.
My solution, OTOH, does respond to the value of DATEFIRST. AAMOF, the first line is "set DATEFIRST 1" so the correct result set will be generated. OP can build his upsert statement around the code as written and get the result he wanted. I fail to see how this is "second best."
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 4, 2008 at 11:26 am
I'm a bit confused. I went back and reread the original post to refresh my memory. Sure enough, the end of the week is specified as Sunday:
weekly contains a summary of daily data for each week, by 'productnumber' (weekending Sunday).
This would make the beginning of the week a Monday (set DATEFIRST 1).
Micheal's solution, while I fully acknowledge its brilliance in every other way, has the week ending on Saturday hard-coded into it. Sure, it's possible to make it into a sproc and pass a DATEFIRST-type parameter into it but, as written, it ends the week on the wrong day and is unresponsive to the DATEFIRST setting.
My solution, OTOH, does respond to the value of DATEFIRST. AAMOF, the first line is "set DATEFIRST 1" so the correct result set will be generated. OP can build his upsert statement around the code as written and get the result he wanted. I fail to see how this is "second best."
Later the OP seemed to say that the last day of the week could be Saturday.
Paul Manning (9/2/2008)
I've no problem with creating a view for this but there must be a way of specifying the weekending day of week to be Saturday and using that date as the grouping?
So either mine or Michael's solultions will work.
September 4, 2008 at 11:56 am
ggraber (9/4/2008)
Later the OP seemed to say that the last day of the week could be Saturday.Paul Manning (9/2/2008)
I've no problem with creating a view for this but there must be a way of specifying the weekending day of week to be Saturday and using that date as the grouping?
So we seem to have a case of "OP confusion" or the dreaded "wandering specifications."
So either mine or Michael's solultions will work.
Actually, all three will work, though mine does respond to changes in DATEFIRST setting. However, probably 99.9% of all date-related code is DATEFIRST agnostic. In fact, the only time I have ever changed the setting is, like in my solution, for testing purposes and only then because I thought the user had a setting other than the default. I have never seen it in production code. So being responsive to DATEFIRST settings doesn't seem to be really big in the "must have" section. 😉
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 4, 2008 at 9:38 pm
My solution was specifically meant to be NOT responsive to the setting of DATEFIRST.
I posted the link in one of my prior posts that has a function for the start of week. It does not use the DATEFIRST setting; you just pass the start day of the week that you want in an input parameter to the function.
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
If you prefer to have the logic directly in your query, instead of in a function call, you can just do it this way for any day of the week that you want to start the week with.
-- Demo query for Start of Week
select
a.DATE,
Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
MyTable a
September 5, 2008 at 2:27 am
Michael Valentine Jones (9/4/2008)
My solution was specifically meant to be NOT responsive to the setting of DATEFIRST.
Why? The purpose of DATEFIRST is to allow your functions and procedures to react to changes without having to be rewritten.
I posted the link in one of my prior posts that has a function for the start of week. It does not use the DATEFIRST setting; you just pass the start day of the week that you want in an input parameter to the function.
Start of Week Function:
Again, why? DATEFIRST is a universal "parameter" that lets every function and proc (including system functions like DatePart) know where you want the week to start and to perform the calculations accordingly. Why design your code to ignore DATEFIRST and have to be told at every invocation what the expected first day of the week is?
There could well be the day when suddenly all such code starts returning wrong answers! The DBA informs you, "Oh, didn't you get the memo? We've changed the start of the week from Sunday to Monday. I adjusted DATEFIRST accordingly..." Now you have to go through all your code and look for that blankity-blank parameter and change the value. Hope you don't miss any.
If one develops code that works with week boundaries, it must respond to changes in DATEFIRST settings. To ignore it is poor design.
And there is no reason to ignore it. Using DatePart in the calculation is the simplest way to ensure the DATEFIRST setting will be accounted for. Just subtract the DatePart of the date from 7 and add that many days back to the date.return DateAdd( dd, 7 - DatePart( dw, @Date ), @Date )gets you the end of the week. Change the 7 to a 1 and get the start of the week. You can add code to truncate the time part and it will still be simple, direct, and if the DBA ever changes the setting of DATEFIRST, you won't be bothered in the slightest.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 5, 2008 at 7:13 pm
Tomm Carr (9/5/2008)
Michael Valentine Jones (9/4/2008)
My solution was specifically meant to be NOT responsive to the setting of DATEFIRST.Why? The purpose of DATEFIRST is to allow your functions and procedures to react to changes without having to be rewritten.
I posted the link in one of my prior posts that has a function for the start of week. It does not use the DATEFIRST setting; you just pass the start day of the week that you want in an input parameter to the function.
Start of Week Function:
Again, why? DATEFIRST is a universal "parameter" that lets every function and proc (including system functions like DatePart) know where you want the week to start and to perform the calculations accordingly. Why design your code to ignore DATEFIRST and have to be told at every invocation what the expected first day of the week is?
There could well be the day when suddenly all such code starts returning wrong answers! The DBA informs you, "Oh, didn't you get the memo? We've changed the start of the week from Sunday to Monday. I adjusted DATEFIRST accordingly..." Now you have to go through all your code and look for that blankity-blank parameter and change the value. Hope you don't miss any.
If one develops code that works with week boundaries, it must respond to changes in DATEFIRST settings. To ignore it is poor design.
And there is no reason to ignore it. Using DatePart in the calculation is the simplest way to ensure the DATEFIRST setting will be accounted for. Just subtract the DatePart of the date from 7 and add that many days back to the date.
return DateAdd( dd, 7 - DatePart( dw, @Date ), @Date )gets you the end of the week. Change the 7 to a 1 and get the start of the week. You can add code to truncate the time part and it will still be simple, direct, and if the DBA ever changes the setting of DATEFIRST, you won't be bothered in the slightest.
You are making a bad assumption that DATEFIRST is a universal "parameter". We do outsourcing work for many different clients, so the start of the week has to match the clients, not the servers default setting for DATEFIRST. With work for many different clients being done on the same server, I need to make sure that code does what is expected. We even have the possibility of the week starting on different days on different reports for the same client, or even being differnt on different columns within a single report.
You need to lighten up a bit on this. I really don't care if you use DATEFIRST the way you do or ignore it the way I do, but there is nothing that says that what works in your organization is something that is universal.
I explained the way that my code works, so people are free to use it or not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply