How to select Week Start and Week End dates

Hello, I have the following test code where I need to get the expected results as shown at the bottom of the code.

I have a table of events with a start and end date. Sometimes the start and end date spans weeks. I need to select the week start and week end for each employeeid and for each week.

I need help with a statement to insert into #EMPWEEKS the weeks for each employee. Any help or advice would be appreciated.


IF OBJECT_ID('tempdb..#EMPLOYEE_EVENT') IS NOT NULL
	DROP TABLE #EMPLOYEE_EVENT
CREATE TABLE #EMPLOYEE_EVENT
(
	[EmployeeId] [int] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[OccupationType] [nvarchar](50) NULL,
	[EventType] [nvarchar](50) NOT NULL,
	[HolidayName] [nvarchar](50) NOT NULL,
	[HolidayOfficeId] [int] NOT NULL
)

Insert #EMPLOYEE_EVENT(EmployeeId,StartDate,EndDate,OccupationType,EventType,HolidayName,HolidayOfficeId)
Select 825,'2016-11-21 00:00:00.000','2016-11-21 00:00:00.000','All Day', 'PTO','', 1

Insert #EMPLOYEE_EVENT(EmployeeId,StartDate,EndDate,OccupationType,EventType,HolidayName,HolidayOfficeId)
Select 825,'2016-11-28 00:00:00.000','2016-12-6 00:00:00.000','All Day', 'PTO','', 1

Insert #EMPLOYEE_EVENT(EmployeeId,StartDate,EndDate,OccupationType,EventType,HolidayName,HolidayOfficeId)
Select 725,'2016-11-28 00:00:00.000','2016-11-28 00:00:00.000','All Day', 'PTO','', 1


IF OBJECT_ID('tempdb..#EMPEVENTS') IS NOT NULL
	DROP TABLE #EMPEVENTS

CREATE TABLE #EMPEVENTS
(
	[EmployeeId] [int] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[OccupationType] [nvarchar](50) NULL,
	[EventType] [nvarchar](50) NOT NULL,
	[HolidayName] [nvarchar](50) NOT NULL,
	[HolidayOfficeId] [int] NOT NULL
)
INSERT #EMPEVENTS
SELECT 
	 [EmployeeId]
	,[StartDate]
	,[EndDate]
	,[OccupationType]
	,[EventType]
	,[HolidayName]
	,[HolidayOfficeId]
FROM #EMPLOYEE_EVENT
WHERE StartDate > GETDATE()

IF OBJECT_ID('tempdb..#EMPWEEKS') IS NOT NULL
	DROP TABLE #EMPWEEKS

CREATE TABLE #EMPWEEKS
(
	 [EmployeeId] [int] NOT NULL
	,[WeekStartDate] [datetime] NOT NULL
	,[WeekEndDate] [datetime] NOT NULL
)

/* Need help with statement to insert into #EMPWEEKS to obtain expected results below */

/*
Expected results from running this query:
SELECT * FROM #EMPWEEKS

EmployeeId	WeekStartDate				WeekEndDate	
825			2016-11-20 00:00:00.000		2016-11-26 00:00:00.000
825			2016-11-27 00:00:00.000		2016-12-03 00:00:00.000
825			2016-12-04 00:00:00.000		2016-12-10 00:00:00.000
725			2016-11-28 00:00:00.000		016-11-28 00:00:00.000

*/

Open in new window

Scott BaldridgeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
No, you definitely don't need a loop(s), instead a "standard" tally table -- a table of sequential numbers -- will do quite nicely.  I've used a cte to generate a tally table on the fly, as is very common in SQL:


;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
),
cteTally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    CROSS JOIN cteTally10 c3
)
--INSERT INTO #EMPWEEKS
SELECT ee.EmployeeId, DATEADD(DAY, t.number * 7, WeekStartDate) AS WeekStartDate,
    DATEADD(DAY, t.number * 7 + 6, WeekStartDate) AS WeekEndDate
FROM #EMPEVENTS ee
CROSS APPLY (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, StartDate) % 7, StartDate) AS WeekStartDate
) AS assign_alias_names_1
LEFT OUTER JOIN cteTally1000 t ON t.number BETWEEN 0 AND DATEDIFF(DAY, WeekStartDate, EndDate) / 7
0
 
Shaun KlineLead Software EngineerCommented:
Using a Sunday to Saturday week, this formula calculates Sunday's date:
SELECT DATEADD(DAY, -1 * DATEPART(WEEKDAY, GETDATE()) + 1, DATEDIFF(DAY, 0, GETDATE()))

This formula calculates Saturday's date:
DATEADD(DAY, 6, DATEADD(DAY, -1 * DATEPART(WEEKDAY, GETDATE()) + 1, DATEDIFF(DAY, 0, GETDATE())))

For your purposes, substitute your date for GETDATE()
0
 
Scott BaldridgeAuthor Commented:
Wouldn't I need to do some kind of looping to find the start and end of each week when the rows startdate and enddate span multiple weeks?
0
 
Shaun KlineLead Software EngineerCommented:
If events span multiple weeks, and you need a record for each week, you may want to consider building a calendar table as describe in Jim Horn's article. The calendar table will allow you to run a query using your dates to find the start/end dates for all weeks the events cover.
0
 
Scott BaldridgeAuthor Commented:
Thank you for helping!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.