question

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

Concate TimeIn,TimeOut and Absent in One Column

I want to display TimeIN ,Timeout and Absent in one column,

 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)
   ,CET3 as (
   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)
   SELECT EnrollNumbeR,D_Date,Day
   ,CASE WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL AND [STATUS]='A' 
   THEN [STATUS] 
   ELSE CONVERT(varchar(30),[IN(A_Date)],121) 
   END [IN(A_Date)]
   ,[OUT(A_Date)],[STATUS],[HOUR]
   FROM CET3
    ORDER BY D_Date,EnrollNumbeR

112033-image.png


sql-server-generalsql-server-transact-sql
image.png (72.5 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.

 ;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

112085-output.png

If above is still not working, please provide your expected output. Thanks.

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 (22.2 KiB)
output.png (21.9 KiB)
output.png (22.7 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.

@MelissaMa-msft

I want to just display Time IN AM and PM ,please do little bit favour

0 Votes 0 ·

Hi @akhterhussain-3167,

I updated above query and please check again.

Thank you.

Best regards,
Melissa

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

Hi @akhterhussain-3167,

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)
    SELECT EnrollNumber,D_Date,Day,CONCAT(convert(varchar(30),[IN(A_Date)],120),CHAR(10)+ CHAR(13),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
    ORDER BY D_Date,EnrollNumbeR

111958-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 (25.7 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

I also want to concate Status Column,Means Where Time exist ,then display time other Display A and S in Same column.

0 Votes 0 ·