week range

Question

Post reply

week range

weston_086

SSCommitted

Points: 1645
More actions
February 29, 2012 at 5:13 pm

#252366

how i can get the week range for
date weekrange
2012-05-30 5/27-6/2
2012-06-02 5/27-6/2
2012-11-07 11/4-11/10
2012-11-13 11/11-11/17
i want to update weekrange as above as per the values in date column.i have thousands of rows in date column and i want to update the weekrange as per date column
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

i am unclear with your requirement? what value u wnat to update the weekrange with?

Follow this post for Forum Etiquettes : Forum Etiquettes[/url]

weston_086 SSCommitted Points: 1645 More actions · Answer 2

I think this can help you understand what i want

create table dt(dates datetime,wrange varchar(40))

insert into dt values('2012-02-29 00:00:00.000',null),

('2012-05-30 00:00:00.000',null),

('2007-01-01 00:00:00.000',null),

('2007-01-08 00:00:00.000',null)

select * from dt

i want to update the wrange column in such a way that it should show me that in which week range values of dates column is .

for e.g value date 2012-05-30 is in the week which starts on 05/27(sunday)( and week ends in 06/02saturday)(for better clerfification you can check calender ).

similarly date 2012-02-29(today's date) is in the week range which starts on 02/26(sunday) and this week ends on 03/03(saturday).so in wrange column for today's date

in want 02/26-03/03

so in dt table after updating wrange column i want result which should be like

insert into dt values ('2012-02-29 00:00:00.000','02/26-03/03'),

('2012-05-30 00:00:00.000','05/27-06/02'),

('2007-01-01 00:00:00.000','1/1-1/7'),

('2007-01-08 00:00:00.000','1/8-1/14')

i think we can use that query but it is not giving me that result

update dt

set WRange=WkR from

(select dates, convert(varchar,datepart(m,min(dates))) + '/' + convert(varchar,datepart(d,min(dates)))

+ ' - ' +convert(varchar,datepart(m,max(dates))) + '/' + convert(varchar,datepart(d,max(dates))) as 'WkR'

from dt)

group by [dates]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

This shows how to find the first and last day of the week for a given date, with the week starting on Sunday and ending on Saturday. You can refer to the CONVERT function in SQL Server Books Online to figure out how to format them in your desired format.

select

a.*,

WeekStarting= dateadd(dd,(datediff(dd,-53684,a.DT)/7)*7,-53684),

WeekEnding= dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)+6,-53684)

from

( -- Test Data

select DT = convert(datetime,'2012-02-29 00:00:00.000')union all

select DT = convert(datetime,'2012-05-30 00:00:00.000')union all

select DT = convert(datetime,'2007-01-01 00:00:00.000')union all

select DT = convert(datetime,'2007-01-08 00:00:00.000')

) a

Results:

DT WeekStarting WeekEnding

----------------------- ----------------------- -----------------------

2012-02-29 00:00:00.000 2012-02-26 00:00:00.000 2012-03-03 00:00:00.000

2012-05-30 00:00:00.000 2012-05-27 00:00:00.000 2012-06-02 00:00:00.000

2007-01-01 00:00:00.000 2006-12-31 00:00:00.000 2007-01-06 00:00:00.000

2007-01-08 00:00:00.000 2007-01-07 00:00:00.000 2007-01-13 00:00:00.000

The week ranges you posted for 2007-01-01 and 2007-01-08 seem off, since you are showing weeks starting on Monday and ending on Sunday for those two dates. If those dates are what you really want, then you need to explain your actual requirements.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Try this:

SELECT DATES

, CrsApp2.DtAd WeekStart

, DATEADD(DD, 6, CrsApp2.DtAd) WeekEnd

FROM dt

CROSS APPLY ( SELECT DATEDIFF(WK,0,DATES) ) CrsApp (DtDf)

CROSS APPLY ( SELECT DATEADD(WK ,CrsApp.DtDf ,-1 ) ) CrsApp2 (DtAd)

I see a flaw in your sample date; for '2007-01-01' , the week start at 2006-12-31. But i see that you have started 2007-01-01 at 2007-01-01 itself. Is that a specific requirement?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

ColdCoffee

SSC-Dedicated

Points: 39972

February 29, 2012 at 9:48 pm

#1453450

Whooops.. MVJ beat me to it...

weston_086 SSCommitted Points: 1645 More actions · Answer 6

thanks all

sorry 2007-01-01 and 2007-01-08 in last update was flaw.

-----

Even i was working with the same way with this

update dt

set WRange=Wrannn from

(select convert(varchar,datepart(m,min(dates))) + '/' +

convert(varchar,datepart(d,min(dates))) + ' - ' + convert(varchar,datepart(m,max(dates))) + '/' + convert(varchar,datepart(d,max(dates))) as 'wrannn'

from dt)t

but when i run this query it just update wrange column with same weekrange irrespective what date value is in dates column. As in dates column i have more than 10000 rows.i want that wrange would update according to the values of dates column.

weston_086 SSCommitted Points: 1645 More actions · Answer 7

i think this problem was kind weird .but i got the solution after all

UPDATE dt

SET dt.wrange = CrsApp4.WkStrt + ' - ' + CrsApp4.WkEnd

FROM dt

CROSS APPLY (

SELECT DATEDIFF(WK,0,dt.Dates)

) CrsApp (DtDf)

CROSS APPLY (

SELECT DATEADD(WK ,CrsApp.DtDf ,-1 )

) CrsApp2 (WkStrt)

CROSS APPLY (

SELECT DATEADD(DD, 6, CrsApp2.WkStrt)

) CrsApp3 (WkEnd)

CROSS APPLY (

SELECT CONVERT(VARCHAR(5), CrsApp2.WkStrt , 101) ,

CONVERT(VARCHAR(5), CrsApp3.WkEnd , 101)

) CrsApp4( WkStrt, WkEnd)

i think it may be helpful for others.

Thanks all for your effort

siva 20997 Ten Centuries Points: 1181 More actions · Answer 8

I have this problem in all my apps like accounting, Billing,TimeSheets etc

I keep a table called calendar which is always in the system ( Date, WeekNo, PeriodNo) etc

then all my queries does a external joi to get the weekno or periodno

It is more like you got a pre-calculated table of dates with all your divisions (like 13 Month year etc)

Quite a few use this technique and it is called Calendar Table and there is some discussion on this forum about it