CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Evil rising
Evil rising

Reputation: 442

Showing result for specific person

I have written this query to Show overtime if person has worked above than 18:00 pm clock time regardless whatever time he/she came or duration of work he/she has worked, that is out of topic. It only shows OVERTIME if person has worked above 18:00 pm that's it, e.g InTime = 15:16 pm and TimeOut= 18:31 then Overtime = 00:31. So i wrote this query given below BUT NOW PROBLEM IS THAT FEW EMPLOYEES LEAVE AT 17:00 pm and their OVERTIME should be calculated after 17:00 pm (how many minutes+hours he has worked above 17:00 pm only), SO FOR THOSE SPECIFIC EMPLOYEES OVERTIME SHOULD BE CALCULATED AFTER 17:00 Pm

Those employees are:

EmplID 5, 43, 11

so only for these overtime should be calculated after 17:00.

query:

with times as (
SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , cast(min(t1.RecTime) as datetime) AS InTimeSub
        , cast(max(t2.RecTime) as datetime) AS TimeOutSub
        , t1.RecDate AS [DateVisited]
FROM  AtdRecord t1 
INNER JOIN 
      AtdRecord t2 
ON    t1.EmplID = t2.EmplID 
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join 
      HrEmployee t3 
ON    t3.EmplID = t1.EmplID 
group by 
          t1.EmplID
        , t3.EmplName
        , t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,CONVERT(char(5),CASE WHEN  CAST([TimeOutSub] AS DATETIME) >= '18:00' Then     
 LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST('18:00' AS DATETIME),   
 CAST([TimeOutSub] AS DATETIME)),0), 108),5) ELSE '00:00' END, 108) AS OVERTIME
FROM times

OUTPUT:

enter image description here

Upvotes: 0

Views: 67

Answers (1)

George Mastros
George Mastros

Reputation: 24498

Try replacing: 

CASE WHEN  CAST([TimeOutSub] AS DATETIME) >= '18:00' Then     
 LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST('18:00' AS DATETIME),   
 CAST([TimeOutSub] AS DATETIME)),0), 108),5) ELSE '00:00' END, 108) AS OVERTIME

With 

CASE WHEN  CAST([TimeOutSub] AS DATETIME) >= '18:00' And EmplId Not In (5,43,11)
     Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST('18:00' AS DATETIME),  CAST([TimeOutSub] AS DATETIME)),0), 108),5) 
     WHEN  CAST([TimeOutSub] AS DATETIME) >= '17:00' And EmplId In (5,43,11)
     Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST('17:00' AS DATETIME),  CAST([TimeOutSub] AS DATETIME)),0), 108),5) 
     ELSE '00:00' END, 108) AS OVERTIME

Basically, I just put multiple conditions on the WHEN portion. When time > 18:00 and emplid not IN (5,43,11) and then another condition for time > 17:00 and emplid in (5,43,11).

It may be easier to understand this as pseudo-code:

CASE WHEN  TIME >= '18:00' And EmplId Not In (5,43,11)
     Then  -- Calculate overtime at 18:00
     WHEN  TIME >= '17:00' And EmplId In (5,43,11)
     Then  -- Calculate overtime at 17:00
     ELSE '00:00' 
     END OVERTIME

Upvotes: 1

Related Questions