Answered by:
need to help a query in sql server 2008

Question
-
Hi I have doubt about sql server
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 (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, 'op', '20150915', 2, 10, 'har')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (4, 'jai', '20140501', 5, 10, 'kal')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'acbc', '20140520', 2, 20, 'hr')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'har', '20140626', 2, 20, 'ceo')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname)
VALUES (5, 'nan', '20140720', 5, 20, 'jan')
INSERT INTO emp (EmpId, name, Sdate, checkvalue, deptno, deptname) VALUES
(11,'test','2015-09-18','2','10','Hr'),
(11,'tes','2015-09-13','2','10','ceo'),
(11,'hari','2015-09-14','2','10','Pm'),
(11,'balu','2015-09-05','5','10','cm'),
(11,'jai','2015-09-12','1','20','Hr'),
(11,'ab','1998-04-24','5','10','je'),
(11,'ran','2015-09-07','5','10','kn')
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.
Finaly Targeemp table datalook like below:
Empid |name |sdate |checkvalue|deptno |deptname
2 |hni |2015-09-15 | 2 |20 |Deo
3 |tai |2015-07-20 | 2 |20 |po
3 |jai |2015-09-10 | 3 |20 |ceo
3 |man |2015-09-16 | 5 |20 |hal
4 |op |2015-09-15 | 2 |10 |har
4 |jai |2014-05-01 | 5 |10 |kal
5 |nan |2014-06-26 | 2 |20 |ceo
5 |acbc |2014-05-20 | 2 |20 |hr
11 |ran |2015-09-18 | 2 |10 |Hr
11 |hari |2015-09-14 | 2 |10 |Pm
11 |tes |2015-09-13 | 2 |10 |ceo
11 |balu |2015-09-05 | 5 |10 |cm
11 |ab |1998-04-24 | 5 |10 |je
11 |jai |2015-09-12 | 1 |20 |Hr
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.
- Moved by Dave PatrickMVP Friday, October 30, 2015 6:35 AM
Friday, October 30, 2015 6:31 AM
Answers
-
I'd try them over here.
https://social.msdn.microsoft.com/Forums/sqlserver/en-us/home?category=sqlserver
Regards, Dave Patrick ....
Microsoft Certified Professional
Microsoft MVP [Windows]
Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights.- Proposed as answer by Mike Laughlin Friday, October 30, 2015 11:56 AM
- Marked as answer by Just Karl Sunday, November 8, 2015 8:04 PM
Friday, October 30, 2015 6:35 AM