Solved

How to select Week Start and Week End dates

Posted on 2016-11-17
5
24 Views
Last Modified: 2016-11-18
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

19 Experts available now in Live!

Get 1:1 Help Now