Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to select Week Start and Week End dates

Posted on 2016-11-17
5
Medium Priority
?
69 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 27

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 27

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

916 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