none
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.






    Friday, October 30, 2015 6:31 AM

Answers