Hi I have doubt about Sql server
table : Emp
Empid | name | Sdate | Flag | deptno | Deptname
1 |test | 2015-09-18 | 2 |10 |Hr
1 |tes | 2014-09-13 | 2 |10 |ceo
1 |hari | 2015-09-14 | 2 |10 |Pm
1 |balu | 2015-09-05 | 5 |10 |cm
1 |jai | 2015-09-12 | 1 |20 |hr
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 |tes |2015-09-20 | 3 |20 |hr
3 |man |2015-09-16 | 5 |20 |hal
4 |op |2015-09-12 | 2 |10 |har
4 |jai |2014-05-01 | 5 |10 |kal
based on above table I want output like below
Here change Flag values, but here actually changing names, because deptname remains from the row with value 2 >compare records based on empid+ deptno and the sdate of flag=2 and sdate of flag=5, sdate should be less than or equal to flag=2 sdate and the difference between the sdates should be less than 30 days. we need apply lesst than 30 days for flag 2 and 5 values only remain flag values(1,3) no need to check date conditon.
Empid | name | Sdate | Flag | deptno | deptname
1 | balu | 2015-09-18 | 2 |10 |Hr
1 |jai | 2015-09-12 | 1 |20 |Hr
2 |hni |2015-09-15 | 2 |20 |Deo
3 |jai |2015-09-10 | 3 |20 |ceo
3 |tes |2015-09-20 | 3 |20 |hr
3 |man |2015-09-16 | 5 |20 |Hal
4 |op |2015-09-12 | 2 |10 |har
I Tried like below :
SELECT x.EmpId ,
x.name ,
x.Sdate ,
x.Flag ,
x.deptno ,
x.deptname
FROM
(
SELECT e.EmpId ,
ISNULL(x.name,e.name) AS name ,
e.Sdate ,
e.Flag ,
e.deptno ,
e.deptname,
ROW_NUMBER() OVER (PARTITION BY e.EmpId, e.deptno, e.Flag ORDER BY e.Sdate DESC) AS rn
FROM
emptest e
LEFT JOIN
( SELECT *
FROM emptest e2
WHERE
EXISTS (SELECT * FROM emptest e4 WHERE e2.empid = e4.empid AND e2.deptno = e4.deptno AND e4.Flag = 2)
AND ABS(ISNULL(DATEDIFF(DAY, (SELECT TOP 1 e1.Sdate FROM emptest e1 WHERE e2.EmpId = e1.EmpId AND e2.deptno = e1.deptno AND e1.Flag = 2),
e2.Sdate),0)) < 30
) x
ON e.EmpId = x.EmpId AND e.deptno = x.deptno AND x.Flag = 5
WHERE
(
e.Flag <>5
or not exists
(SELECT 1 FROM emptest e3 WHERE e.EmpId = e3.EmpId AND e.deptno = e.deptno AND e3.Flag = 2))
) x
WHERE
x.rn = 1
above query not give expected result . please tell me how to write query to achive this task in sql server