Solved

How to select Week Start and Week End dates

Posted on 2016-11-17
5
36 Views
Last Modified: 2017-01-05
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

0
Comment
Question by:Scott Baldridge
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41891808
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
 

Author Comment

by:Scott Baldridge
ID: 41891897
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
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41891959
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41892087
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
 

Author Closing Comment

by:Scott Baldridge
ID: 41892865
Thank you for helping!!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now