question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked akhterhussain-3167 commented

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

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

112149-image.png


sql-server-generalsql-server-transact-sql
image.png (85.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MelissaMa-msft
Thanks for giving me extra favor

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.


113216-reg1.png


113256-date2.txt


0 Votes 0 ·
reg1.png (46.6 KiB)
date2.txt (11.4 KiB)
RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered RyanAbbey-0701 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


113071-image.png

113042-data.txt


0 Votes 0 ·
image.png (41.4 KiB)
data.txt (5.4 KiB)
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167

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

112373-output1.png

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

112337-output2.png

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.


output1.png (38.6 KiB)
output2.png (30.3 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Check below my output.It is not marking absent on Sunday,as i used your last provided query,




112531-image.png



0 Votes 0 ·
image.png (59.2 KiB)

Hi @akhterhussain-3167

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

0 Votes 0 ·

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 ,

0 Votes 0 ·