Insert row after checking records between date

Question

Insert row after checking records between date

Analyst_SQL 3,576

Below is query which will be executed first,then (A) absent will be checked after and before H(Holidays) ,if A(Absent) is coming after and before H(Holiday) then rows will be inserted into table like below,

Date 2022-07-11 to 2022-07-15 is marked 'H' in table #MonthDate

    Insert into #AttendenceLOG values (10002,'2022-07-11 00:00:00',1)  
  Insert into #AttendenceLOG values (10002,'2022-07-12 00:00:00',1)  
   Insert into #AttendenceLOG values (10002,'2022-07-13 00:00:00',1)  
    Insert into #AttendenceLOG values (10002,'2022-07-14 00:00:00',1)  
     Insert into #AttendenceLOG values (10002,'2022-07-15 00:00:00',1)

first below query executed,

 Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
                  Create table #MonthDate (Day varchar(50),Date date,Holiday varchar(50))  
              Create table #AttendenceLOG (EnrollNumber int,A_Date datetime,H_status int)  
                 Insert into #EmpMaster values (10001,'ALi')  
          Insert into #EmpMaster values (10002,'Hussain')  
                    Insert into #MonthDate values ('Wednesday','2022-07-01',null)   
                    Insert into #MonthDate values ('Thursday','2022-07-02',null)   
                    Insert into #MonthDate values ('Friday','2022-07-03',null)   
                    Insert into #MonthDate values ('saturday','2022-07-04',null)   
                    Insert into #MonthDate values ('Sunday','2022-07-05',null)   
                    Insert into #MonthDate values ('Monday','2022-07-07',null)   
                    Insert into #MonthDate values ('Tuesday','2022-07-08',null)   
                    Insert into #MonthDate values ('Wednesday','2022-07-09',null)   
                    Insert into #MonthDate values ('Thursday','2022-07-10',null)   
                    Insert into #MonthDate values ('Friday','2022-07-11','H')   
                    Insert into #MonthDate values ('Saturday','2022-07-12','H')   
                    Insert into #MonthDate values ('Sunday','2022-07-13','H')   
                    Insert into #MonthDate values ('Monday','2022-07-14','H')   
                    Insert into #MonthDate values ('Tuesday','2022-07-15','H')   
                    Insert into #MonthDate values ('Wednesday','2022-07-16',null)  
                    Insert into #MonthDate values ('Thursday','2022-07-17',null)      
                    Insert into #MonthDate values ('Friday','2022-07-18',null)      
                    Insert into #MonthDate values ('Saturday','2022-07-19',null)      
                    Insert into #MonthDate values ('Sunday','2022-07-20',null)      
                    Insert into #MonthDate values ('Monday','2022-07-21',null)             
                    Insert into #MonthDate values ('Tuesday','2022-07-22',null)      
                     Insert into #MonthDate values ('Wednesday','2022-07-23',null)      
                          Insert into #MonthDate values ('Thursday','2022-07-24',null)  
                              Insert into #MonthDate values ('Friday','2022-07-25',null)  
                                  Insert into #MonthDate values ('Saturday','2022-07-26',null)  
                                     Insert into #MonthDate values ('Sunday','2022-07-27',null)  
                                         Insert into #MonthDate values ('Monday','2022-07-28',null)  
                                             Insert into #MonthDate values ('Tuesday','2022-07-29',null)  
              
              
                      
               Insert into #AttendenceLOG values (10001,'2022-07-02 08:04:00',null)  
               Insert into #AttendenceLOG values (10001,'2022-07-02 17:45:00',null)  
               Insert into #AttendenceLOG values (10001,'2022-07-03 08:04:00',null)  
               Insert into #AttendenceLOG values (10001,'2022-07-03 17:45:00',null)  
                 Insert into #AttendenceLOG values (10001,'2022-07-07 08:04:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-07-09 07:45:00',null)  
               Insert into #AttendenceLOG values (10001,'2022-07-09 18:45:00',null)  
                      
                Insert into #AttendenceLOG values (10001,'2022-07-15 08:35:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-07-15 17:35:00',null)  
                 Insert into #AttendenceLOG values (10001,'2022-07-16 08:35:00',null)  
                Insert into #AttendenceLOG values (10001,'2022-07-16 17:35:00',null)  
                 Insert into #AttendenceLOG values (10001,'2022-07-17 08:35:00',null)  
                 Insert into #AttendenceLOG values (10001,'2022-07-17 17:35:00',null)  
                 Insert into #AttendenceLOG values (10001,'2022-07-25 08:35:00',null)  
                 Insert into #AttendenceLOG values (10001,'2022-07-25 17:35:00',null)  
          
      Insert into #AttendenceLOG values (10002,'2022-07-02 08:04:00',null)  
               Insert into #AttendenceLOG values (10002,'2022-07-02 17:45:00',null)  
               Insert into #AttendenceLOG values (10002,'2022-07-03 08:04:00',null)  
               Insert into #AttendenceLOG values (10002,'2022-07-03 17:45:00',null)  
                 Insert into #AttendenceLOG values (10002,'2022-07-07 08:04:00',null)  
                      
                      
                  
         
                 Insert into #AttendenceLOG values (10002,'2022-07-17 08:35:00',null)  
                 Insert into #AttendenceLOG values (10002,'2022-07-17 17:35:00',null)  
                 Insert into #AttendenceLOG values (10002,'2022-07-25 08:35:00',null)  
                 Insert into #AttendenceLOG values (10002,'2022-07-25 17:35:00',null)  
      
      
  ;WITH CTE AS   
  (  
   SELECT a.EnrollNumber,b.Date,b.Day,c.A_Date ,a.EmpName,b.Holiday,C.H_status   
   FROM #EmpMaster a CROSS JOIN #MonthDate b  
                     LEFT JOIN #AttendenceLOG c ON a.EnrollNumber=c.EnrollNumber AND cast(c.A_Date as date)=b.Date  
  ),CTE1 AS   
  (  
   SELECT  EnrollNumbeR,Date,Day,EmpName,Holiday,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)],H_status   
   FROM CTE   
   GROUP BY EnrollNumbeR,Date,Day,EmpName,Holiday,H_status   
  ),CTE2 AS   
  (  
   SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,EmpName,Holiday,H_status   
         ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME))   
               THEN NULL ELSE [IN(A_Date)] END [IN(A_Date)]  
         ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME))   
               THEN [OUT(A_Date)] ELSE NULL END [OUT(A_Date)]  
   FROM CTE1  
  ),CTE3 AS (  
   SELECT EnrollNumbeR,Date,Day,EmpName,Holiday,H_status ,[IN(A_Date)],[OUT(A_Date)]  
         ,CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=5  
               THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2))   
               ELSE 0 END [HOUR]  
         ,CASE WHEN  [IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL   
               THEN CONCAT(FORMAT([IN(A_Date)],'yyyy-MM-dd hh:mm'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm'))  
               ELSE NULL END [A_Date]  
         ,CASE WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
               WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=5 THEN 'P'  
               ELSE 'A' END [STATUS]  
   FROM CTE2  
  ),CTE4 AS (  
   SELECT EnrollNumbeR,EmpName,H_status ,Date,Day,[HOUR],Holiday,[IN(A_Date)],[OUT(A_Date)],A_Date  
         ,CASE WHEN [DAY] ='Sun' AND (LAG([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P' OR LEAD([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P') THEN 'S'   
    When Holiday='H' THEN 'H'  
               ELSE [STATUS] END [STATUS]  
   FROM CTE3   
  )  
  SELECT EnrollNumbeR,EmpName,Date,Day,[HOUR]  
        ,CASE WHEN STATUS='A' THEN 1   
              WHEN STATUS='S' THEN 0   
   WHEN STATUS='H' THEN 0   
              WHEN [IN(A_Date)] IS NULL OR [OUT(A_Date)] IS  NULL THEN 1   
              ELSE 0 END [Absent]  
        ,CASE WHEN STATUS='A' THEN 0   
              WHEN STATUS='S' THEN 1   
    WHEN STATUS='H' THEN 1  
              WHEN [IN(A_Date)] IS NULL OR [OUT(A_Date)] IS NULL THEN 0     
              ELSE 1 END [Present]  
        ,STATUS,ISNULL([A_Date],STATUS) AS [IN(A_Date)],H_status   
  FROM CTE4  
  WHERE Date BETWEEN '2022-07-07' and '2022-07-25'  
  ORDER BY EnrollNumbeR,Date

1 answer

Your answer

Answer 1

Hi,

The result SET which you present not 100% clear to me. Why only these 5 rows should get value 1 ?

What about rows which has range of dates in the [IN(A_Date)] columns and what about the dates 11-15 for the EnrollNumber = 10001 ?

According to your explanation, the following should bring the result, bu as mentioned, this not fit the image 100%. Check if this fit your need.

Basically, instead of H_status I simply add:

,H_status = CASE
WHEN Absent = 0 and Not LEAD([Absent]) OVER (partition by EnrollNumber ORDER BY [Date]) IS NULL
THEN 1
ELSE H_status
END

Which looks like:

   ;WITH CTE AS   
   (  
    SELECT a.EnrollNumber,b.Date,b.Day,c.A_Date ,a.EmpName,b.Holiday,C.H_status   
    FROM #EmpMaster a   
 CROSS JOIN #MonthDate b  
 LEFT JOIN #AttendenceLOG c   
 ON a.EnrollNumber=c.EnrollNumber AND cast(c.A_Date as date)=b.Date  
   ),CTE1 AS   
   (  
    SELECT  EnrollNumbeR,Date,Day,EmpName,Holiday,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)],H_status   
    FROM CTE   
    GROUP BY EnrollNumbeR,Date,Day,EmpName,Holiday,H_status   
   ),CTE2 AS   
   (  
    SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,EmpName,Holiday,H_status   
          ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME))   
                THEN NULL ELSE [IN(A_Date)] END [IN(A_Date)]  
          ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME))   
                THEN [OUT(A_Date)] ELSE NULL END [OUT(A_Date)]  
    FROM CTE1  
   ),CTE3 AS (  
    SELECT EnrollNumber,Date,Day,EmpName,Holiday,H_status ,[IN(A_Date)],[OUT(A_Date)]  
          ,CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=5  
                THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2))   
                ELSE 0 END [HOUR]  
          ,CASE WHEN  [IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL   
                THEN CONCAT(FORMAT([IN(A_Date)],'yyyy-MM-dd hh:mm'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm'))  
                ELSE NULL END [A_Date]  
          ,CASE WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
                WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=5 THEN 'P'  
                ELSE 'A' END [STATUS]  
    FROM CTE2  
   ),CTE4 AS (  
    SELECT EnrollNumber,EmpName,H_status ,Date,Day,[HOUR],Holiday,[IN(A_Date)],[OUT(A_Date)],A_Date  
          ,CASE WHEN [DAY] ='Sun' AND (LAG([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P' OR LEAD([STATUS])OVER(ORDER BY EnrollNumbeR,Date)='P') THEN 'S'   
     When Holiday='H' THEN 'H'  
                ELSE [STATUS] END [STATUS]  
    FROM CTE3   
   ),  
   CTE____A01 as (  
   SELECT EnrollNumber,EmpName,Date,Day,[HOUR]  
         ,CASE WHEN STATUS='A' THEN 1   
               WHEN STATUS='S' THEN 0   
               WHEN STATUS='H' THEN 0   
               WHEN [IN(A_Date)] IS NULL OR [OUT(A_Date)] IS  NULL THEN 1   
               ELSE 0 END [Absent]  
         ,CASE WHEN STATUS='A' THEN 0   
               WHEN STATUS='S' THEN 1   
               WHEN STATUS='H' THEN 1  
               WHEN [IN(A_Date)] IS NULL OR [OUT(A_Date)] IS NULL THEN 0     
               ELSE 1 END [Present]  
         ,STATUS,ISNULL([A_Date],STATUS) AS [IN(A_Date)],H_status   
   FROM CTE4  
   WHERE Date BETWEEN '2022-07-07' and '2022-07-25'  
   --ORDER BY EnrollNumbeR,Date   
   )  
select   
 EnrollNumber, EmpName,[Date],[Day],[HOUR], [Absent], [Present], [STATUS], [IN(A_Date)]  
 /*-------------------  
 -- Next three column we do not need! I present it for the sake of the forum, just to clarify next Column  
 , MyGroup = SUM([Absent]) OVER (partition by EnrollNumber ORDER BY [Date])   
 , NextInGroup =  LEAD([Absent]) OVER (partition by EnrollNumber ORDER BY [Date])  
 , InGroup = CASE  
 WHEN Absent = 1 THEN 'FirstInGroup'  
 WHEN Absent = 0 and LEAD([Absent]) OVER (partition by EnrollNumber ORDER BY [Date]) = 0  
 THEN 'MiddleInGroup'  
 WHEN Absent = 0 and LEAD([Absent]) OVER (partition by EnrollNumber ORDER BY [Date]) = 1  
 THEN 'LastInGroup'  
 ELSE 'No Closure to group...'  
 END  
 -------------------------*/  
 ,H_status = CASE   
 WHEN Absent = 0 and Not LEAD([Absent]) OVER (partition by EnrollNumber ORDER BY [Date]) IS NULL  
 THEN 1  
 ELSE H_status  
 END  
from CTE____A01  
ORDER BY EnrollNumbeR,Date

If this not fit then maybe use for the last column this:

,H_status = CASE
WHEN Absent = 0 and Not LEAD([Absent]) OVER (partition by EnrollNumber ORDER BY [Date]) IS NULL and [IN(A_Date)] in ('A','H')
THEN 1
ELSE H_status
END

Analyst_SQL 3,576 Reputation points

2022-11-14T05:17:46.923+00:00

@Ronen Ariely

i answered your query ,

The result SET which you present not 100% clear to me. Why only these 5 rows should get value 1 ?

because ,i mentioned in table #MonthDate from 11 to 15 (H) Holiday Column.

What about rows which has range of dates in the [IN(A_Date)] columns and what about the dates 11-15 for the EnrollNumber = 10001 ?

EnrollNumber = 10001 ,is not absent(A) before and after from 11 to 15 ,that why 1 will not be marked in H_Status,

as your provided solution,is not fullfill my need,i do not want to add condition,

i want,first execute query ,then query will check ,that those employees ,who did not come before and after holidays 11-15 ,then insert rows accordingly against employee into table #AttendenceLOG and mark H_Status column 1.

Share via

Insert row after checking records between date

1 answer

Your answer