Finding week ending date for each week of the year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding week ending date for each week of the year

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-07 : 11:19:51

Hi

I have the following script, which displays the week number of the year and the number of events that are taking place that week:

select DATEPART(wk, (eventdate)) as Week_Number, count(eventdate) as Number_of_Events

from tblbooking b
inner join tblbookingvsvenue bvv on b.chosenEventID = bvv.bookingVsVenueID

where b.bookingstatusid = 6

Instead of showing the week number is it possible to display the end date of each of these weeks i.e the date of the last sunday each week?

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 11:24:44

SELECT DATE FROM F_TABLE_DATE('20070101', '20071231')
WHERE WEEKDAY_NAME = 'Sun'

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-07 : 11:36:26

HI

Will that show all the week ending dates for the whole year?

Thanks

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 11:52:48

--is this what you are after?

--if your connections all have the default 7 for DATEFIRST try this...
select eventdate + (((8-datepart(dw, eventdate))) % 7) as following_sunday

select @@datefirst --this can be used to check the the DATEFIRST value
SET DATEFIRST 7 -- this can be used to force the connection value

--or to programatically adjust for any given DATEFIRST try this more complex formula...
select
eventdate --start date
+( ((8-datepart(dw, eventdate)) --plus number of days until the end of the week
+ (7-@@datefirst) --plus the difference is users end of week and sunday
) % 7 ) --limited to adding less than a full week

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-08 : 03:37:32

Hi

Ideally I was looking for the outputted query to look like this

Week_Ending No_of_Events
07/01/2007 6
14/01/2007 15
21/01/2007 12
…
…
11/11/2007 16
18/11/2007 12

I hope this helps (Sorry about the formatting)

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-08 : 03:49:07

did you try anon's code? i'm sure it gives you what you're after...


declare @t table (eventdate datetime)
insert into @t
			select '20071106'
union all	select '20071106'
union all	select '20071108'
union all	select '20071110'
union all	select '20071112'
union all	select '20071112'
union all	select '20071113'

select eventdate + (((8-datepart(dw, eventdate))) % 7) as following_sunday
      , count(eventdate) as Number_of_Events
from @t
group by eventdate + (((8-datepart(dw, eventdate))) % 7)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-08 : 04:40:57

Thanks for that, it works!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 04:51:14

Not for me that have different @@DATEFIRST setting.

SELECT		FollowingSunday,
		COUNT(*) AS NumberOfEvents
FROM		(
			SELECT		DATEADD(WEEK, DATEDIFF(WEEK, '19000101', EventDate), '19000107') AS FollowingSunday
			FROM		@t
		) AS d
GROUP BY	FollowingSunday

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-08 : 06:32:47

Is that for the week ending on sunday, or week beginning on sunday?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 07:18:29

Ending on a sunday.
Feel free to change to "19000107" date to accomodate the "ending" weekday you are interested in.

E 12°55'05.25"
N 56°04'39.16"

billsack
Starting Member

35 Posts