Scott Baldridge
asked on
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.
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
*/
ASKER
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?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for helping!!!
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()