SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shift week to Wed - Tues


Shift week to Wed - Tues

Author
Message
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 155
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
Andrew Kernodle
Andrew Kernodle
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 8135
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.

- :-D
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 155
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".
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88526 Visits: 41130
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 155
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
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5914 Visits: 11771
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88526 Visits: 41130
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5914 Visits: 11771
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. Cool

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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88526 Visits: 41130
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. Cool

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
paul.j.kemna
paul.j.kemna
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 155
Isn't SET DATEFIRST a sever wide setting?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search