June 22, 2012 at 2:48 pm
Greetings,
I have a table that represents the calendar of days that employees work during the year. Below is sample data for three rows of that table.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myCalendars','U') IS NOT NULL
DROP TABLE #myCalendars
--===== Create the test table with
CREATE TABLE #myCalendars
(
CalendarID char(4),
CalendarYear int,
CalendarDays char(366)
)
--===== Insert the test data into the test table
INSERT INTO #myCalendars VALUES ('A209',2011,'UU.....UU.....UUU....UU.....UU.....UU.....UUU....UUU....UU. ....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU.UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU.UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU...U.UU.....UU...UUUU.....UU.....UU.....UUUUUUUUUUUUUU')
INSERT INTO #myCalendars VALUES ('A209',2012,'UU....UU.....UUU....UU.....UU.....UU.....UUU....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU..UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU.UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU....UUU.....UU...UUUU.....UU.....UU.....UUUUUUUUUUUUUUU')
INSERT INTO #myCalendars VALUES ('A209',2013,'U...UU.....UU.....UUU....UU.....UU.....UUU....UUU....UU.... .UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU..UUUUUU..UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU....UU.....UU.....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU...UUUU.....UU.....UU.....UU.UUUUUUUUUUUUUU')
Unpaid days in the CalendarDays field are represented with 'U'. Paid days are represented with a period (' .'). Some calendars include other values for holidays.
The first value in CalendarDays represents Jan 1 and the last value represents Dec 31. The 60th value is always Feb 29 which on some calendars would be blank if that year is not a leap-year.
I will be joining this table with an employee table that identifies the employee's Hire Date and CalendarID. I need to be able to identify the date that the employee should be evaluated, which is 100 paid days after the employee's Hire Date. For example, an employee hired on Feb 3 2012 would be evaluated 100 paid days later which is June 22 2012.
I would appreciate any suggestions on how I should find this future date. Thank you.
gmrose
June 22, 2012 at 3:12 pm
I think that part of the solution would involve converting the Hire Date into a Julian value. Is there any function in SSMS that will convert a date to a Julian value?
June 22, 2012 at 3:29 pm
gmrose (6/22/2012)
I think that part of the solution would involve converting the Hire Date into a Julian value. Is there any function in SSMS that will convert a date to a Julian value?
Something like this?
SELECT DATEPART(dy,GETDATE()), DATEPART(dy,'20120101');
Only problem would be non-leap years. After February 28th you have to add 1 to match your layout.
June 22, 2012 at 3:32 pm
You can disregard my comment about the Julian date. I believe instead that we can find the Hire Date in the CalendarDays field after calculating the DateDiff in days since the end of the previous year. For example, to find which position in CalendarDays represents Feb 3, 2012, I did this simple select to find that its the 34th position.
SELECT DATEDIFF(dd, '12/31/2011', '02/03/2012')
June 22, 2012 at 3:34 pm
ok i'm not sure how your calendar table is constructed.
my calendar table has a row for each date,a s well as a flag for IsWorkDay or not.
with my version, the command, which just uses rownumber(), makes it easy:
SELECT * FROM
(
SELECT ROW_NUMBER()
over(order by TheDate) As RW,* from TallyCalendar
Where IsWorkDay = 1
--Lowell's imaginary Hire Date
And TheDate > '2008-12-11'
)myAlias
--combined with the WHERE statement, 100 work days later:
WHERE RW = 100
here is a link to the massive script I wrote for my TallyCalendar table:
http://www.stormrage.com/SQLStuff/TallyCalendar_Complete_With_DST.txt
it creates a table, from SQL begin date of 1900-01-01 to 100 years int he future.
the final table looks like this,and is populated with al the US holidays and a LOT of other handy information(need to know the next Friday the 13th that is also a full moon?)
CREATE TABLE [dbo].[TALLYCALENDAR] (
[THEDATE] DATETIME NOT NULL,
[DAYOFWEEK] VARCHAR(50) NULL,
[ISHOLIDAY] BIT NULL DEFAULT ((0)),
[ISWORKHOLIDAY] BIT NULL DEFAULT ((0)),
[ISWEEKDAY] BIT NULL DEFAULT ((0)),
[ISWEEKEND] BIT NULL DEFAULT ((0)),
[ISDAYLIGHTSAVINGS] BIT NULL DEFAULT ((0)),
[HOLIDAYNAME] VARCHAR(100) NULL,
[LUNARPHASE] VARCHAR(50) NULL,
[ISOWEEK] INT NULL,
[ISWORKDAY] AS (case when [IsWorkHoliday]=(1) OR ([DayOfWeek]='Sunday' OR [DayOfWeek]='Saturday') then (0) else (1) end) PERSISTED,
[JULIANDAY] AS (datediff(day,dateadd(year,datediff(year,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,
[YEARNUMBER] AS (datepart(year,[Thedate])) PERSISTED,
[MONTHNUMBER] AS (datediff(month,dateadd(year,datediff(year,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,
[DAYNUMBER] AS (datediff(day,dateadd(month,datediff(month,(0),[TheDate]),(0)),[TheDate])+(1)) PERSISTED,
CONSTRAINT [PK__TallyCal__5CB7C64E173876EA] PRIMARY KEY CLUSTERED (TheDate))
GO
CREATE INDEX [IX_TallyCalendar] ON [TallyCalendar] (DayOfWeek, TheDate) INCLUDE (IsHoliday, IsWorkHoliday, HolidayName)
Lowell
June 22, 2012 at 3:46 pm
I don't think that your sample values are correct. June 22, 2012 is 102 days after Feb 3, 2012. If you only want to count paid days, are you saying that this employee only had two unpaid days off in that whole time? Don't you give your employees weekends?
Your schema is a mess. You're having problems with your query, because your data is not normalized. You're also going to run into problems if your 100 days rolls over into the next year.
You'll probably need a calendar table. Someone had a very comprehensive calendar table script, which I'll try looking for. Given a date, you can use this formula to find the index for any particular day within a leap year and will correspond to the index of your string. You'll need to replace the GETDATE() with the appropriate field.
SELECT DATEPART(dy, DATEADD(YEAR, -DATEDIFF(YEAR, '2000-01-01', GETDATE()), GETDATE()))
I would probably add this as a computed column in your calendar table.
Once you have your calendar table set up, you can join to your field based something like the following.
SUBSTRING(CalendarDays, DayIndex, 1) <> 'U'
NOTE: the SUBSTRING makes this non-SARGable, so performance will be relatively bad (which is another reason to normalize your data).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 22, 2012 at 3:52 pm
Lowell posted his script, so I don't need to find a link. I think his calculation for Julian Day can be simplified to DATEPART(dy, YourDateField), but you need an Adjusted Julian Day, which my previous script calculates.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 22, 2012 at 4:36 pm
drew.allen said
I don't think that your sample values are correct. June 22, 2012 is 102 days after Feb 3, 2012. If you only want to count paid days, are you saying that this employee only had two unpaid days off in that whole time? Don't you give your employees weekends?
Your schema is a mess. You're having problems with your query, because your data is not normalized. You're also going to run into problems if your 100 days rolls over into the next year.
You'll probably need a calendar table. Someone had a very comprehensive calendar table script, which I'll try looking for. Given a date, you can use this formula to find the index for any particular day within a leap year and will correspond to the index of your string. You'll need to replace the GETDATE() with the appropriate field.
SELECT DATEPART(dy, DATEADD(YEAR, -DATEDIFF(YEAR, '2000-01-01', GETDATE()), GETDATE()))
I would probably add this as a computed column in your calendar table.
Once you have your calendar table set up, you can join to your field based something like the following.
SUBSTRING(CalendarDays, DayIndex, 1) <> 'U'
From my original post, I identified the paid days as those with value '.' and the unpaid days as those with value 'U'. One can easily see that all weekends appear as pairs of 'U' values and that there are plenty of other unpaid days. The union contracts at our agency identify the paid days and the unpaid days which certainly are not the same as his agency's.
The calendar table used at our agency will not be changed. I will set up CTEs to accomplish this task.
I do not understand his statement SUBSTRING(CalendarDays, DayIndex, 1) <> 'U'.
If anyone know a syntax from parsing a column similar to my CalendarDays column, I would appreciate being shown. Thank you.
gmrose
June 22, 2012 at 4:51 pm
I'm sure someone here could come up with a function that would parse your calendar table, but you'd be far better off creating a new calendar table that would exist in parallel with your current table. The scripts provided above are excellent and will help you to build a table that can use all the native date functionality supported by SQL Server. I can't think of a good reason to build a calendar table the way you've shown. I have a suspicion that this table is just a persisted array from some application. If so, you should find the programmer and beat them senseless for saddling you with such a monstrosity. (OK, violence isn't the way to go, but if they're forcing you to work with that thing, they owe you big time).
June 23, 2012 at 1:37 pm
I understand that you can't change the Calendar Table from what you have but I have to
join in the din of the others to say that the form of Calendar Table you have to work with
is absolutely one of the worst because it's difficult to use for just about anything where a
Calendar Table would be used.
Now that I have that off my chest, let's solve this problem in a "reusable" and as
"maintenance free" manner as possible.
First, you're going to need a Tally Table for this. We could use a Ben-Gan style of cascading
CTE to build a "cteTally" for this, but I wanted to keep the code as simple as possible.
Here's the code to build the same kind of Tally Table I use in production. Obviously, you should
do this in your real DB but I'm doing it in TempDB just to be on the safe side.
--===== Do this in a nice safe place that everyone has
USE TempDB;
IF OBJECT_ID('dbo.Tally','U') IS NOT NULL
DROP TABLE Tally;
GO
--===================================================================
-- Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
For more information on how a Tally Table can be used to replace certain loops, please
see the following article.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Now, let's create a mockup of your "Calendar Table". I'm assuming that you have a real
table that looks like this and, since a function can't use a Temp Table, I'm building a real
table for this demonstration. Since I'm also dropping the table to make reruns in SSMS
easier, I'm doing this all in TempDB so we don't take a chance on dropping any of your real
tables. We'll do the same with a mockup of your employee table.
--===== Since we are dropping real tables for this experiment,
-- do it in a nice safe place that everyone has.
USE tempdb
;
--===== If the test table already exists, drop it
IF OBJECT_ID('dbo.myCalendars','U') IS NOT NULL
DROP TABLE dbo.myCalendars
--===== Create the test table
CREATE TABLE dbo.myCalendars
(
CalendarID CHAR(4),
CalendarYear INT,
CalendarDays CHAR(366)
)
--===== Insert the test data into the test table
INSERT INTO dbo.myCalendars VALUES ('A209',2011,'UU.....UU.....UUU....UU.....UU.....UU.....UUU....UUU....UU. ....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU.UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU.UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU...U.UU.....UU...UUUU.....UU.....UU.....UUUUUUUUUUUUUU')
INSERT INTO dbo.myCalendars VALUES ('A209',2012,'UU....UU.....UUU....UU.....UU.....UU.....UUU....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU..UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU.UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU....UUU.....UU...UUUU.....UU.....UU.....UUUUUUUUUUUUUUU')
INSERT INTO dbo.myCalendars VALUES ('A209',2013,'U...UU.....UU.....UUU....UU.....UU.....UUU....UUU....UU.... .UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU.....UU.....UU.....UU..UUUUUU..UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU....UU.....UU.....UUU....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UU.....UUU....UU...UUUU.....UU.....UU.....UU.UUUUUUUUUUUUUU')
GO
--===== If the test table already exists, drop it
IF OBJECT_ID('dbo.Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
--===== Create the test table
CREATE TABLE dbo.Employee
(
EmployeeID INT,
StartDate DATETIME
)
;
INSERT INTO dbo.Employee
(EmployeeID, StartDate)
SELECT 1, '20111215' UNION ALL
SELECT 2, '20110113' UNION ALL
SELECT 3, '20120113' UNION ALL
SELECT 4, '20120114' --Not a workday so not possible. Evaluation date will be NULL.
;
GO
Next, we need a programmable function that will allow you to select which "CalendarID"
you want to use and how many days offset you want to use. Again, I'm conditionally
dropping the function to make reruns in SSMS easier so I'm doing this in TempDB to protect
any real code. As always, details for the code are in the comments in the code.
--===== 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.GetOffSetDates','IF') IS NOT NULL
DROP FUNCTION dbo.GetOffSetDates
GO
CREATE FUNCTION dbo.GetOffSetDates
(@pCalendarID CHAR(4), @pOffSetDays INT)
RETURNS TABLE
AS
RETURN
WITH
cteEnumerate AS
( --=== Split each "day" out with a sort value and the minimum year for the
-- particular calendar we're using according to @pCalendarID.
-- It also gets rid of the blank place holders for missing February 29ths.
SELECT SortOrder = ROW_NUMBER() OVER (ORDER BY c.CalendarYear,t.N)-1,
DayType = SUBSTRING(c.CalendarDays,t.N,1),
d.MinYear
FROM dbo.myCalendars c
CROSS JOIN dbo.Tally t
CROSS JOIN ( --=== This finds the minimum year for this particular CalendarID
SELECT CAST(MIN(CalendarYear) AS CHAR(4))
FROM dbo.MyCalendars
WHERE CalendarID = @pCalendarID
) d (MinYear)
WHERE c.CalendarID = @pCalendarID
AND t.N BETWEEN 1 AND LEN(c.CalendarDays)
AND SUBSTRING(c.CalendarDays,t.N,1) > ' '
),
cteCreateWorkDayDates AS
( --=== Remove everything that isn't a workday, create the date
-- from the SortOrder, and then number the dates.
SELECT DayNumber = ROW_NUMBER() OVER (ORDER BY SortOrder),
WorkDayDate = DATEADD(dd,SortOrder,MinYear)
FROM cteEnumerate
WHERE DayType = '.'
) --=== Create the future dates according to the @pOffSetDays
SELECT wd1.WorkDayDate,
OffSetDate = wd2.WorkDayDate
FROM cteCreateWorkDayDates wd1
LEFT JOIN cteCreateWorkDayDates wd2
ON wd1.DayNumber + @pOffSetDays = wd2.DayNumber
;
GO
The rest is child's play...
--===== Find the evaluation date for all employees in the Employee table
-- according to their start date. In this case, the evaluation date
-- is 100 work days after (not including) the start date.
-- Note that the StartDate MUST be a "whole date" (Midnight time).
-- In 2008 and above, it could be a DATE datatype.
SELECT e.EmployeeID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.GetOffSetDates('A209',100) offset
ON offset.WorkDayDate = e.StartDate
ORDER BY EvaluationDate
;
Here are the results of that little bit of computational heaven...
EmployeeID StartDate EvaluationDate
----------- ----------------------- -----------------------
4 2012-01-14 00:00:00.000 NULL
2 2011-01-13 00:00:00.000 2011-06-08 00:00:00.000
1 2011-12-15 00:00:00.000 2012-05-22 00:00:00.000
3 2012-01-13 00:00:00.000 2012-06-07 00:00:00.000
(4 row(s) affected)
Again, I implore you to try to get the company to change to a more reasonable form of
"Calendar Table". It will keep you and many others from having to jump through the same
hoops that we just did to solve a really simple problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2012 at 1:47 pm
David Webb-200187 (6/22/2012)
If so, you should find the programmer and beat them senseless for saddling you with such a monstrosity..
Heh... how can you beat someone senseless when they apparently had no sense to begin with? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2012 at 4:34 pm
To Jeff Moden -
Thank you for your help. I have used you Tally Table method on several issues that I have had and it always works very well. For the issue with the Calendar, I have gotten it to work fairly well and I hope you will be able to give me a suggestion that would improve it. When I had provided sample data for the #myCalendar table, I had only included 3 rows for different years for just one CalendarID. My table actually has about ten years for about a dozen different CalendarIDs. The Employee table also includes a value for the employee's CalendarID which is based on the different jobs they have. Some employee jobs have different unpaid days in the summer and so they have different calendars.
You had given me this statement below to select the EvaluationDate for employees.
SELECT e.EmployeeID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.GetOffSetDates('A209',100) offset
ON offset.WorkDayDate = e.StartDate
ORDER BY EvaluationDate
I think that I now need to call the GetOffSetDates function with diffenent values in place of the 'A209' constant for different employees. It might be something better than the following:
SELECT e.EmployeeID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.GetOffSetDates(e.CalendarID,100) offset
ON offset.WorkDayDate = e.StartDate
ORDER BY EvaluationDate
I believe that I cannot use a column name as a parameter in a function. What is the correct syntax to use the function correctly?
Thank you for all your help.
gmrose
June 25, 2012 at 8:16 pm
gmrose (6/25/2012)
To Jeff Moden -Thank you for your help. I have used you Tally Table method on several issues that I have had and it always works very well. For the issue with the Calendar, I have gotten it to work fairly well and I hope you will be able to give me a suggestion that would improve it. When I had provided sample data for the #myCalendar table, I had only included 3 rows for different years for just one CalendarID. My table actually has about ten years for about a dozen different CalendarIDs. The Employee table also includes a value for the employee's CalendarID which is based on the different jobs they have. Some employee jobs have different unpaid days in the summer and so they have different calendars.
You had given me this statement below to select the EvaluationDate for employees.
SELECT e.EmployeeID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.GetOffSetDates('A209',100) offset
ON offset.WorkDayDate = e.StartDate
ORDER BY EvaluationDate
I think that I now need to call the GetOffSetDates function with diffenent values in place of the 'A209' constant for different employees. It might be something better than the following:
SELECT e.EmployeeID, e.StartDate, EvaluationDate = offset.OffSetDate
FROM dbo.Employee e
LEFT JOIN dbo.GetOffSetDates(e.CalendarID,100) offset
ON offset.WorkDayDate = e.StartDate
ORDER BY EvaluationDate
I believe that I cannot use a column name as a parameter in a function. What is the correct syntax to use the function correctly?
Thank you for all your help.
gmrose
Is there a "CalendarID" or some other way to associate an EmployeeID with the CalendarID that the
employee will be subject to? If so, please post that information and I'll show you how to do this. If
not, then we're pretty much stuck.
Here's a reminder of the Employee table I used which has no CalendarID in it. It either needs to have
one or some way of associating the employees with their respective calendars.
--===== Create the test table
CREATE TABLE dbo.Employee
(
EmployeeID INT,
StartDate DATETIME
)
;
INSERT INTO dbo.Employee
(EmployeeID, StartDate)
SELECT 1, '20111215' UNION ALL
SELECT 2, '20110113' UNION ALL
SELECT 3, '20120113' UNION ALL
SELECT 4, '20120114' --Not a workday so not possible. Evaluation date will be NULL.
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2012 at 9:16 am
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
June 26, 2012 at 9:18 am
Correction:
--===== Create the test table
CREATE TABLE dbo.Employee
(
EmployeeID INT,
StartDate DATETIME,
CalendarID CHAR(4)
)
;
INSERT INTO dbo.Employee
(EmployeeID, StartDate, CalendarID)
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply