Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Shift week to Wed - Tues Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 10:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:40 AM
Points: 48, Visits: 117
Hello. I need to be able to group data based on not only the date, but also the "week of". However, the "week" is defined as Wed - Tues.

Basically, I think what I need is for the code to convert a date to the preceding Wednesday (not the Wed of last week). For example, 9/23/2013 would convert to 9/18/2013, but 9/27/2013 would convert to 9/25/2013.

I can find some code to find a date in the previous week, but nothing like this (so far).

I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.


Thanks,

PK
Post #1497969
Posted Tuesday, September 24, 2013 11:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 590, Visits: 6,754
Taking a crack at this... Try this article:

Tally Tables

With a tally table, you could whip up something like this; note that my naming is a bit sketchy, because this is going to be mostly a test of concept:

SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE #Tally ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

SELECT N,CONVERT(DateTime,32500+N) AS Dates
INTO #Temp
FROM #Tally
ORDER BY 32500+N ASC

SELECT N,CONVERT(DateTime,32500+N) AS Dates
INTO #Temp2
FROM #Tally

SELECT b.Dates,a.Dates FROM #Temp2 a
INNER JOIN #Temp b
ON a.N >= B.N AND a.N < b.N+7
WHERE DATEPART(DW,b.Dates) = 4
ORDER BY b.Dates ASC

This is going to give you a set of dates from 1988 to 2019, and a pair of columns; one will have the Wednesday of a particular week (your "starting point", in essence), and the other will contain all of the dates that would fall into this week. The dates will "belong" to the week on the left; in essence, you'd run an update from here to set the actual date to the "starting point" date.

This is a rough conceptual example, since we don't have sample data and so forth; this code will probably need a good bit of adaptation, but it should be a good starting point. Please include table definitions and sample data if you'd like more clarification.




-
Post #1497984
Posted Wednesday, September 25, 2013 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:40 AM
Points: 48, Visits: 117
A user on another forum suggested this:

DATEADD(dd,-DATEDIFF(dd,2, YourDateColumn)%7,CAST(YourDateColumn AS DATE))

DATEDIFF(dd,2,YourDateColumn) calculates the number of days between the value in YourDateColumn and the date represented by the number 2 - which is Jan 3, 1900 - which happened to be a Wednesday.

So DATEDIFF(dd,2,YourDateColumn)%7 will be zero if YourDateColumn is a Wednesday, 1 if it is Thursday, 2 if it is Friday and so on.

When you subtract 0 days from Wed, or 1 day from Thursday, or 2 days from Friday and so on, you get to Wednesday. Hence the "-DATEDIFF(dd,2,YourDateColumn)%7".
Post #1498429
Posted Wednesday, September 25, 2013 11:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 35,266, Visits: 31,758
paul.j.kemna (9/24/2013)

I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.


I believe the best thing to do would be to add WeekStartWednesday and DayOfWeekWednesday to your calendar table. It also sounds like you may have to change a wad of code whether you can make this Calendar table change or not.

I'd also be interested in the rules behind determining what the first and last week of any given year would be.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1498505
Posted Wednesday, September 25, 2013 1:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:40 AM
Points: 48, Visits: 117
We don't own the calendar table, so that is not really an option.

Anyway, my boss and I came up with this code as an option as well:

DECLARE @someDate as DATE = '2013-09-26'

select CASE WHEN DATEPART(DW, @someDate) >= 4 THEN DATEADD(d,4-DATEPART(DW, @someDate), @someDate) ELSE
DATEADD(d,4-DATEPART(DW, DATEADD(D, -7, @someDate)),DATEADD(D, -7, @someDate)) END

Weeks of the year is not necessary in this instance. We simply needed to be able to group by a week start of Wednesday, and display the past X weeks. (I chose 8 as a place to start).

PK
Post #1498563
Posted Wednesday, September 25, 2013 4:26 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
paul.j.kemna (9/25/2013)
We don't own the calendar table, so that is not really an option.

Anyway, my boss and I came up with this code as an option as well:

DECLARE @someDate as DATE = '2013-09-26'

select CASE WHEN DATEPART(DW, @someDate) >= 4 THEN DATEADD(d,4-DATEPART(DW, @someDate), @someDate) ELSE
DATEADD(d,4-DATEPART(DW, DATEADD(D, -7, @someDate)),DATEADD(D, -7, @someDate)) END

Weeks of the year is not necessary in this instance. We simply needed to be able to group by a week start of Wednesday, and display the past X weeks. (I chose 8 as a place to start).

PK


That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.

Try this to return the datetime of the Wednesday on or before @someDate at time 00:00:00.000 (midnight). You can cast the result back to DATE if you prefer.
declare @someDate as DATE = '2013-09-26'
Select Wed = dateadd(dd,((datediff(dd,'17530103',@someDate)/7)*7),'17530103')


More info here:
Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307


Edit: Modified code to use parameter of type DATE



Post #1498619
Posted Thursday, September 26, 2013 6:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 35,266, Visits: 31,758
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.


Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1498808
Posted Thursday, September 26, 2013 9:25 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.


Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.


I wanted the OP to be aware of the limitations of their method. They are certainly free to ignore my wonderful advice.

I prefer to have code that works as expected under the widest possible conditions, especially if it's no harder to code.

People may be operating with different default languages than our "normal" US English, and that can change the setting of DATEFIRST, and the client application can also define that setting in an unexpected way.



Post #1498922
Posted Thursday, September 26, 2013 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:54 PM
Points: 35,266, Visits: 31,758
Michael Valentine Jones (9/26/2013)
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.


Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.


I wanted the OP to be aware of the limitations of their method. They are certainly free to ignore my wonderful advice.

I prefer to have code that works as expected under the widest possible conditions, especially if it's no harder to code.

People may be operating with different default languages than our "normal" US English, and that can change the setting of DATEFIRST, and the client application can also define that setting in an unexpected way.


Absolutely agreed across all points especially the part about it not being any more difficult. I was actually talking about putting SET DATEFIRST into the actual code, though. If someone can't figure out the integer math, I can't see why you couldn't use SET DATEFIRST for the session. People seem almost phobic about its use.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1499120
Posted Friday, September 27, 2013 5:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:40 AM
Points: 48, Visits: 117
Isn't SET DATEFIRST a sever wide setting?
Post #1499326
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse