Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to calculate employees working pattern Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 42, Visits: 2,212
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.
Post #1384816
Posted Wednesday, November 14, 2012 12:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:26 AM
Points: 823, Visits: 1,203
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.
Post #1384823
Posted Wednesday, November 14, 2012 1:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1384826
Posted Wednesday, November 14, 2012 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 42, Visits: 2,212
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.
Post #1384828
Posted Wednesday, November 14, 2012 2:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1384854
Posted Thursday, November 15, 2012 2:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 42, Visits: 2,212
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.
Post #1385018
Posted Thursday, November 15, 2012 3:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 42, Visits: 2,212
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?
Post #1385061
Posted Thursday, November 15, 2012 4:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 42, Visits: 2,212
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?
Post #1385067
Posted Thursday, November 15, 2012 6:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
Raastarr (11/15/2012)
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?


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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1385115
Posted Thursday, November 15, 2012 7:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 3,617, Visits: 5,234
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385414
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse