SQLTeam.com | Weblogs | Forums

Need to Help query in sql server

sql2008
sql2012

#1

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


Help query in sql server
#2

It look like a sequel of this, this and this.