Search code examples
sqloracle10g

Query to define workink days from attendance

I have a SQL query result : enter image description here

Query :

SELECT  E.EMPID ,D.DT,to_char(D.DT,'Day') days ,case  
         when to_char(D.DT, 'fmday') = 'friday' then 'workday'
         when to_char(D.DT, 'fmday') = 'saturday' then 'workday'
         when to_char(D.DT, 'fmday') = 'monday' then 'workday' 
         when to_char(D.DT, 'fmday') = 'tuesday' then 'workday'
         when to_char(D.DT, 'fmday') = 'wednesday' then 'workday'
         when to_char(D.DT, 'fmday') = 'thursday' then 'workday' 
   else    'holiday' end work_days 
         
        ,D.DT+DBMS_RANDOM.VALUE(0,0.25/24)+ ((H.FHR+(H.FMT/60)+DECODE(H.FAM,'PM',12,0))/24) INTIME,
         D.DT+DBMS_RANDOM.VALUE(0,0.25/24)+ ((H.THR+(H.TMT/60)+DECODE(H.TAM,'PM',12,0))/24) OUTTIME
        ,E.ROWID  
  FROM EMP_SHIFT H, EMPL E,
           (SELECT LEVEL LVL , (TO_DATE('01-Dec-22','DD-MON-RR')+LEVEL-1) DT 
           FROM DUAL conNect by level  <= to_date('31-dec-22','DD-MON-RRRR')-TO_DATE('01-Dec-22','DD-MON-RRRR') +1 ) D
           WHERE E.SHIFT = H.CD

I need to get null yellow highlighed cells like the image below enter image description here

Solution

  • you can do something like this

    SELECT  E.EMPID ,D.DT,to_char(D.DT,'Day') days ,case  
         when to_char(D.DT, 'fmday') = 'friday' then 'workday'
         when to_char(D.DT, 'fmday') = 'saturday' then 'workday'
         when to_char(D.DT, 'fmday') = 'monday' then 'workday' 
         when to_char(D.DT, 'fmday') = 'tuesday' then 'workday'
         when to_char(D.DT, 'fmday') = 'wednesday' then 'workday'
         when to_char(D.DT, 'fmday') = 'thursday' then 'workday' 
   else    'holiday' end work_days ,
case  
         when to_char(D.DT, 'fmday') != 'friday' 
         or to_char(D.DT, 'fmday') != 'saturday' 
         or to_char(D.DT, 'fmday') != 'monday'  
         or to_char(D.DT, 'fmday') != 'tuesday' 
         or to_char(D.DT, 'fmday') != 'wednesday'
         or to_char(D.DT, 'fmday') != 'thursday' then NULL 
   else D.DT+DBMS_RANDOM.VALUE(0,0.25/24)+ ((H.FHR+(H.FMT/60)+DECODE(H.FAM,'PM',12,0))/24) end INTIME,
case  
         when to_char(D.DT, 'fmday') != 'friday' 
         or to_char(D.DT, 'fmday') != 'saturday' 
         or to_char(D.DT, 'fmday') != 'monday'  
         or to_char(D.DT, 'fmday') != 'tuesday' 
         or to_char(D.DT, 'fmday') != 'wednesday'
         or to_char(D.DT, 'fmday') != 'thursday' then NULL 
   else D.DT+DBMS_RANDOM.VALUE(0,0.25/24)+ ((H.THR+(H.TMT/60)+DECODE(H.TAM,'PM',12,0))/24) end OUTTIME 
,E.ROWID  
  FROM EMP_SHIFT H, EMPL E,
           (SELECT LEVEL LVL , (TO_DATE('01-Dec-22','DD-MON-RR')+LEVEL-1) DT 
           FROM DUAL conNect by level  <= to_date('31-dec-22','DD-MON-RRRR')-TO_DATE('01-Dec-22','DD-MON-RRRR') +1 ) D
           WHERE E.SHIFT = H.CD