Insert or update value which is not exists in table

Analyst_SQL 3,551 Reputation points
2022-11-19T09:12:28.38+00:00

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

262009-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Jonathon Kindred 406 Reputation points
    2022-11-19T21:36:56.877+00:00

    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'  
    

  2. NikoXu-msft 1,911 Reputation points
    2022-11-21T06:08:56.473+00:00

    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".