April 22, 2011 at 5:38 am
Hi Alll,
We have requirement to calculate the last data of the corresponding week .
Let me explain you the scenario , we are having time table that update on daily basis having date till today .In that table i am having column Week and week day , So in this table there will be all date from previous week .Let for a example for week 16 there are all seven day , for running week we are having latest date till today i.e
Week column Day Column Required Column
Week 17 2011 15 april 2011 W/E 21 2011
Week 17 2011 16 april 2011 W/E 21 2011
Week 17 2011 17 april 2011 W/E 21 2011
Week 17 2011 18 april 2011 W/E 21 2011
Week 17 2011 19 april 2011 W/E 21 2011
Week 17 2011 20 april 2011 W/E 21 2011
Week 17 2011 21 april 2011 W/E 21 2011
Week 18 2011 22 april 2011 w/E 29 2011
So as in above table in case of Week number 17 , it's not a problem to show W/e 21 2011, We calculating maximum for that week showing it in third Column but in case of week 18 , we are facing problem to get w/e 29 2011 as in our table we do not have that day.....
We are in very critical production release , Please guide me to implement required Column in case of future .We are trying our best but no luck till data .
Many Many Thanks ,
Alok
April 22, 2011 at 2:36 pm
DECLARE @date DATE = getdate()
SELECT DATEADD(DAY,7-DATEPART(dw,@date),@date)
Just substitute your date column for @date in the DATEADD function.
The DATEPART() function with the 'dw' parameter returns the day of the week (1-7) for a given date. Subtract that from 7 and you know how many days to add to that date to get the last day of the week.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 22, 2011 at 6:10 pm
getalok_akg (4/22/2011)
Hi Alll,We have requirement to calculate the last data of the corresponding week .
Let me explain you the scenario , we are having time table that update on daily basis having date till today .In that table i am having column Week and week day , So in this table there will be all date from previous week .Let for a example for week 16 there are all seven day , for running week we are having latest date till today i.e
Week column Day Column Required Column
Week 17 2011 15 april 2011 W/E 21 2011
Week 17 2011 16 april 2011 W/E 21 2011
Week 17 2011 17 april 2011 W/E 21 2011
Week 17 2011 18 april 2011 W/E 21 2011
Week 17 2011 19 april 2011 W/E 21 2011
Week 17 2011 20 april 2011 W/E 21 2011
Week 17 2011 21 april 2011 W/E 21 2011
Week 18 2011 22 april 2011 w/E 29 2011
So as in above table in case of Week number 17 , it's not a problem to show W/e 21 2011, We calculating maximum for that week showing it in third Column but in case of week 18 , we are facing problem to get w/e 29 2011 as in our table we do not have that day.....
We are in very critical production release , Please guide me to implement required Column in case of future .We are trying our best but no luck till data .
Many Many Thanks ,
Alok
I believe I can fix all of this but I need to know what defines the first day of the first week of the year for all years? For example, are you following ISO weeks? SQL Server weeks? Something else?
--Jeff Moden
April 23, 2011 at 2:13 am
Hi Flatline,
Many many thanks .This is what , i was looking but in our application week end on friday.So our week start on Saturday and end on friday .Let me elaborate more :
Case 1 ::
DECLARE @date DATE = '04/22/2011'
SELECT DATEADD(DAY,7-DATEPART(dw,@date),@date)
output :: 2011-04-23
But actually , it should be :2011-04-22(that is friday of that week)
Case 2 ::
DECLARE @date DATE = '04/24/2011'
SELECT DATEADD(DAY,7-DATEPART(dw,@date),@date)
output :: 2011-04-30 --- (In our application it should come 29th April that friday ).
Can we customize this to fullfill above requirement to consider week from Saturday to friday .
Many Many thanks,
Alok
April 23, 2011 at 2:31 am
HI Jeff Moden,
Many many thanks for your reply , In my application , we are considering week from Saturday to friday .Let me elaborate more :
Week 1 (for 2011) -- 1st jan 2011 to 7th Jan 2011)
Week 2 (2011) ------8th jan 2011 to 14th jan 2011) and so on .
Many many thanks,
Alok
April 23, 2011 at 2:40 am
getalok_akg (4/23/2011)
Hi Flatline,Many many thanks .This is what , i was looking but in our application week end on friday.So our week start on Saturday and end on friday .Let me elaborate more :
Case 1 ::
DECLARE @date DATE = '04/22/2011'
SELECT DATEADD(DAY,7-DATEPART(dw,@date),@date)
output :: 2011-04-23
But actually , it should be :2011-04-22(that is friday of that week)
Easy to fix. Just change Flatlines original to account for the fact that your week begins Saturday but you have SQL configured so that SQL weeks begin on Sunday:
DECLARE @date DATE = getdate()
DECLARE @day int = DATEPART(dw,@date)
SELECT DATEADD(DAY,CASE @day WHEN 7 THEN 6 ELSE 6-@day END,@date)
The SQL days 1,2,3,4,5,6,7 are your days 2,3,4,5,6,7,1.
edit: if instead of one whole week when Saturday is in one year and the next Friday is in another year you have two fractional weeks then this code won't work inthe first fractional week; in that case you would have to test whether the end of week date calculated as above was in the same year as the date you were looking at, and if it wasn't change then end date to 31 DEC. But that's an unusual thing to do.
Tom
April 23, 2011 at 4:10 pm
getalok_akg (4/23/2011)
HI Jeff Moden,Many many thanks for your reply , In my application , we are considering week from Saturday to friday .Let me elaborate more :
Week 1 (for 2011) -- 1st jan 2011 to 7th Jan 2011)
Week 2 (2011) ------8th jan 2011 to 14th jan 2011) and so on .
Many many thanks,
Alok
2011 was convenient for that. I'm trying to build a general rule for code to follow, though, so you don't have to fix the code every year. Please explain what week 1 of Jan 2009 and Jan 2012 would be and why. Then I can do this for you easily, I believe.
--Jeff Moden
April 26, 2011 at 7:13 am
Hi All,
Thanks all of you for your support and response .
Many many Thanks to Tom Thomson ,Jeff Moden ,Flatline .
Below mention Code is working as par our requirement
DECLARE @date DATE = getdate()
DECLARE @day int = DATEPART(dw,@date)
SELECT DATEADD(DAY,CASE @day WHEN 7 THEN 6 ELSE 6-@day END,@date)
Again many many thanks all of you .
Regards,
Alok S
April 27, 2011 at 6:42 am
Hi Alok,
Updated this as per expected requirment
Case 1 ::
DECLARE @date DATETIME
set @date = '04/22/2011'
SELECT DATEADD(DAY,7-DATEPART(dw,@date) - 1,@date)
output :: 2011-04-22
But actually , it should be :2011-04-22(that is friday of that week)
Case 2 ::
DECLARE @date DATETIME -- = '04/24/2011'
set @date = '04/24/2011'
SELECT DATEADD(DAY,7-DATEPART(dw,@date) -1,@date)
output :: 2011-04-29 --- (In our application it should come 29th April that friday ).
Try this 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply