August 27, 2015 at 4:55 pm
Hi Folks,
I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.
Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.
Thank you and really appreciate all the help I get from this wonderful group.
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS
(
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL
SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT Person
, TransactDate, STARTDATE, END_DATE
, MIN(IN_PUNCH) OVER (PARTITION BY Person,TransactDate) AS IN_PUNCH
, MAX(OUT_PUNCH) OVER (PARTITION BY Person, TransactDate) AS OUT_PUNCH
,HOURS
,PAYCODE
FROM SampleData
ORDER BY 1,2,8
Current Results
PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1
Desired Results
PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1 NULL
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2 NULL
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1 1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2 2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1 NULL
September 3, 2015 at 7:07 am
Okay, take a look at the following code:
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH, OUT_PUNCH, [HOURS], PAYCODE) AS (
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3','REG1' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2','LEAVE2' UNION ALL
SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1','LEAVE1' UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1','REG1' UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
),
LEAVES_TOGETHER AS (
SELECT D.*
FROM SampleData AS D
INNER JOIN SampleData AS S
ON D.PERSON = S.PERSON
AND D.TRANSACTDATE = S.TRANSACTDATE
AND D.PAYCODE <> S.PAYCODE
AND S.PAYCODE IN ('LEAVE1', 'LEAVE2')
WHERE D.PAYCODE IN ('LEAVE1', 'LEAVE2')
)
SELECT SD.PERSON
, SD.TransactDate, SD.STARTDATE, SD.END_DATE
, MIN(SD.IN_PUNCH) OVER (PARTITION BY SD.Person, SD.TransactDate) AS IN_PUNCH
, MAX(SD.OUT_PUNCH) OVER (PARTITION BY SD.Person, SD.TransactDate) AS OUT_PUNCH
, SD.[HOURS]
, SD.PAYCODE
, RIGHT(LT.PAYCODE, 1) AS FLAG
FROM SampleData AS SD
LEFT OUTER JOIN LEAVES_TOGETHER AS LT
ON SD.PERSON = LT.PERSON
AND SD.TRANSACTDATE = LT.TRANSACTDATE
AND SD.STARTDATE = LT.STARTDATE
AND SD.END_DATE = LT.END_DATE
AND SD.[HOURS] = LT.[HOURS]
AND SD.IN_PUNCH = LT.IN_PUNCH
AND SD.OUT_PUNCH = LT.OUT_PUNCH
ORDER BY SD.PERSON, SD.TRANSACTDATE, SD.PAYCODE;
I'm not sure how this will perform, as a look at the execution plan shows that 98% of the cost is in two Sort operators. This may mean that a properly indexed source table would allow this to fly, but only testing can determine that. Sizable volume could also slow it down. Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 3, 2015 at 1:23 pm
One more option (may not be the best but it works).
create table #temp(Person nvarchar(50),TrnDt date, StartDt date, EndDt date,In_Punch time, Out_Punch time,WkHours int, PayCode nvarchar(50),[XFlag] int)
insert#temp([Person],[TrnDt],[StartDt],[EndDt],[In_Punch],[Out_Punch],[WkHours],[PayCode])
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL
SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
select [Person],[StartDt],[EndDt], Count(*) 'RowCnt' into #x
from #temp a where a.[PayCode] in ('LEAVE1') and exists (select 1 from #temp b where a.[Person] = b.[Person] and a.StartDt = b.StartDt and b.PayCode = 'LEAVE2')
group by [Person],[StartDt],[EndDt]
updateA
setA.[XFlag] = case when A.[PayCode] = 'LEAVE1' then 1 when A.[PayCode] = 'LEAVE2' then 2 else NULL end
from#temp A
INNER JOIN #x b on a.[Person] = b.[Person] and a.[StartDt] = b.[StartDt] and a.[EndDt] = b.[EndDt]
select * from #temp
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply