Post reply

using CTE how we can achive this. please help me

  • December 29, 2017 at 1:04 am

    #342184

    Declare @Input Table(EmpID VARCHAR(50),  from_value int, to_value int);
    Insert @Input(EmpID, from_value,to_value) Values
    ('E1',  1, 4),
    ('E1',  5,null),
    ('E1',  7, Null);

    Select * from @Input

    Need out put like

    EmpID    from_value    to_value
    E1            1            4
    E1            5            6
    E1            7            NULL

    Next from value of the employee is 7 so 7-1 =6. this 6 value will be the to_value of previous record

  • December 29, 2017 at 1:19 am

    #1973800

    vemula.narayanan - Friday, December 29, 2017 1:04 AM

    Declare @Input Table(EmpID VARCHAR(50),  from_value int, to_value int);
    Insert @Input(EmpID, from_value,to_value) Values
    ('E1',  1, 4),
    ('E1',  5,null),
    ('E1',  7, Null);

    Select * from @Input

    Need out put like

    EmpID    from_value    to_value
    E1            1            4
    E1            5            6
    E1            7            NULL

    Next from value of the employee is 7 so 7-1 =6. this 6 value will be the to_value of previous record

    Two suggestions, one for SQL Server 2008 and earlier, the other for 2012 and later
    😎

    Declare @Input Table(EmpID VARCHAR(50), from_value int, to_value int);
    Insert @Input(EmpID, from_value,to_value) Values
    ('E1', 1, 4),
    ('E1', 5,null),
    ('E1', 7, Null);

    --SQL SERVER 2008 AND EARLIER
    ;WITH BASE_DATA AS
    (
      SELECT
       ROW_NUMBER() OVER
        (
          PARTITION BY IP.EmpID
          ORDER BY  IP.from_value ASC
        ) AS EMP_RID
       ,IP.EmpID
       ,IP.from_value
       ,IP.to_value
      FROM  @Input IP
    )
    SELECT
      BD.EmpID
     ,BD.from_value
     ,ISNULL(BD.to_value,(B2.from_value - 1)) AS to_value
    FROM    BASE_DATA   BD
    LEFT OUTER JOIN BASE_DATA   B2
    ON     BD.EMP_RID = (B2.EMP_RID - 1)
    AND     BD.EmpID  = B2.EmpID;

    -- SQL SERVER 2012 AND LATER
    SELECT
      IP.EmpID
      ,IP.from_value
      ,ISNULL( IP.to_value
        ,LEAD(IP.from_value,1,NULL) OVER
          (
           PARTITION BY IP.EmpID
           ORDER BY  IP.from_value
          ) - 1
        ) AS to_value
    FROM  @Input IP;

  • December 29, 2017 at 1:26 am

    #1973801

    Thanks a lot Eirikur:)

  • December 29, 2017 at 3:41 am

    #1973810

    vemula.narayanan - Friday, December 29, 2017 1:26 AM

    Thanks a lot Eirikur:)

    You are welcome!
    😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply