question

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

Query Require for attendance register

Below Is Data

         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')

Column Detail

EnrollNumber =will come from table empMaster .
D_Date/Day = will come from MonthDate table.
A_Date(IN/Out) = will come from #AttendenceLOG table.
Status = If Day is equal to Sunday then Mentioned 'S'
if IN and Out Time Does not exit of employee ,then mentioned 'A',
if IN/OUT Time found and Hours is equal to 8 hours or more then, then Mentioned 'P' otherwise 'A'

103788-image.png


sql-server-generalsql-server-transact-sql
image.png (48.0 KiB)
· 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.

What determines IN vs OUT date/time? If there is only one entry for the employee - shouldn't that be considered IN?

The rest of this is a simple join using >= and < for a date range when joining the #MonthDate and #AttendanceLog tables.

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

Hi @akhterhussain-3167

Please refer below updated one:

 ;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)
 SELECT *
 ,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
 ORDER BY D_Date,EnrollNumbeR

104084-output.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.



output.png (30.0 KiB)
· 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.

Thanks @MelissaMa-msft ,further more modification i have to do in this report....will as in next question.

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

Hi @akhterhussain-3167

I checked your sample data and expected output but found that the data of EnrollNumber=10004 was different from your expected outout , and the hour was wrong while calculating the period of '2021-06-09 08:04:00.000' and '2021-06-09 17:45:00.000'.

Besides, how will you judge whether A_Date is under [in] or [out] if there is only one A_Date per one D_Date? For example, the row of EnrollNumber=10003.

Please help check above and provide your confirmation. Thanks.

104031-1.png
104032-2.png


You could also refer below based on your sample data and set the judge time as 12:00 PM. You could change this judge time to 2:00 PM or any other time according to your own requirement.

 ;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)
 SELECT *
 ,CASE WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'
     WHEN [DAY] ='Sunday' 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

Output:
104041-3.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.


3.png (30.4 KiB)
1.png (20.4 KiB)
2.png (56.4 KiB)
· 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

Your query is working fine,but one thing left,which i did not mentioned that ,if Day is Sunday then Mentioned in status 'S'

i tried ,but it is not working'

     WHEN [DAY] ='Sunday' THEN 'S'
0 Votes 0 ·