and other table is targetemp:
create Table targetemp (EmpId INT, name NVARCHAR(10), Sdate DATE, checkvalue INT, deptno INT, deptname NVARCHAR(10))
Here we need change name values only when targetemp table checkvalue 2 related
sdate days must be less than equal to 30day
of sourceemp(emp table) flag 5 values with same empid.if we found multiple flag 2 values
with in 30days for same empid then we need change name only latest flag 2 sdate values
with flag 5 related name value.we need apply less than 30 days for flag 2 and flag5
values only.remain flag 1,3 values no need to check date condition.
below steps I have followed to achive this task
step 1: I am inserting checkvalues 1,2,3 related records into target emptable.
merge into targetemp as target
using (select * from emp where checkvalue in(1,2,3)) as source
on target.empid=source.empid and target.deptno=source.deptno
when not matched then insert( empid,name,sdate,checkvalue,deptno,deptname)values
(stag.empid,stag.name,stag.sdate,stag.checkvalue,stag.deptno,stag.deptname)
step 2 :I need to found lessthan 30days for targetemp table checkvalue 2 with emp table checkvalue 5 if fall multiple records targetemp check2 values
then we need to update max(sdate) checkvalue name only with emp table checkvalue 5 column related name .
I given update statment:
update targetemp set name=o.name
select * from empo join targetemp t on o.empid=t.empid and o.deptno=t.deptno and o.arel in('5') and t.arel in('2') and o.sdate<=t.sdate
and datediff(dd,o.sdate,t.sdate)<=30 and t.sdate=
(
select max(t.sdate) from empo join targetemp t on o.empid=t.empid and o.deptno=t.deptno and o.arel in('5') and t.arel in('2')
and o.sdate<=t.sdate and datediff(dd,o.sdate,t.sdate)<=30
)
here I am able to updated with correct source side checkvalue 5 related name with targetemp table checkvalues 2 related name.
step 3: pending for insert remain emp table checkvalue 5 lessthan 30days values into target table.
insert into targetemp
select * from empo join targetemp t on o.empid=t.empid and o.deptno=t.deptno and o.arel in('5') and t.arel in('2') and o.sdate<=t.sdate
and datediff(dd,o.sdate,t.sdate)<=30 and t.sdate<>
(
select max(t.sdate) from empo join targetemp t on o.empid=t.empid and o.deptno=t.deptno and o.arel in('5') and t.arel in('2')
and o.sdate<=t.sdate and datediff(dd,o.sdate,t.sdate)<=30
)
Here I facing the issue when I try to ran this quer 2nd or 3rd time again duplicate data load into targetemp.
I want avoid this case.please tell me how to write query to avoid this case in sql server.
step4 : Here I need to found morethan 30days records and need to insert targeemptable.
when I compare targetemp table checkvalues 2 related sdate with emp table checkvalue 5
Here I tried like below :
insert into targetemp
select * from empo join targetemp t on o.empid=t.empid and o.deptno=t.deptno and o.arel in('5') and t.arel in('2') and o.sdate<=t.sdate
and datediff(dd,o.sdate,t.sdate)>30 and t.sdate<>
(
select max(t.sdate) from empo join targetemp t on o.empid=t.empid and o.deptno=t.deptno and o.arel in('5') and t.arel in('2')
and o.sdate<=t.sdate and datediff(dd,o.sdate,t.sdate)>30
)
but above query not given expect result in the target table.
Here mianly we need to focus emp table one empid have checkvalues 2 is there thensame empid in the targetemp table need checkvalue 5 related value
fall between less than 30day if fall then we need to update target tablecheckvalues 5 change to 2 and name also changed
suppose emp table one empid have checkvalues 5 is there then same empid in the targetemp table need checkvalue 2 related value
fall between less than 30day if fall then we need to update target tablecheckvalues 2 related name chenged with emp table check5 value related name.
I done only emp table checkvalues 5 with targetemp table checkvalue 2.Here also I did not get correct result.
please tell me how to write query to achive this task in sql server.
