Solved

How to select Week Start and Week End dates

Posted on 2016-11-17
5
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Scott Pletcher 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Failover Cluster Primary Nodes Current Vote = 0 5 39
TDE for SQL Web Edition 1 43
Creating Scalar Function 3 20
Query Task 8 23
I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

710 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