Help query in sql server

Transact-SQL
1

Hi I have doubt about 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 achive this task in sql server .

2

Your first statement is really two, which is easy to see if you format it properly:

UPDATE targetemp
SET NAME = o.NAME

SELECT *
FROM empo
INNER 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
                  INNER 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
                  )

As written, it is not valid SQL. First correct your syntax errors. Do you mean:

UPDATE targetemp
SET NAME = o.NAME

FROM emp AS o
...

?

3

Hi can you please tell me I change bit changes in my question.please help a query to achieve this task in sql server. Thanks!

4

Please format your SQL using PoolSql.com and the formatting options on the the tool bar (higlight your code and hit the </> icon). Formatted, your query looks like this:

UPDATE targetemp
SET NAME = o.NAME
FROM emp o
INNER 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
        INNER 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
        )

This query looks like it would do part 2 of your original request. Is it doing that part correctly? Note that Part 3 will need a new query since it has different conditions