June 26, 2012 at 1:25 pm
Dangit Jeff!
I like your approach better, but I came up with this... which invariably is very similar to yours...
First we turn it into a calendar table, then do the easy work. I have to agree with everybody... Please make a real calendar table and use that. Adding a calendarid to the calendar table to allow for multiple parallel calendars is not a big deal and it will save you so much heartache in the long run.
DECLARE @hiredate DATETIME = GETDATE(); --set me to the hire date of a given employee or make me a function parameter
WITH start
AS (
SELECT
calendarid,
calendaryear,
rc = ROW_NUMBER() OVER (PARTITION BY CalendarID ORDER BY CalendarYear, N),
poschar = SUBSTRING(calendardays,N,1)
FROM
tally
JOIN #myCalendars ON 1 = 1
WHERE
N < 366
AND SUBSTRING(calendardays,N,1) != ''
),
calendar_start
AS (
SELECT
calendarid,
calstart = MIN(DATEADD(yy,Calendaryear - 1900,0))
FROM
start
GROUP BY
calendarid
),
working_calendar
AS (
SELECT
a.calendarid,
Calendardate = DATEADD(dd,rc - 1,calstart),
RC = ROW_NUMBER() OVER (PARTITION BY a.calendarid ORDER BY rc)
FROM
start a
JOIN calendar_start b ON a.calendarid = b.calendarid
WHERE
poschar != 'U'
AND DATEADD(dd,rc - 1,calstart) >= @hiredate
)
SELECT
*
FROM
working_calendar a
WHERE
rc = 100
June 26, 2012 at 10:17 pm
gmrose (6/26/2012)
Hi Jeff,Let's use this:
--===== Create the test table
CREATE TABLE dbo.Employee
(
EmployeeID INT,
StartDate DATETIME,
CalendarID CHAR(4)
)
;
INSERT INTO dbo.Employee
(EmployeeID, StartDate)
SELECT 1, '20111215', 'A209' UNION ALL
SELECT 2, '20110113', 'L261' UNION ALL
SELECT 3, '20120113', 'S222' UNION ALL
SELECT 4, '20120114', 'A209' --Not a workday so not possible. Evaluation date will be NULL.
;
GO
This is similar to the setup I have. Thank you for your help.
gmrose
Apologies for the delay. I'll be back soon.
--Jeff Moden
June 26, 2012 at 10:32 pm
GMRose,
This will do as you ask... and it will still return a NULL where an Employee StartDate isn't a proper
WorkDay so that someone can be alerted to make a correction.
It does have a bit of a performance problem in the form of some "Hidden RBAR". I'm working on that.
SELECT e.EmployeeID, e.CalendarID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
OUTER APPLY (SELECT OffSetDate FROM dbo.GetOffSetDates(e.CalendarID,100) WHERE WorkDayDate = e.StartDate) offset
ORDER BY EvaluationDate
;
--Jeff Moden
June 27, 2012 at 12:11 am
Ok... I like the following better especially now that I know that we need to work with more than 1 calendar at a time.
First, here's a much improved function. It still bugs me to no end that when you call a CTE twice that it actually has to execute twice.
--===== Since we are dropping a real function for this experiment,
-- do it in a nice safe place that everyone has.
USE tempdb
;
--===== If the function already exists, drop it
IF OBJECT_ID('dbo.OffsetCalendar','IF') IS NOT NULL
DROP FUNCTION dbo.OffsetCalendar
GO
CREATE FUNCTION dbo.OffsetCalendar
(@pOffSetDays INT)
RETURNS TABLE
AS
RETURN
WITH
cteCreateWorkDayDates AS
( --=== Split each WorkDay out with an overall DayNumber so we can do an offset later.
-- This normalizes ALL of the calendars at once.
-- It also gets rid of the blank place holders for missing February 29ths.
SELECT c.CalendarID,
DayNumber = ROW_NUMBER() OVER (ORDER BY c.CalendarID,c.CalendarYear,t.N),
WorkDayDate = DATEADD(dd,t.N-1,CONVERT(CHAR(4),c.CalendarYear))
FROM dbo.myCalendars c
CROSS JOIN dbo.Tally t
WHERE t.N BETWEEN 1 AND LEN(c.CalendarDays)
AND SUBSTRING(CalendarDays,t.N,1) = '.'
)
--===== Create the future dates according to the @pOffSetDays
SELECT wd1.CalendarID,
wd1.WorkDayDate,
OffSetDate = wd2.WorkDayDate
FROM cteCreateWorkDayDates wd1
LEFT JOIN cteCreateWorkDayDates wd2
ON wd1.DayNumber + @pOffSetDays = wd2.DayNumber
;
GO
Here's how to use it against the updated version of the Employee table that GMRose provided...
SELECT e.EmployeeID, e.CalendarID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.OffsetCalendar(100) offset
ON e.CalendarID = offset.CalendarID
AND e.StartDate = offset.WorkDayDate
ORDER BY EvaluationDate
;
On my 10 year old single 1.8 GHz 32 bit desktop box, that takes 62 CPU ms and less than a half
second to display 5,120 rows. That's still not up to my normal standards but it's past 2AM and
I have to get some shuteye.
--Jeff Moden
June 28, 2012 at 5:59 am
Are you all set now?
--Jeff Moden
June 28, 2012 at 1:43 pm
Hi Jeff,
Thank you and everyone for your help. I started using the statements from your posting of 6/23/12 8:37PM and was able to get the data that I wanted. I then noticed that there were some recently hired employees whose evaluation dates were off by a day. Then I saw your posting from 6/27/12 at 7:11AM and used it. It appears to also have the evaluation dates one day too late.
For example: One of our agencies does three month evaluations at 65 days instead of five month evaluations at 100 days. Below are the statements that I have used for testing your 6/27/12 statements:
INSERT INTO dbo.Employee
(EmployeeID, StartDate, CalendarID)
SELECT 6, '20120301', 'L261' UNION ALL
SELECT 7, '20120508', 'L261'
;
GO
;
SELECT e.EmployeeID, e.CalendarID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.OffsetCalendar(65) offset
ON e.CalendarID = offset.CalendarID
AND e.StartDate = offset.WorkDayDate
WHERE E.EmployeeId in (6,7)
ORDER BY EvaluationDate
;
GO
Below are the results:
EmployeeIDCalendarIDStartDate EvaluationDate
6 L261 2012-03-01 00:00:00.0002012-06-01 00:00:00.000
7 L261 2012-05-08 00:00:00.0002012-08-10 00:00:00.000
Since the only unpaid days between the start of March and the end of August for this calendar are the weekends and the holidays on 2012-05-28 and 2012-07-04, when I count 65 days after StartDate 2012-03-01 I get EvaluationDate of 2012-06-01 which matches the value selected, but when I count 65 days after StartDate 2012-05-08 I get that the EvaluationDate should be 2012-08-09 instead of the selected value of 2012-08-10.
Again, thank you to everyone for their help.
gmrose
June 28, 2012 at 1:55 pm
It's a simple change. Change the following...
ON wd1.DayNumber + @pOffSetDays = wd2.DayNumber
... to ...
ON wd1.DayNumber + @pOffSetDays - 1 = wd2.DayNumber
--Jeff Moden
June 28, 2012 at 2:11 pm
I now have found out that the myCalendar table was set up worse than everyone thought. Columns 1 through 182 of the field named CalendarDays represent days January 1 though June 30 (and includes February 29) of the current year, but columns 183 through 366 represent days July 1 through December 31 of the prior year. This table contains fiscal years rather than calendar years. I agree that it should be replaced by something with a better logic, but since it is part of a third-party package, I cannot change its setup.
Jeff, if you have any ideas about adjusting the function to handle the odd setup of the myCalendar table, I would appreciate any suggestions that have. Thank you again.
gmrose
Also, I failed to mention that Holidays in the CalendarDays field are represented by 'H' and are paid days, so the clause from your statement that was SUBSTRING(CalendarDays,t.N,1) = '.' will need to be SUBSTRING(CalendarDays,t.N,1) in ('.','H').
June 28, 2012 at 8:17 pm
The code I wrote properly includes Feb 29th when it has a "." in it and ignores it when it has a " " in it. Of course, anything with a "U" in
it is ignored.
SELECT TOP 182
DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'2012')
FROM sys.all_columns
I don't know what else you mean when you talk of columns 1 to 182 including Jun 30th. If you exeute the code above, you'll see
that's precisely what is expected for 2012. It would be 182 for all of the years in your calendar tables except that non-Leap Years will
contain a blank which my code handles as an "ignore". If I understand all of this, I believe you're all set. If not, please explain a bit more
because I don't understand the problem you've just described.
--Jeff Moden
June 28, 2012 at 8:22 pm
gmrose (6/28/2012)
Also, I failed to mention that Holidays in the CalendarDays field are represented by 'H' and are paiddays, so the clause from your statement that was SUBSTRING(CalendarDays,t.N,1) = '.' will need to be SUBSTRING
(CalendarDays,t.N,1) in ('.','H').
Are Holidays supposed to be counted as part of the 100 days? If not, then no code changes are required because I'm only counting the days represented with a "." which is supposed to be a workday. If Holidays are supposed to be treated as a workday, then the changes you suggest above are spot on.
--Jeff Moden
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply