CODEWITHSUNDEEP
oracle-databaseoracle11g
user2119324
user2119324

Reputation: 597

how to get before8.30am,8.30-9am and after 9am employee punch data

how to get before8.30am,8.30-9am and after 9am employee punch data 

 select distinct  emp.emc_name 
    Count(select count(max(s.SAD_DATE)) from Shift s1 where s1.EM_CODE=s.EM_CODE and EXTRACT(HOUR FROM CAST(s.SAD_DATE AS TIMESTAMP)) < 8:30  ) Before8.30am  ,
    Count(select count(max(s.SAD_DATE)) from Shift s1 where s1.EM_CODE=s.EM_CODE and EXTRACT(HOUR FROM CAST(s.SAD_DATE AS TIMESTAMP)) between 8:30 and 9  ) 8.30amTo9am,
    Count(select count(max(s.SAD_DATE)) from Shift s1 where s1.EM_CODE=s.EM_CODE and EXTRACT(HOUR FROM CAST(s.SAD_DATE AS TIMESTAMP)) > 9  ) After9,
    from Shift s,emply emp
    where s.EM_CODE = emp.EM_CODE and emp.DP_CODE in  ('C019') and s.SAC_INOUT=1
    and s.SAD_DATE between '01-mar-2018' and '31-mar-2018'
    order by emp.EMC_NAME
    group by emp.EMC_NAME

Upvotes: 0

Views: 43

Answers (1)

MT0
MT0

Reputation: 168351

Something like:

SELECT              -- You don't need both DISTINCT and GROUP BY
       emp.emc_name, 
       COUNT(
         CASE
         WHEN SAD_DATE <  TRUNC( SAD_DATE ) + INTERVAL '08:30' HOUR TO MINUTE
         THEN 1
         END
       ) "Before8.30am",
       COUNT(
         CASE
         WHEN SAD_DATE >= TRUNC( SAD_DATE ) + INTERVAL '08:30' HOUR TO MINUTE
         AND  SAD_DATE <  TRUNC( SAD_DATE ) + INTERVAL '09:00' HOUR TO MINUTE
         THEN 1
         END
       ) "8.30amTo9am"
       COUNT(
         CASE
         WHEN SAD_DATE >= TRUNC( SAD_DATE ) + INTERVAL '09:00' HOUR TO MINUTE
         THEN 1
         END
       ) "After9"
from   Shift s
       INNER JOIN emply emp
       ON ( s.EM_CODE = emp.EM_CODE ) -- Use ANSI Joins not legacy Oracle joins
where  emp.DP_CODE in  ('C019')
and    s.SAC_INOUT = 1
and    s.SAD_DATE between DATE '2018-03-01' and DATE '2018-03-31'
                          -- Use a date literal and not implicit conversion of a string
group by emp.EMC_NAME  order by 1

Upvotes: 1

Related Questions