T-SQL brain teaser, tricky sum for employee data.
-
Hello All,
I have a question... Given the following:
--------------------------------------------------------
--Scripts to create sample tables and data, assuming you
--allready have a Test Database to put them into.
--------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PS_JOB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PS_JOB]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CHECK_DATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CHECK_DATA]
GO
CREATE TABLE [dbo].[PS_JOB] (
[EMPLID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EFFDT] [datetime] NULL ,
[REG_TEMP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CHECK_DATA] (
[EMPLID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYEND_DT] [datetime] NULL ,
[AMOUNT] [money] NULL
) ON [PRIMARY]
GO
------------------------------------------
--Insert sampla data into the above tables
------------------------------------------
--Insert to PS_JOB
------------------
--"T" = Temporary Employees and "R" = FullTime Employees
--------------------------------------------------------
INSERT INTO PS_JOB (EMPLID, EFFDT, REG_TEMP)
VALUES ('000001', CONVERT(DATETIME, '2004-01-01 00:00:00', 102), 'T')
INSERT INTO PS_JOB (EMPLID, EFFDT, REG_TEMP)
VALUES ('000001', CONVERT(DATETIME, '2004-04-01 00:00:00', 102), 'R')
INSERT INTO PS_JOB (EMPLID, EFFDT, REG_TEMP)
VALUES ('000002', CONVERT(DATETIME, '2004-01-01 00:00:00', 102), 'T')
INSERT INTO PS_JOB (EMPLID, EFFDT, REG_TEMP)
VALUES ('000003', CONVERT(DATETIME, '2004-01-01 00:00:00', 102), 'T')
------------------------
--Insert into CHECK_DATA
------------------------
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000001', CONVERT(DATETIME, '2004-01-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000001', CONVERT(DATETIME, '2004-02-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000001', CONVERT(DATETIME, '2004-03-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000001', CONVERT(DATETIME, '2004-04-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000001', CONVERT(DATETIME, '2004-05-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000002', CONVERT(DATETIME, '2004-01-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000002', CONVERT(DATETIME, '2004-02-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000002', CONVERT(DATETIME, '2004-03-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000002', CONVERT(DATETIME, '2004-04-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000002', CONVERT(DATETIME, '2004-05-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000003', CONVERT(DATETIME, '2004-01-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000003', CONVERT(DATETIME, '2004-02-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000003', CONVERT(DATETIME, '2004-03-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000003', CONVERT(DATETIME, '2004-04-15 00:00:00', 102), 10)
INSERT INTO CHECK_DATA (EMPLID, PAYEND_DT, AMOUNT)
VALUES ('000003', CONVERT(DATETIME, '2004-05-15 00:00:00', 102), 10)
---------------------------------------------------------------------
--The above data is baased on ADP's payroll and HR tables.
--The question I have is kind of a T-SQL "brain teaser".
--Given the above table structure and dataset:
--What is the total amount allocated to temporary employees?
--Note that EMPLID 000001 started out as Temporary and became
--FulTime on 4/1/2004, as a reault of this move only check data
--from the temporary period should be allocated as Temporary expenses.
--What is the T-SQL that will group and total employees Amount
--based on the PS_JOB.REG_TEMP value = "T"?
---------------------------------------------------------------------
-Isaiah -
Just to let the readers know, this is not just an exercise for me. I need to be able to answer these kinds of questions. I can't alter the database (its propitarery) and the structure for many tables is similar, using an "effective date" for changes to various employee statuses. I can't seem to get the employees who have changed status (from T to R) to calculate correctly.
Thanks!
-Isaiah -
Here is one solution...
I do have one extra join to over state my thought process
--Join pass for attributes and amount
select sum(CD.amount)as amount from check_data CD,
--Choose employees and dates for query
(select SUB1.emplid,SUB1.payend_dt from
--Temp pay
(select a.emplid,a.payend_dt from check_data a, ps_job b where a.emplid=b.emplid and b.reg_temp='T' and b.effdt<=a.payend_dt)SUB1
LEFT OUTER JOIN
--Regular pay
(select a.emplid,a.payend_dt from check_data a, ps_job b where a.emplid=b.emplid and b.reg_temp='R' and b.effdt<=a.payend_dt)SUB2
--Join properties excluding pay dates for full-time
ON SUB1.emplid=SUB2.emplid and SUB1.payend_dt=SUB2.payend_dt
WHERE SUB2.emplid is null and SUB2.payend_dt IS null)SUB3
where SUB3.emplid=CD.emplid and SUB3.payend_dt=CD.payend_dt
-
One tricky issue is going to be this: employee 1 changed from T to R in the middle of a pay period, so I suppose you are going to want to pro rate the amount 10 paid for March 16 to April 15 between the T time of March 16 to March 31 and the R time of April 1 to April 15.
Yes no maybe?
Given this, I think you're going to have to look at a per day solution of some kind; I don't think it's going to be pretty.
-
maybe this is a good starter
select Subsel.EMPLID, Subsel.EFFDT, sum( C.AMOUNT ) as TotAmt
from (select J1.EMPLID, J1.EFFDT
, isnull((select dateadd(ms,-3,min(J2.EFFDT) ) from PS_JOB J2 where J1.EMPLID = J2.EMPLID and J2.EFFDT > J1.EFFDT ), '9999-12-31' ) as EFFDT_END
, J1.REG_TEMP
from PS_JOB J1 ) Subsel -- determines dateranges per employee
inner join CHECK_DATA C
on C.EMPLID = Subsel.EMPLID
and Subsel.REG_TEMP = 'T'
and C.PAYEND_DT between Subsel.EFFDT and Subsel.EFFDT_END
group by Subsel.EMPLID, Subsel.EFFDT
order by Subsel.EFFDT, Subsel.EMPLID
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
AKM,
It's not necesary to pro rate, if the move happends in the middle of a period then the action is effective the following period. This is standar HR/Payroll pratice. Same thing for promotions and pay rate changes, they take effect the following period. It the data entry people understood this better then they would make each action take effect on the close of a pay period, so it would look cleaner.
-Isaiah -
This has been a really fun topic! Just for fun I populated a huge CHECK_DATA table to see the effects of the queries for a long history. I am getting the least costly (to the server) results from:
declare @@RegCost float
declare @@FullCost float
set @@FullCost = (select sum(amount)
from check_data)
set @@RegCost = (select sum(c.amount)
from check_data c
inner join ps_job j on c.emplid = j.emplid where j.reg_temp = 'R' AND c.payend_dt>=j.effdt)
select (cast(@@FullCost - @@RegCost as money)) as TempCost
GO
Ryan
-
Ryan,
Great code! I like the approach (full cost - temp) as it can be applied to both sides, ie calculate regular or temp. I'm enjoying pulling apart the SQL from the various posts and trying to figure how the concepts can be applied elswhere. This is a great way for me to learn how other people approach the same problem.
-Isaiah -
Ryan,
The above SQL gives the grand total however, I need to show the total for each employee as well (grouped by employee) Also, to be able to pass a date range like 1/1/2004 --> 12/31/2004 would make this wonderful!
THANKS A MILLION
!
-Isaiah
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply