March 26, 2015 at 10:13 am
I am having a problem getting started on this problem, and I'm hoping someone has solved something similar and can guide me.
I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.
SET NOCOUNT ON;
declare @EmployeeStatus TABLE (
[EmployeeID] [int] NOT NULL,
[EmployeeStatus] [varchar](24) NOT NULL,
[EffectiveDate] [date] NOT NULL
)
INSERT INTO @EmployeeStatus VALUES (11, 'full time', '2014-03-01');
INSERT INTO @EmployeeStatus VALUES (11, 'part time', '2014-07-21');
INSERT INTO @EmployeeStatus VALUES (11, 'full time', '2014-09-15');
INSERT INTO @EmployeeStatus VALUES (11, 'termed', '2014-11-22');
INSERT INTO @EmployeeStatus VALUES (22, 'full time', '2013-03-06');
INSERT INTO @EmployeeStatus VALUES (22, 'part time', '2014-09-21');
INSERT INTO @EmployeeStatus VALUES (22, 'full time', '2014-12-19');
INSERT INTO @EmployeeStatus VALUES (33, 'full time', '2013-06-18');
INSERT INTO @EmployeeStatus VALUES (44, 'full time', '2004-01-01');
INSERT INTO @EmployeeStatus VALUES (44, 'part time', '2014-08-21');
INSERT INTO @EmployeeStatus VALUES (44, 'full time', '2014-11-15');
DECLARE @PayrollSchedule TABLE (
[PayrollYear] [smallint] NOT NULL,
[PayPeriod] [smallint] NOT NULL,
[PayPeriodStart] [date] NOT NULL,
[PayPeriodEnd] [date] NOT NULL
)
DECLARE @PayrollYear smallint = 2014;
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 1, '2014-01-01', '2014-01-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 2, '2014-01-16', '2014-01-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 3, '2014-02-01', '2014-02-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 4, '2014-02-16', '2014-02-28');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 5, '2014-03-01', '2014-03-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 6, '2014-03-16', '2014-03-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 7, '2014-04-01', '2014-04-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 8, '2014-04-16', '2014-04-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 9, '2014-05-01', '2014-05-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 10, '2014-05-16', '2014-05-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 11, '2014-06-01', '2014-06-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 12, '2014-06-16', '2014-06-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 13, '2014-07-01', '2014-07-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 14, '2014-07-16', '2014-07-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 15, '2014-08-01', '2014-08-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 16, '2014-08-16', '2014-08-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 17, '2014-09-01', '2014-09-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 18, '2014-09-16', '2014-09-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 19, '2014-10-01', '2014-10-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 20, '2014-10-16', '2014-10-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 21, '2014-11-01', '2014-11-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 22, '2014-11-16', '2014-11-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 23, '2014-12-01', '2014-12-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 24, '2014-12-16', '2014-12-31');
SELECT * FROM @PayrollSchedule
SELECT * FROM @EmployeeStatus
I am unsure where to begin, however I'm sure a tally table would help. Any ideas?
Edited to correct dates in DDL for pay period 3 & 4.
March 26, 2015 at 8:23 pm
Maybe I could start you off with a function to return the date that their status became active (at a given date) CREATE FUNCTION dbo.fn_maxEffDate (@periodDate DATE)
RETURNS TABLE
AS
RETURN
SELECT max(effectivedate) as edate,EmployeeID as eID
FROM EmployeeStatus
WHERE effectivedate < @periodDate
GROUP BY EmployeeID;
You could pass in the start or end date of the pay period and retrieve the status from EmployeeStatus. I used tables rather than table variables.
March 26, 2015 at 9:11 pm
Dave-3000 (3/26/2015)
I am having a problem getting started on this problem, and I'm hoping someone has solved something similar and can guide me.I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.
I have some questions, please.
1. What do you want to show on pay periods that are before the first entry for a given employee. For example, if an employee was hired on 05 May 2014 and your report is showing pay periods from 01 April 2014 thru 30 Jun 2014, what should the output be for that employee?
2. What do you want to show on pay periods that are after an employee has been terminated. For example, if a full time employee was terminated on 05 May 2014 and your report is showing pay periods from 01 April 2014 thru 30 Jun 2014, what should the output be for that employee?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2015 at 9:39 am
Jeff Moden (3/26/2015)
I have some questions, please.
1. What do you want to show on pay periods that are before the first entry for a given employee. For example, if an employee was hired on 05 May 2014 and your report is showing pay periods from 01 April 2014 thru 30 Jun 2014, what should the output be for that employee?
2. What do you want to show on pay periods that are after an employee has been terminated. For example, if a full time employee was terminated on 05 May 2014 and your report is showing pay periods from 01 April 2014 thru 30 Jun 2014, what should the output be for that employee?
I am rebuilding a legacy Access 2007 database into a web app and SQL Server 2012. This is an app that calculates 401k eligibility and profit sharing, and the business people are reluctantly doling out their business rules to me as if they were krugerrands. So far all I know is that at random places within the app they need to show the status on those dates. This data gets loaded from another system every two weeks, so I was planning to load a table (~1300 employees times 24 pay periods) and refer to it later. Hopefully, at some point I will become worthy of knowing the rest of the business rules. :rolleyes:
To answer your first question, I would need to return a NULL or some other indicator that says they weren't employed on that date.
For employees who have been terminated, they might still be eligible for profit sharing later in that calendar year, so just the most recent status (terminated) should show for that employee.
Thanks very much for your help on this!
March 27, 2015 at 11:12 am
I am still working on a solution to this, (I thought I had something but just saw your reply to Jeff)...
In the meantime I came up with a better way to populate your payroll schedule table...
First the function:
IF OBJECT_ID('tempdb.dbo.GetDupNums') IS NOT NULL DROP FUNCTION dbo.GetDupNums;
GO
CREATE FUNCTION dbo.GetDupNums(@Dups tinyint, @MaxN int, @ZeroOrOne bit)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/*
Purpose:
Return a column of ints from 1 through @MaxN;
The columns will be duplicated (@dups) number of times (e.g. @dups = 2, you get two 1's, two 2's, etc...);
It also includes a psuedo ROW_NUMBER() column starting at @ZeroOrOne
Usage:
-- Syntax example (Returns INT)
SELECT RowNum, N
FROM dbo.GetDupNums(@Dups, @MaxN, @ZeroOrOne)
Notes:
0. This function is based on Itzek Ben-Gan's getnumbers function and Jeff Moden's fnTally() function
1. This is my BETA version of the function (still testing & updating)
2. Will work with SQL Server 2005+
3. The function also returns a PSEUDO-ROW_NUMBER() column (why not?)
4. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
5. A negetive number will break it (as it will has an improper value to be passed to the top clause)
5a. This can be fixed by replacing the TOP clause by use ROWS instead
(This would, however, make the function SQL Server 2012+ compatible)
5b. Alternatively you could wrap and ABS around the TOP clause then include a "WHERE N < @Dups*@maxN" clause
Either fix would return 0 rows instead of throwing an error
6. Made dups tinyint to reduce enforce a non-negetive int (and 255 should be enough)
7. DONT Sort by either column unless you must! This will decrease performance
- I'm working on this... just put this function together, still considering the best way to deal with that
- It DOES return the rows in order (but as we all know, an ORDER BY is required for a sort order is to be guaranteed).
Revision History:
Rev 00 - 3/27/2015 - Alan Burstein
*/
WITH
L1(N) AS
( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --8
L3(N) AS (SELECT 1 FROM L1 a, L1 b, L1 c), -- 512
TallyOh(N) AS
( SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM L3 a, L3 b, L3 c ) -- 134,217,728 (+1 for oh)
SELECT TOP (@Dups*@maxN)
RowNum = N+ISNULL(@ZeroOrOne,0),
N = (N/@Dups)+1
FROM TallyOh;
GO
And how you would use it to create the pay periods table (note: with a function like this it is easy to create multiple years and do so in nasty fast fashion.
DECLARE @payrollyear smallint = 2014;
DECLARE @Dups int = 2, @MaxN int = 12;
SELECT
PayrollYear = @payrollyear,
ParyPeriod = RowNum,
PayPeriodStart =
CASE RowNum%2
WHEN 1 THEN dateadd(month,n-1,concat('1/1/',@payrollyear))
ELSE dateadd(month,n-1,concat('1/16/',@payrollyear))
END,
PayPeriodEnd =
CASE RowNum%2
WHEN 1 THEN dateadd(month,n-1,concat('1/15/',@payrollyear))
ELSE dateadd(day,-1,dateadd(month,n,concat('1/1/',@payrollyear)))
END
FROM dbo.GetDupNums(@Dups, @MaxN, 1);
It's worth noting too (for anyone else you is working on this) that this query:
SELECT *
FROM @PayrollSchedule ps
LEFT JOIN @EmployeeStatus es
ON es.EffectiveDate >= ps.PayPeriodStart
AND es.EffectiveDate <= ps.PayPeriodEnd;
gets you pretty close. For example it lets us know that employee 11 was full time on 3-1 through 3-15 and part time beginning 7-16 through 7-31.
Again, still pluggin away...
Edit: Comment Typo...
-- Itzik Ben-Gan 2001
March 29, 2015 at 8:43 pm
You can try this. Had to make some assumptions around Jeff's excellent questions.
declare @EmployeeStatus TABLE (
[EmployeeID] [int] NOT NULL,
[EmployeeStatus] [varchar](24) NOT NULL,
[EffectiveDate] [date] NOT NULL
)
INSERT INTO @EmployeeStatus VALUES (11, 'full time', '2014-03-01');
INSERT INTO @EmployeeStatus VALUES (11, 'part time', '2014-07-21');
INSERT INTO @EmployeeStatus VALUES (11, 'full time', '2014-09-15');
INSERT INTO @EmployeeStatus VALUES (11, 'termed', '2014-11-22');
INSERT INTO @EmployeeStatus VALUES (22, 'full time', '2013-03-06');
INSERT INTO @EmployeeStatus VALUES (22, 'part time', '2014-09-21');
INSERT INTO @EmployeeStatus VALUES (22, 'full time', '2014-12-19');
INSERT INTO @EmployeeStatus VALUES (33, 'full time', '2013-06-18');
INSERT INTO @EmployeeStatus VALUES (44, 'full time', '2004-01-01');
INSERT INTO @EmployeeStatus VALUES (44, 'part time', '2014-08-21');
INSERT INTO @EmployeeStatus VALUES (44, 'full time', '2014-11-15');
INSERT INTO @EmployeeStatus VALUES (55, 'full time', '2014-05-12');
DECLARE @PayrollSchedule TABLE (
[PayrollYear] [smallint] NOT NULL,
[PayPeriod] [smallint] NOT NULL,
[PayPeriodStart] [date] NOT NULL,
[PayPeriodEnd] [date] NOT NULL
)
DECLARE @PayrollYear smallint = 2014;
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 1, '2014-01-01', '2014-01-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 2, '2014-01-16', '2014-01-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 3, '2014-02-01', '2014-02-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 4, '2014-02-16', '2014-02-28');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 5, '2014-03-01', '2014-03-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 6, '2014-03-16', '2014-03-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 7, '2014-04-01', '2014-04-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 8, '2014-04-16', '2014-04-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 9, '2014-05-01', '2014-05-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 10, '2014-05-16', '2014-05-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 11, '2014-06-01', '2014-06-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 12, '2014-06-16', '2014-06-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 13, '2014-07-01', '2014-07-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 14, '2014-07-16', '2014-07-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 15, '2014-08-01', '2014-08-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 16, '2014-08-16', '2014-08-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 17, '2014-09-01', '2014-09-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 18, '2014-09-16', '2014-09-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 19, '2014-10-01', '2014-10-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 20, '2014-10-16', '2014-10-31');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 21, '2014-11-01', '2014-11-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 22, '2014-11-16', '2014-11-30');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 23, '2014-12-01', '2014-12-15');
INSERT INTO @PayrollSchedule VALUES (@PayrollYear, 24, '2014-12-16', '2014-12-31');
--SELECT * FROM @PayrollSchedule
--SELECT * FROM @EmployeeStatus;
WITH Employees AS
(
SELECT EmployeeID, MinDate=MIN(EffectiveDate)
FROM @EmployeeStatus
GROUP BY EmployeeID
)
SELECT EmployeeID, PayPeriod, PayPeriodStart
,StatusAtPayPeriodStart=d.EmployeeStatus
,EffectiveDateAtPayPeriodStart=d.EffectiveDate
,PayPeriodEnd
,StatusAtPayPeriodEnd=c.EmployeeStatus
,EffectiveDateAtPayPeriodEnd=c.EffectiveDate
FROM @PayrollSchedule a
CROSS APPLY
(
SELECT EmployeeID
FROM Employees b
WHERE MinDate <= PayPeriodEnd
) b
OUTER APPLY
(
-- Status at pay period end
SELECT TOP 1 EffectiveDate, EmployeeStatus
FROM @EmployeeStatus c
WHERE b.EmployeeID = c.EmployeeID AND c.EffectiveDate <= PayPeriodEnd
ORDER BY c.EffectiveDate DESC
) c
OUTER APPLY
(
-- Status at pay period start
SELECT TOP 1 EffectiveDate, EmployeeStatus
FROM @EmployeeStatus d
WHERE b.EmployeeID = d.EmployeeID AND d.EffectiveDate <= PayPeriodStart
ORDER BY d.EffectiveDate DESC
) d
WHERE ISNULL(d.EmployeeStatus, '') <> 'termed'
ORDER BY EmployeeID
I also added EmployeeID=55 to test one case you didn't include.
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
April 7, 2015 at 9:54 am
Thanks very much, Dwain. I really appreciate your help with this.
April 8, 2015 at 2:26 pm
CELKO (4/8/2015)
What math do you on the employee identifier? None! It should never be a numeric;
Absolute rubbish and you know it, Joe. What would you use instead?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2015 at 8:29 pm
CELKO (4/8/2015)
Absolute rubbish and you know it, Joe. What would you use instead?
Strings, with validation and verification. The validation is usually done with regular expressions and/or check digits. We would prefer a fixed length string.
Grab any packaged item sitting near your computer and look at the UPC barcode. Is it a negative number? A positive number? Irrational? Complex? No! It does not model a magnitude or quantity, so it is not a number of any kind by definition.
Based on an informal research project of my own, the typical ANSI, ISO, DIN, etc industry standard identifier is 8 to 16 digits/characters, with most of them around 9-10 positions. The Luhn is the most common check digit (this is not the best choice, but the easiest to put into hardware). Hardcopy printouts are also the reason for preferring fixed length encoding, too.
Good God no! Not for an Employee Identifier. A 4 byte positive integer is just fine instead of an 8 to 16 byte string especially when you consider how many times that identifier might be used in other tables and indexes. An Employee ID is no different than an all digit SSN except there is no need for leading zero's and certainly no need for the denormalization that they original built into SSNs.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2015 at 12:27 pm
I concur with Jeff.
[p]Some of that was just simplifying the problem for the post, and some was due to the legacy tables and code thrust upon us. This app sits downstream from a massive accounting system; I don't have much control with how the mothership serves me data.
Plus, if an 8-digit employee ID fits into a 4-byte int, and the IT shop charges us $22,000 per terabyte per year for SAN space (with no other options :crying:), it doesn't make $$$ sense to use the extra bytes to get a textbook solution.
[/p]
[p]The rest of us have to live in the real world, and not just on these forums.[/p]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply