Hi I have question in sql server.
source table: emp
EmpId |name |Sdate |checkvalue|deptno |deptname
1 |te |2015-09-18 | 2 |10 |Hr
1 |har |2015-09-14 | 5 |10 |Pm
1 |ts |2015-08-13 | 2 |10 |ceo
1 |bu |2015-08-14 | 5 |10 |cm
1 |jai |2013-04-21 | 5 |10 |pm
2 |kali |2015-09-15 | 2 |20 |Deo
2 |hni |2015-09-04 | 5 |20 |br
3 |jai |2015-09-10 | 3 |20 |ceo
3 |man |2015-09-16 | 5 |20 |hal
3 |tai |2015-07-20 | 2 |20 |po
4 |han |2013-05-08 | 2 |10 |kal
Target table : target emp:
testemp:
empId |name |Sdate |checkvalue|deptno |deptname
1 |te |2015-09-18 | 2 |10 |Hr
1 |ts |2015-08-15 | 2 |10 |ceo
2 |kali |2015-09-15 | 2 |20 |Deo
3 |tai |2015-07-20 | 2 |20 |po
4 |jai |2014-05-01 | 5 |10 |kal
here mainly focus checkvalue 2 with targetemp checkvalues5 and targetemp table checkvalues 2 with source side check values 5 :
1)checkvalue=5's sdate is less than or equal to checkvalue=2's sdate and
the difference between the dates should be less than 30days
2)if checkvalue=2 already exists in the target emp and new row is
checkvalues=5 then updated the follwing column checkvalue=2 from the
checkvalues=5(updated on the target table)
name column we need to update.
3)if checkvalues=5 already exists (on the target emp) and new row
is checkvalues=2 then update the following column on the existing checkvalues=5
(updated the targetemP table)
need to updated deptname column in the target emp table. if the condition not matched with target 2 values then need to insert 5 values in the target
similarly target 5 not matched condition then need to insert 2 values in the target.
based on above condition I want output in the targetemp table:
empId |name |Sdate |checkvalue|deptno|deptname
1 |har |2015-09-18 | 2 |10 |Hr ----updated
1 |bu |2015-08-15 | 2 |10 |ceo ------updated
1 |jai |2013-04-21 | 5 |10 |pm ------inserted
2 |hni |2015-09-15 | 2 |20 |Deo-------updated
3 |tai |2015-07-20 | 2 |20 |po
3 |man |2015-09-16 | 5 |20 |hal-----inserted
4 |jai |2014-05-01 | 5 |10 |kal
4 |han |2013-05-08 | 2 |10 |kal----inserted
5 |ope |2015-02-10 | 2 |10 |te -----updated
5 |b |2015-02-09 | 2 |10 |t
I tried for update statement like below:
update targetemp
set name = o.name
from emp o
join targetemp t on o.empid = t.empid
and o.deptno = t.deptno
and o.checkvalue in ('5')
and t.checkvalue 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.checkvalue in ('5')
and t.checkvalue in ('2')
and o.sdate <= t.sdate
and datediff(dd, o.sdate, t.sdate) <= 30)
but it did not given expect result.please tell me how to write query achive this task in sql server .