Click here to Skip to main content
15,919,749 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have question in sql server.

source table: emp
VB
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:


VB
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:
SQL
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 .
Posted
Updated 31-Oct-15 6:25am
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900