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.
here less than 30 days need to apply only flag 2 and 5 only.
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 :
DECLARE @emp1 Table (EmpId INT, name NVARCHAR(10), Sdate DATE, Flag INT, deptno INT, deptname NVARCHAR(10))
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (1, 'test', '20150918', 2, 10, 'Hr')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (1, 'tes', '20150913', 2, 10, 'ceo')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (1, 'hari', '20150914', 2, 10, 'Pm')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (1, 'balu', '20150905', 5, 10, 'cm')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (1, 'jai', '20150912', 1, 20, 'Hr')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (2, 'kali', '20150915', 2, 20, 'Deo')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (2, 'hni', '20150904', 5, 20, 'br')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (3, 'jai', '20150910', 3, 20, 'ceo')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (3, 'man', '20150916', 5, 20, 'hal')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (4, 'op', '20150915', 2, 10, 'har')
INSERT INTO @emp1 (EmpId, name, Sdate, Flag, deptno, deptname)
VALUES (4, 'jai', '20140501', 5, 10, 'kal')
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
@emp1 e
LEFT JOIN
( SELECT *
FROM @emp1 e2
WHERE
EXISTS (SELECT * FROM @emp1 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 @emp1 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 @emp1 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