November 14, 2012 at 12:43 pm
Hi all,
I am having a bit of a problem trying to calculate an employees working pattern i.e. if I specify a date range of a month/year I need a list of dates and an indicator to show if they are/were scheduled to work that day. The problem I have is down to the way we are storing this information, please see some sample data:
USE tempdb;
IF OBJECT_ID('dbo.#Employee','U') IS NOT NULL
DROP TABLE dbo.#Employee
CREATE TABLE #Employee (
Empcode VARCHAR(10) PRIMARY KEY,
Firstname VARCHAR(24) NULL,
Surname VARCHAR(24) NULL
)
IF OBJECT_ID('dbo.#Working_Pattern','U') IS NOT NULL
DROP TABLE dbo.#Working_Pattern
CREATE TABLE #Working_Pattern (
StartDate DATETIME PRIMARY KEY,
Employee VARCHAR(10) NOT NULL,
Monday CHAR(1) NULL,
Tuesday CHAR(1) NULL,
Wednesday CHAR(1) NULL,
Thursday CHAR(1) NULL,
Friday CHAR(1) NULL,
Saturday CHAR(1) NULL,
Sunday CHAR(1) NULL
)
INSERT INTO #Employee (Empcode, Firstname, Surname)
SELECT '0001','John','Smith' UNION ALL
SELECT '0002','Dave','Roberts'
INSERT INTO #Working_Pattern (StartDate, Employee, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
SELECT '2012/01/01','0001','Y','Y','Y','Y','Y','N','N' UNION ALL
SELECT '2012/01/08','0001','N','Y','Y','Y','Y','Y','N' UNION ALL
SELECT '2012/01/22','0001','N','N','N','Y','Y','Y','N' UNION ALL
SELECT '2012/03/01','0001','Y','Y','Y','Y','Y','Y','N' UNION ALL
SELECT '2012/09/08','0001','N','Y','Y','N','Y','Y','N' UNION ALL
SELECT '2009/01/01','0002','Y','Y','Y','Y','N','N','N'
SELECT *
FROM #Employee
SELECT *
FROM #Working_Pattern
As you can see employee 0002 only has one record in the Working_Pattern table. This means that this employee has only ever had one working pattern and is still currently working the same pattern. I need a query which I can specify a start and end date e.g. 01/01/2012 to 31/12/2012 which would return a row for each day in that range and an indicator as to if it was a working day or not.
The problem gets a bit more complicated when you look at employee 0001 who has had several changes throughout the year. I would still need to be able to produce a similar output taking the working pattern changes into consideration.
I hope you can help, apologies if I haven't explained myself well enough.
November 14, 2012 at 12:58 pm
have you tried using an unpivot function? that may be a good approach assuming I have understood your problem correctly.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
November 14, 2012 at 1:00 pm
i have a question about #Working_Pattern. This table holds the days of the week an employee works with the start date being the date the new schedule became effective?
I think i have a very efficient method using a calendar table if that is correct.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 14, 2012 at 1:08 pm
capnhector (11/14/2012)
i have a question about #Working_Pattern. This table holds the days of the week an employee works with the start date being the date the new schedule became effective?I think i have a very efficient method using a calendar table if that is correct.
That is exactly what this table is for. Basically if there is only one record for the employee in the #Working_Pattern table that is their working pattern up until a new date is entered with a new pattern and so on.
November 14, 2012 at 2:17 pm
This works but some of the others on the forum may be able to make it better.
Here is how the code works: First we declare and set our start and end date. The second part of the code is the cteTally and cteCalendarTable which i have included in the code so it is self contained if you all ready have a calendar table you do not need these 2 parts of the code. (If not ill have links at the bottom for all the resources so you can see how the code works.) the second part, ScheduleID, is to make up for the lack of an id we can use to self join the pattern table to get start and end dates. if your schedule table gets a slight redesign we do not need this either. The third part is the real meat of the code, we normalize the structure of the schedule table and get the start and end dates for each schedule as well as get the day of the week and whether the employee works as rows instead of columns.
the BETWEEN is to make sure we dont calculate this information for every single entry in the table and can be omitted if you only have a few entries. however if you have allot of entries this may speed things up quite a bit.
The final query joins the calendar table (which because both the tally and calendar table are CTE's i have limited the calendar table to only the dates between the start and end date, if you have a calendar table in your database you would limit the range of the query here as well with a where clause)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '2012-01-09', @EndDate = '2012-01-30'
;WITH cteTally AS (SELECT TOP (DATEDIFF(DD,@StartDate,@EndDate) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))b(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))c(N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))d(N)),
cteCalendarTable AS (SELECT DATEADD(DD,N,@StartDate) AS [Date], DATENAME(DW,DATEADD(DD,N,@StartDate)) AS WeekDay
FROM cteTally),
ScheduleID AS (SELECT ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY StartDate) AS ID, *
FROM #Working_Pattern),
ScheduleRange AS (SELECT a.StartDate AS StartDate, ISNULL(DATEADD(DD,-1,b.StartDate), DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0)) AS EndDate, a.Employee,
CASE c.WeekDay
WHEN 'Monday' THEN a.Monday
WHEN 'Tuesday' THEN a.Tuesday
WHEN 'Wednesday' THEN a.Wednesday
WHEN 'Thursday' THEN a.Thursday
WHEN 'Friday' THEN a.Friday
WHEN 'Saturday' THEN a.Saturday
WHEN 'Sunday' THEN a.Sunday
END AS Schedule,
c.WeekDay
FROM ScheduleID a
LEFT JOIN ScheduleID b
ON a.ID = b.ID - 1
AND a.Employee = b.Employee
CROSS APPLY (VALUES ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday'), ('Sunday'))c([WeekDay])
WHERE a.StartDate BETWEEN (SELECT TOP 1 StartDate
FROM #Working_Pattern
WHERE StartDate <= @StartDate
AND Employee = a.Employee
ORDER BY StartDate DESC)
AND (SELECT TOP 1 StartDate
FROM #Working_Pattern
WHERE StartDate <= @EndDate
AND Employee = a.Employee
ORDER BY StartDate DESC))
SELECT a.Date, b.Employee, b.Schedule, b.WeekDay
FROM cteCalendarTable a
INNER JOIN ScheduleRange b
ON a.Date BETWEEN b.StartDate AND b.EndDate
AND a.WeekDay = b.WeekDay
ORDER BY b.Employee, a.Date
http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx Information on UnPivot methods
http://www.sqlservercentral.com/articles/T-SQL/62867/ Tally Tables and what they are
http://www.sqlservercentral.com/scripts/Date/68389/ Calendar table script.
http://www.sqlservercentral.com/articles/Test-Driven+Development/71075/ One simple use of a calendar table.
EDIT:
Added link to calendar table creation script.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 15, 2012 at 2:30 am
This does exactly what I need thank you. I appreciate you taking the time to not only provide a solution but to also explain what it is doing.
I am going to spend some time going through all of this today to make sure I fully understand what it is doing.
November 15, 2012 at 3:53 am
I am having a slight problem, the solution works perfectly for records in the past but will only display a working pattern up to the current date.
For example if I specify a start date of 2012-08-01 and an end date of 2012-12-31 it will give me the working pattern from 2012-08-01 up to today's date.
Is there any way to extend this to show dates in the future using the employees last entered working pattern?
November 15, 2012 at 4:00 am
Apologies that I keep posting but I have just amended part of the ISNULL section to DATEADD(DD,DATEDIFF(DD,0,GETDATE()),3650))
and this now allows me to extend the working pattern forwards, as I am still not fully understanding all of the
code would someone be able to confirm that this is correct?
November 15, 2012 at 6:18 am
Raastarr (11/15/2012)
Apologies that I keep posting but I have just amended part of the ISNULL section toDATEADD(DD,DATEDIFF(DD,0,GETDATE()),3650))and this now allows me to extend the working pattern forwards, as I am still not fully understanding all of the
code would someone be able to confirm that this is correct?
for simplicity i used todays date however you could change the second part of the ISNULL to @EndData or a constant which would probably work better than modifying the DATEADD in the manner you have.
Remember if you dont understand the code please dont put it in production, your the one who will have to support the application in the future and if something breaks you will need to know how to fix it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 15, 2012 at 7:01 pm
Boy! Did that end up being a whole lot messier than I thought it would be when I got started on it!
Nevertheless, here's an alternate solution (I think):
DECLARE @StartDT DATETIME = '2010-01-01'
,@EndDT DATETIME = '2012-12-31'
;WITH Tally (n) AS (
SELECT TOP (DATEDIFF(day, @StartDT, @EndDT) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES ($),($),($),($),($),($),($),($),($),($)) a(n1)
CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) b(n1)
CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) c(n1)),
FirstPattern AS (
SELECT StartDate, Employee
,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
,Firstname, Surname
,n=ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY StartDate)
FROM #Working_Pattern
JOIN #Employee ON Empcode = Employee),
WorkPatterns AS (
SELECT StartDate
,Employee
,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
,Firstname, Surname
FROM (
SELECT StartDate=CASE WHEN StartDate < @StartDT THEN @StartDT ELSE StartDate END
,Employee
,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
,Firstname, Surname
FROM FirstPattern
WHERE n = 1
UNION
SELECT StartDate
,Employee
,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
,Firstname, Surname
FROM FirstPattern
WHERE StartDate >= @StartDT) a)
SELECT Empcode, Firstname, Surname
,[WorkDate]=DATEADD(day, n, @StartDT)
,WorkDay=DATENAME(weekday, DATEADD(day, n, @StartDT))
,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM #Employee a
CROSS APPLY Tally b
CROSS APPLY (
SELECT TOP 1 StartDate
,Employee
,Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
FROM WorkPatterns
WHERE Empcode = Employee AND StartDate <= DATEADD(day, n, @StartDT)
ORDER BY StartDate) c
WHERE SUBSTRING(Sunday+Monday+Tuesday+Wednesday+Thursday+Friday+Saturday
,DATEPART(weekday, DATEADD(day, n, @StartDT)), 1) = 'Y'
Edit: Forgot to include the final WHERE that only lists working day records (requires that @@DATEFIRST = 7)!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply