Insert or update value which is not exists in table

Question

I want ,those value get insert or Update (according to date and EnrollNumber ,which not exists in a table,

below is query and data

 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,A_Time Time(7))  
      
  Create table #tbl_Emp_Register  ( EnrollNumbeR int,Date date,Day varchar(4),TimeIN time(7),TimeOut time(7),Hour int,Duty int, OT int)  
      
                   Insert into #EmpMaster values (10001,'ALi')  
            Insert into #EmpMaster values (10002,'Hussain')  
                      Insert into #MonthDate values ('Wednesday','2022-11-01',null)   
                      Insert into #MonthDate values ('Thursday','2022-11-02',null)   
                      Insert into #MonthDate values ('Friday','2022-11-03',null)   
                      Insert into #MonthDate values ('saturday','2022-11-04',null)   
                      Insert into #MonthDate values ('Sunday','2022-11-05',null)   
      
                 Insert into #AttendenceLOG values (10001,'2022-11-01 08:04:00',null,'08:04:00')  
             
                 Insert into #AttendenceLOG values (10001,'2022-11-02 08:04:00',null,'08:04:00')  
                 Insert into #AttendenceLOG values (10001,'2022-11-02 17:45:00',null,'17:45:00')  

  
;with   
 cte as (  
      
  select a.EnrollNumber,b.Date,b.Day,b.Holiday,c.A_Time 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.Date)     
  ,cte1 AS (  
  SELECT  EnrollNumbeR,Date,Day,Holiday  
  ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
  FROM CTE   
  GROUP BY EnrollNumbeR,Date,Day,Holiday)  
       
  ,Cte2 as (  
  SELECT EnrollNumbeR,Date,LEFT(Day,3)Day,Holiday  
  ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(([IN(A_Date)] ) as Time(7))) THEN NULL ELSE [IN(A_Date)] END TimeIN  
  ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(([OUT(A_Date)] ) AS time(7))) THEN [OUT(A_Date)] ELSE NULL END TimeOut  
  FROM cte1  
  )  
  , cte3 as (  
  Select EnrollNumbeR,Date,LEFT(Day,3)Day,Holiday,TimeIN,TimeOut,  
       
   CASE WHEN TimeIN IS NOT NULL AND TimeOut IS NOT NULL AND DATEDIFF(HOUR,TimeIN,TimeOut)>=8   
  THEN CAST(CAST(DATEDIFF(MINUTE,TimeIN,TimeOut) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
       
      from  cte2  
   ),cte_F as (  
   Select EnrollNumbeR,Date,LEFT(Day,3)Day,Holiday,TimeIN,TimeOut,Round(Hour,0)Hour,  
   case when Hour>=8 then 1 when Hour=4 then 0.5 else 0 End Duty,  
   case when Hour>9 then  Round(Hour-9,0) end OT  
  from cte3)  
      
  insert  tbl_Emp_register  ( EnrollNumbeR,Date,Day,Holiday,TimeIN,TimeOut,Hour,Duty, OT)  
      
    Select EnrollNumbeR,Date,Day,Holiday,TimeIN,TimeOut,Hour,  
    Duty,  
    OT  
  from cte_F  
      
  where Date between '2022-11-01' and '2022-11-16'    

as you can see image and expected output,i marked in red,which is not exists in table,which i later added ,means that below data ,i added later or next day

      Insert into #AttendenceLOG values (10001,'2022-11-01 19:45:00',null,' 19:45:00')  
  
        Insert into #AttendenceLOG values (10001,'2022-11-03 11:45:00',null,'11:45:00')  
                 Insert into #AttendenceLOG values (10001,'2022-11-03 18:45:00',null,'17:45:00')  

Answer 1

You're just updating a table based on entered data.

if you want to just update specific fields you'd put:

update <table_name>  
set <column> = <value>  
where <condition>  

so

update #AttendenceLOG  
set TimeOut = '19:45:00'  
where enrollNumber = 10001 and date = '2022-11-01'  

Answer 2

Hi @Analyst_SQL ,

Maybe you can do this:

select EnrollNumbeR ,Date,Day,Holiday,TimeIN,  
cast('19:45' as time)as TimeOut   
from tbl_Emp_Register   
where  EnrollNumbeR=10001 and Date='2022-11-01'  
union all  
select EnrollNumbeR ,Date,Day,Holiday,TimeIN,  
cast('17:45' as time)as TimeOut   
from tbl_Emp_Register   
where  EnrollNumbeR=10001 and Date='2022-11-02'  
union all  
select EnrollNumbeR ,Date,Day,Holiday  
,cast('11:45' as time)as TimeIn  
,cast('17:45' as time)as TimeOut   
from tbl_Emp_Register   
where  EnrollNumbeR=10001 and Date='2022-11-03'  

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".