October 31, 2015 at 12:50 am
Hi I have question in sql server.
source table: emp
create Table emp (EmpId INT, name NVARCHAR(10), Sdate DATE, checkvalue INT, deptno INT, deptname NVARCHAR(10))
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname) VALUES
(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')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (2, 'kali', '20150915', 2, 20, 'Deo')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (2, 'hni', '20150904', 5, 20, 'br')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'jai', '20150910', 3, 20, 'ceo')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'man', '20150916', 5, 20, 'hal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'tai', '20150720', 2, 20, 'po')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (4, 'han', '20130208', 2, 10, 'kal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'ope', '20150208', 5, 10, 'po')
Target table : target emp:
create Table testemp (empId INT, name NVARCHAR(10), Sdate DATE, checkvalue INT, deptno INT, deptname NVARCHAR(10))
INSERT INTO testemp (empId, name, Sdate, checkvalue, deptno, deptname) VALUES
(1,'te','2015-09-18','2','10','Hr'),
(1,'ts','2015-08-15','2','10','ceo')
INSERT INTO testemp (empId, name, Sdate, checkvalue, deptno, deptname)
VALUES (2, 'kali', '20150915', 2, 20, 'Deo')
INSERT INTO testemp (empId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'tai', '20150720', 2, 20, 'po')
INSERT INTO testemp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (4, 'jai', '20140501', 5, 10, 'kal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'a', '20150210', 2, 10, 'te')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'b', '20150209', 2, 10, 't')
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 .
October 31, 2015 at 8:08 am
balukumar (10/31/2015)
Hi I have question in sql server.source table: emp
create Table emp (EmpId INT, name NVARCHAR(10), Sdate DATE, checkvalue INT, deptno INT, deptname NVARCHAR(10))
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname) VALUES
(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')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (2, 'kali', '20150915', 2, 20, 'Deo')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (2, 'hni', '20150904', 5, 20, 'br')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'jai', '20150910', 3, 20, 'ceo')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'man', '20150916', 5, 20, 'hal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'tai', '20150720', 2, 20, 'po')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (4, 'han', '20130208', 2, 10, 'kal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'ope', '20150208', 5, 10, 'po')
Target table : target emp:
create Table testemp (empId INT, name NVARCHAR(10), Sdate DATE, checkvalue INT, deptno INT, deptname NVARCHAR(10))
INSERT INTO testemp (empId, name, Sdate, checkvalue, deptno, deptname) VALUES
(1,'te','2015-09-18','2','10','Hr'),
(1,'ts','2015-08-15','2','10','ceo')
INSERT INTO testemp (empId, name, Sdate, checkvalue, deptno, deptname)
VALUES (2, 'kali', '20150915', 2, 20, 'Deo')
INSERT INTO testemp (empId, name, Sdate, checkvalue, deptno, deptname)
VALUES (3, 'tai', '20150720', 2, 20, 'po')
INSERT INTO testemp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (4, 'jai', '20140501', 5, 10, 'kal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'a', '20150210', 2, 10, 'te')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'b', '20150209', 2, 10, 't')
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 .
Quick question, is this homework? It sure looks like it.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply