Mark Sunday as Absent ,when Saturday and Monday Found Absent?

Question

Mark Sunday as Absent ,when Saturday and Monday Found Absent?

Analyst_SQL 3,576

I want to mark Sunday as Absent(A),when time does not come on Saturday and Monday ,then Mark Sunday as Absent ,other wise Mark Sunday as S ,if Saturday or Sunday time exit from each.

as you can see in image,Enrollnumber form (10001 to 10004) ,time does not exit on date 2021-06-05 and 2021-06-07,except EnrollNumber 10005 Time exit on 2021-06-07.
Then (10001 to 10004) sunday will be mark as a Absent(A) and 10005 sunday Mark as (S).

 Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
              Create table #MonthDate (Day varchar(50),D_Date date)  
          Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)  
             Insert into #EmpMaster values (10001,'ALi')  
          Insert into #EmpMaster values (10002,'Kami')  
          Insert into #EmpMaster values (10003,'Jhon')  
          Insert into #EmpMaster values (10004,'Muli')   
            Insert into #EmpMaster values (10005,'Rose')   
                   
               Insert into #MonthDate values ('Sunday','2021-06-06')   
                   Insert into #MonthDate values ('Monday','2021-06-07')   
                      Insert into #MonthDate values ('Tuesday','2021-06-08')   
                         Insert into #MonthDate values ('Wednesday','2021-06-09')   
                  
                     
           Insert into #AttendenceLOG values (10001,'2021-06-09 08:04:00')  
            Insert into #AttendenceLOG values (10001,'2021-06-09 17:45:00')  
                Insert into #AttendenceLOG values (10002,'2021-06-09 10:04:00')  
               Insert into #AttendenceLOG values (10003,'2021-06-09 20:04:00')  
            Insert into #AttendenceLOG values (10004,'2021-06-09 07:45:00')  
              Insert into #AttendenceLOG values (10004,'2021-06-09 09:45:00')  
                 Insert into #AttendenceLOG values (10004,'2021-06-09 20:35:00')  
  
;with cte as (  
 select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
 from #EmpMaster a   
 cross join #MonthDate b  
 left join #AttendenceLOG c   
 on a.EnrollNumber=c.EnrollNumber   
 and cast(c.A_Date as date)=b.D_Date)  
 ,cte1 AS (  
 SELECT  EnrollNumbeR,D_Date,Day  
 ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
 FROM CTE   
 GROUP BY EnrollNumbeR,D_Date,Day)  
 ,CTE2 AS (  
 SELECT EnrollNumbeR,D_Date,Day  
 ,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,D_Date,Day,CONCAT(convert(varchar(30),[IN(A_Date)],120), CHAR(10),convert(varchar(30),[OUT(A_Date)],120)) [IN(A_Date)]  
 ,CASE WHEN [DAY] ='Sunday' THEN 'S'  
         WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
     WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 THEN 'P'  
     ELSE 'A' END STATUS,  
 CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8   
 THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
 FROM CTE2)  
 SELECT EnrollNumber,D_Date,Day,  
 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:ss tt'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm:ss tt'))  
 ELSE [STATUS] END [IN(A_Date)]  
 ,HOUR  
 FROM CTE3  
 ORDER BY D_Date,EnrollNumbeR

Accepted answer

4 additional answers

Your answer

Answer 1

Hi @Analyst_SQL ，

Please refer below updated one:

SET DATEFIRST 6;  
  
;with cte as (  
select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
from #EmpMaster a   
cross join #MonthDate b  
left join #AttendenceLOG c   
on a.EnrollNumber=c.EnrollNumber   
and cast(c.A_Date as date)=b.D_Date)  
,cte1 AS (  
SELECT  EnrollNumbeR,D_Date,Day  
,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
FROM CTE   
GROUP BY EnrollNumbeR,D_Date,Day)  
,CTE2 AS (  
SELECT EnrollNumbeR,D_Date,Day  
,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)  
,SUNDAY AS (  
SELECT EnrollNumbeR,datepart(week, d_date) weekno FROM CTE2  
WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)  
AND [DAY] IN ('Saturday','Monday')  
)  
,CTE3 AS (  
SELECT  distinct a.*  
,CASE WHEN a.[DAY] ='Sunday' AND b.EnrollNumbeR is not null   THEN 'S'  
    WHEN a.[IN(A_Date)] IS NULL AND a.[OUT(A_Date)] IS NULL THEN 'A'  
WHEN DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8 THEN 'P'  
ELSE 'A' END STATUS,  
CASE WHEN a.[IN(A_Date)] IS NOT NULL AND a.[OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8   
THEN CAST(CAST(DATEDIFF(MINUTE,a.[IN(A_Date)],a.[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
FROM CTE2 a left join SUNDAY b on a.EnrollNumbeR=b.EnrollNumbeR and datepart(week, a.d_date)=b.weekno   
)  
SELECT EnrollNumber,D_Date,Day,  
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:ss tt'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm:ss tt'))  
ELSE [STATUS] END [IN(A_Date)],HOUR  
FROM CTE3  
ORDER BY EnrollNumbeR,D_Date

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Analyst_SQL 3,576 Reputation points

2021-07-09T10:51:48.947+00:00

@MelissaMa-MSFT
Thanks for giving me extra favor

Answer 2

Hi @Analyst_SQL ，

Please refer below:

 --Saturday is now considered the first day of the week  
  SET DATEFIRST 6;  
      
  ;with cte as (  
  select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
  from #EmpMaster a   
  cross join #MonthDate b  
  left join #AttendenceLOG c   
  on a.EnrollNumber=c.EnrollNumber   
  and cast(c.A_Date as date)=b.D_Date)  
  ,cte1 AS (  
  SELECT  EnrollNumbeR,D_Date,Day  
  ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
  FROM CTE   
  GROUP BY EnrollNumbeR,D_Date,Day)  
  ,CTE2 AS (  
  SELECT EnrollNumbeR,D_Date,Day  
  ,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)  
  ,SUNDAY AS (  
  SELECT *,datepart(week, d_date) weekno FROM CTE2  
  WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)  
  AND [DAY] IN ('Saturday','Monday')  
  )  
  ,CTE3 AS (  
  SELECT a.*  
  ,CASE WHEN a.[DAY] ='Sunday' AND b.EnrollNumbeR is not null   THEN 'S'  
      WHEN a.[IN(A_Date)] IS NULL AND a.[OUT(A_Date)] IS NULL THEN 'A'  
  WHEN DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8 THEN 'P'  
  ELSE 'A' END STATUS,  
  CASE WHEN a.[IN(A_Date)] IS NOT NULL AND a.[OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8   
  THEN CAST(CAST(DATEDIFF(MINUTE,a.[IN(A_Date)],a.[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
  FROM CTE2 a left join SUNDAY b on a.EnrollNumbeR=b.EnrollNumbeR and datepart(week, a.d_date)=b.weekno  
  )  
  SELECT EnrollNumber,D_Date,Day,  
  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:ss tt'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm:ss tt'))  
  ELSE [STATUS] END [IN(A_Date)]  
  ,HOUR  
  FROM CTE3  
ORDER BY EnrollNumbeR,D_Date

Output:

EnrollNumber	D_Date	Day	IN(A_Date)	HOUR  
10001	2021-06-01	Tuesday	2021-06-01 08:00:00 AM 2021-06-01 06:00:00 PM	10.00  
10001	2021-06-02	Wednesday	A	0.00  
10001	2021-06-03	Thursday	A	0.00  
10001	2021-06-04	Friday	A	0.00  
10001	2021-06-05	Saturday	A	0.00  
10001	2021-06-06	Sunday	S	0.00  
10001	2021-06-07	Monday	 2021-06-07 08:35:00 PM	0.00  
10001	2021-06-08	Tuesday	A	0.00  
10001	2021-06-09	Wednesday	2021-06-09 10:04:00 AM 2021-06-09 08:04:00 PM	10.00  
10001	2021-06-10	Thursday	2021-06-10 08:00:00 AM 2021-06-10 06:00:00 PM	10.00  
10001	2021-06-11	Friday	2021-06-11 10:04:00 AM 2021-06-11 08:04:00 PM	10.00  
10001	2021-06-12	Saturday	A	0.00  
10001	2021-06-13	Sunday	A	0.00  
10001	2021-06-14	Monday	A	0.00  
10001	2021-06-15	Tuesday	2021-06-15 07:45:00 AM 2021-06-15 09:45:00 PM	14.00  
10001	2021-06-16	Wednesday	2021-06-16 08:35:00 AM 2021-06-16 08:35:00 PM	12.00  
10001	2021-06-17	Thursday	A	0.00  
10001	2021-06-18	Friday	A	0.00  
10001	2021-06-19	Saturday	A	0.00  
10001	2021-06-20	Sunday	A	0.00  
10001	2021-06-21	Monday	A	0.00  
10001	2021-06-22	Tuesday	A	0.00  
10001	2021-06-23	Wednesday	A	0.00  
10001	2021-06-24	Thursday	A	0.00  
10001	2021-06-25	Friday	A	0.00  
10001	2021-06-26	Saturday	A	0.00  
10001	2021-06-27	Sunday	A	0.00  
10001	2021-06-28	Monday	A	0.00  
10001	2021-06-29	Tuesday	A	0.00  
10001	2021-06-30	Wednesday	A	0.00  
10002	2021-06-01	Tuesday	A	0.00  
10002	2021-06-02	Wednesday	A	0.00  
10002	2021-06-03	Thursday	A	0.00  
10002	2021-06-04	Friday	A	0.00  
10002	2021-06-05	Saturday	A	0.00  
10002	2021-06-06	Sunday	A	0.00  
10002	2021-06-07	Monday	A	0.00  
10002	2021-06-08	Tuesday	A	0.00  
10002	2021-06-09	Wednesday	A	0.00  
10002	2021-06-10	Thursday	A	0.00  
10002	2021-06-11	Friday	A	0.00  
10002	2021-06-12	Saturday	A	0.00  
10002	2021-06-13	Sunday	A	0.00  
10002	2021-06-14	Monday	A	0.00  
10002	2021-06-15	Tuesday	A	0.00  
10002	2021-06-16	Wednesday	A	0.00  
10002	2021-06-17	Thursday	A	0.00  
10002	2021-06-18	Friday	A	0.00  
10002	2021-06-19	Saturday	A	0.00  
10002	2021-06-20	Sunday	A	0.00  
10002	2021-06-21	Monday	A	0.00  
10002	2021-06-22	Tuesday	A	0.00  
10002	2021-06-23	Wednesday	A	0.00  
10002	2021-06-24	Thursday	A	0.00  
10002	2021-06-25	Friday	A	0.00  
10002	2021-06-26	Saturday	A	0.00  
10002	2021-06-27	Sunday	A	0.00  
10002	2021-06-28	Monday	A	0.00  
10002	2021-06-29	Tuesday	A	0.00  
10002	2021-06-30	Wednesday	A	0.00

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Analyst_SQL 3,576 Reputation points

2021-07-09T07:42:50.017+00:00

Hi @MelissaMa-MSFT

Check Enroll Number 10001,when i am inserting row from '2021-06-21' to 2021-06-25' then days getting double. Please check below attached data.

113256-date2.txt

Answer 3

Hi @Analyst_SQL ，

According to your expected output, I added two more rows of data.

Insert into #MonthDate values ('Saturday','2021-06-05')     
Insert into #AttendenceLOG values (10005,'2021-06-07 08:04:00')

Please refer below:

;with cte as (  
select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
from #EmpMaster a   
cross join #MonthDate b  
left join #AttendenceLOG c   
on a.EnrollNumber=c.EnrollNumber   
and cast(c.A_Date as date)=b.D_Date)  
,cte1 AS (  
SELECT  EnrollNumbeR,D_Date,Day  
,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
FROM CTE   
GROUP BY EnrollNumbeR,D_Date,Day)  
,CTE2 AS (  
SELECT EnrollNumbeR,D_Date,Day  
,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)  
,SUNDAY AS (  
SELECT EnrollNumbeR FROM CTE2  
WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)  
AND [DAY] IN ('Saturday','Monday'))  
SELECT *  
,CASE WHEN [DAY] ='Sunday' AND EnrollNumbeR IN (SELECT EnrollNumbeR FROM SUNDAY)   THEN 'S'  
        WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
    WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 THEN 'P'  
    ELSE 'A' END STATUS,  
CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8   
THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
FROM CTE2  
ORDER BY D_Date,EnrollNumbeR

OR

;with cte as (  
select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
from #EmpMaster a   
cross join #MonthDate b  
left join #AttendenceLOG c   
on a.EnrollNumber=c.EnrollNumber   
and cast(c.A_Date as date)=b.D_Date)  
,cte1 AS (  
SELECT  EnrollNumbeR,D_Date,Day  
,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
FROM CTE   
GROUP BY EnrollNumbeR,D_Date,Day)  
,CTE2 AS (  
SELECT EnrollNumbeR,D_Date,Day  
,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)  
,SUNDAY AS (  
SELECT EnrollNumbeR FROM CTE2  
WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)  
AND [DAY] IN ('Saturday','Monday'))  
,CTE3 AS (  
SELECT *  
,CASE WHEN [DAY] ='Sunday' AND EnrollNumbeR IN (SELECT EnrollNumbeR FROM SUNDAY)   THEN 'S'  
    WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 THEN 'P'  
ELSE 'A' END STATUS,  
CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8   
THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
FROM CTE2)  
SELECT EnrollNumber,D_Date,Day,  
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:ss tt'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm:ss tt'))  
ELSE [STATUS] END [IN(A_Date)]  
,HOUR  
FROM CTE3  
ORDER BY D_Date,EnrollNumbeR

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Analyst_SQL 3,576 Reputation points

2021-07-07T08:19:53.467+00:00

Check below my output.It is not marking absent on Sunday,as i used your last provided query,
MelissaMa-MSFT 24,221 Reputation points

2021-07-07T08:33:54.89+00:00

Hi @Analyst_SQL ，

My query is working with your sample data.

Could you please provide your data used in your snapshot? Then I could enhance the query.

Do your actual data include data of more than one week?

Thanks.

Best regards,
Melissa
Analyst_SQL 3,576 Reputation points

2021-07-07T10:10:45.75+00:00

Hi @MelissaMa-MSFT ,

Do your actual data include data of more than one week?
Yes it is a month data of each employee.

Yes your provided query is working fine with dummy data, but actual data output is not coming properly ,

Answer 4

Hi @Analyst_SQL ，

I added some more data so that we could have data of more than one week.

I also set Saturday as the first day of the week so that we could consider Saturday, Sunday and Monday in the same week since there were more than one week in your actual data.

Please refer below updated one and check whether it is working.

drop table if exists #EmpMaster,#MonthDate,#AttendenceLOG  
  
Create table #EmpMaster (EnrollNumber int, empName varchar(50))  
Create table #MonthDate (Day varchar(50),D_Date date)  
Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)  
Insert into #EmpMaster values (10001,'ALi')  
Insert into #EmpMaster values (10002,'Kami')  
Insert into #EmpMaster values (10003,'Jhon')  
Insert into #EmpMaster values (10004,'Muli')   
Insert into #EmpMaster values (10005,'Rose')   
  
Insert into #MonthDate values ('Saturday','2021-05-29')    
Insert into #MonthDate values ('Sunday','2021-05-30')   
Insert into #MonthDate values ('Monday','2021-05-31')   
Insert into #MonthDate values ('Tuesday','2021-06-01')  
Insert into #MonthDate values ('Wednesday','2021-06-02')   
Insert into #MonthDate values ('Saturday','2021-06-05')                        
Insert into #MonthDate values ('Sunday','2021-06-06')   
Insert into #MonthDate values ('Monday','2021-06-07')   
Insert into #MonthDate values ('Tuesday','2021-06-08')   
Insert into #MonthDate values ('Wednesday','2021-06-09')   
                    
                         
Insert into #AttendenceLOG values (10001,'2021-06-09 08:04:00')  
Insert into #AttendenceLOG values (10001,'2021-06-09 17:45:00')  
Insert into #AttendenceLOG values (10002,'2021-06-09 10:04:00')  
Insert into #AttendenceLOG values (10003,'2021-06-09 20:04:00')  
Insert into #AttendenceLOG values (10004,'2021-06-09 07:45:00')  
Insert into #AttendenceLOG values (10004,'2021-06-09 09:45:00')  
Insert into #AttendenceLOG values (10004,'2021-06-09 20:35:00')  
Insert into #AttendenceLOG values (10005,'2021-06-07 08:04:00')  
Insert into #AttendenceLOG values (10004,'2021-05-29 10:04:00')  
Insert into #AttendenceLOG values (10003,'2021-06-07 08:04:00')  
Insert into #AttendenceLOG values (10002,'2021-06-07 10:04:00')  
Insert into #AttendenceLOG values (10001,'2021-06-07 11:04:00')  
  
--Saturday is now considered the first day of the week  
 SET DATEFIRST 6;  
  
 ;with cte as (  
 select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
 from #EmpMaster a   
 cross join #MonthDate b  
 left join #AttendenceLOG c   
 on a.EnrollNumber=c.EnrollNumber   
 and cast(c.A_Date as date)=b.D_Date)  
 ,cte1 AS (  
 SELECT  EnrollNumbeR,D_Date,Day  
 ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
 FROM CTE   
 GROUP BY EnrollNumbeR,D_Date,Day)  
 ,CTE2 AS (  
 SELECT EnrollNumbeR,D_Date,Day  
 ,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)  
 ,SUNDAY AS (  
 SELECT *,datepart(week, d_date) weekno FROM CTE2  
 WHERE ([IN(A_Date)] IS NOT NULL OR [OUT(A_Date)] IS NOT NULL)  
 AND [DAY] IN ('Saturday','Monday')  
 )  
 ,CTE3 AS (  
 SELECT a.*  
 ,CASE WHEN a.[DAY] ='Sunday' AND b.EnrollNumbeR is not null   THEN 'S'  
     WHEN a.[IN(A_Date)] IS NULL AND a.[OUT(A_Date)] IS NULL THEN 'A'  
 WHEN DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8 THEN 'P'  
 ELSE 'A' END STATUS,  
 CASE WHEN a.[IN(A_Date)] IS NOT NULL AND a.[OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,a.[IN(A_Date)],a.[OUT(A_Date)])>=8   
 THEN CAST(CAST(DATEDIFF(MINUTE,a.[IN(A_Date)],a.[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
 FROM CTE2 a left join SUNDAY b on a.EnrollNumbeR=b.EnrollNumbeR and datepart(week, a.d_date)=b.weekno  
 )  
 SELECT EnrollNumber,D_Date,Day,  
 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:ss tt'),CHAR(10),FORMAT([OUT(A_Date)],'yyyy-MM-dd hh:mm:ss tt'))  
 ELSE [STATUS] END [IN(A_Date)]  
 ,HOUR  
 FROM CTE3  
 ORDER BY D_Date,EnrollNumbeR

Output:

EnrollNumber	D_Date	Day	IN(A_Date)	HOUR  
10001	2021-05-29	Saturday	A	0.00  
10002	2021-05-29	Saturday	A	0.00  
10003	2021-05-29	Saturday	A	0.00  
10004	2021-05-29	Saturday	2021-05-29 10:04:00 AM 	0.00  
10005	2021-05-29	Saturday	A	0.00  
10001	2021-05-30	Sunday	A	0.00  
10002	2021-05-30	Sunday	A	0.00  
10003	2021-05-30	Sunday	A	0.00  
10004	2021-05-30	Sunday	S	0.00  
10005	2021-05-30	Sunday	A	0.00  
10001	2021-05-31	Monday	A	0.00  
10002	2021-05-31	Monday	A	0.00  
10003	2021-05-31	Monday	A	0.00  
10004	2021-05-31	Monday	A	0.00  
10005	2021-05-31	Monday	A	0.00  
10001	2021-06-01	Tuesday	A	0.00  
10002	2021-06-01	Tuesday	A	0.00  
10003	2021-06-01	Tuesday	A	0.00  
10004	2021-06-01	Tuesday	A	0.00  
10005	2021-06-01	Tuesday	A	0.00  
10001	2021-06-02	Wednesday	A	0.00  
10002	2021-06-02	Wednesday	A	0.00  
10003	2021-06-02	Wednesday	A	0.00  
10004	2021-06-02	Wednesday	A	0.00  
10005	2021-06-02	Wednesday	A	0.00  
10001	2021-06-05	Saturday	A	0.00  
10002	2021-06-05	Saturday	A	0.00  
10003	2021-06-05	Saturday	A	0.00  
10004	2021-06-05	Saturday	A	0.00  
10005	2021-06-05	Saturday	A	0.00  
10001	2021-06-06	Sunday	S	0.00  
10002	2021-06-06	Sunday	S	0.00  
10003	2021-06-06	Sunday	S	0.00  
10004	2021-06-06	Sunday	A	0.00  
10005	2021-06-06	Sunday	S	0.00  
10001	2021-06-07	Monday	2021-06-07 11:04:00 AM 	0.00  
10002	2021-06-07	Monday	2021-06-07 10:04:00 AM 	0.00  
10003	2021-06-07	Monday	2021-06-07 08:04:00 AM 	0.00  
10004	2021-06-07	Monday	A	0.00  
10005	2021-06-07	Monday	2021-06-07 08:04:00 AM 	0.00  
10001	2021-06-08	Tuesday	A	0.00  
10002	2021-06-08	Tuesday	A	0.00  
10003	2021-06-08	Tuesday	A	0.00  
10004	2021-06-08	Tuesday	A	0.00  
10005	2021-06-08	Tuesday	A	0.00  
10001	2021-06-09	Wednesday	2021-06-09 08:04:00 AM 2021-06-09 05:45:00 PM	9.68  
10002	2021-06-09	Wednesday	2021-06-09 10:04:00 AM 	0.00  
10003	2021-06-09	Wednesday	 2021-06-09 08:04:00 PM	0.00  
10004	2021-06-09	Wednesday	2021-06-09 07:45:00 AM 2021-06-09 08:35:00 PM	12.83  
10005	2021-06-09	Wednesday	A	0.00

If above is still not working, please kindly provide more sample data of one month and expected output.

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Analyst_SQL 3,576 Reputation points

2021-07-08T12:42:35.157+00:00

Hi @MelissaMa-MSFT
please check below attachment with Data.

113042-data.txt

Answer 5

Without giving exact answer, how about something along the lines of

select em.*, attended.Sunday
from #EmpMaster em
join #MonthDate md on md.Day = 'Sunday'
left outer join (select distinct EnrolNumber, mds.D_date+1 as Sunday
from #MonthDate mds
join #AttendanceLog al on mds.D_Date <= al.A_Date and mds.D_date+3 > al.A_Date
where mds.Day = 'Saturday'

) attended on em.enrolnumber = attended.enrolnumber and md.D_Date = Sunday

if Attended.Sunday is null, then they didn't attend Friday to Monday otherwise they did. You can then push that back to whatever it is you need to update

Share via

Mark Sunday as Absent ,when Saturday and Monday Found Absent?

4 additional answers

Your answer