locked
Sql Query to calculate current row of Val column divide by previous row of val column ... RRS feed

  • Question

  • User-1068576637 posted

    Example : I am expected Result column.. Any one can help.

    Name Date Val Result
    A 12/27/2016 2633.8  Not to Considerd since No Previous Val For the Name 'A'
    A 12/28/2016 2633.15 =Current Val / Previous Val
    A 12/29/2016 2651.45 =Current Val / Previous Val
    A 12/30/2016 2632.2 =Current Val / Previous Val
    B 12/27/2016 809.75  Not to Considerd since No Previous Val For the Name 'B'
    B 12/28/2016 820.2 =Current Val / Previous Val
    B 12/29/2016 833.35 =Current Val / Previous Val
    B 12/30/2016 842.2 =Current Val / Previous Val
    C 12/27/2016 149.9  Not to Considerd since No Previous Val For the Name 'C'
    C 12/28/2016 150.15 =Current Val / Previous Val
    C 12/29/2016 151.5 =Current Val / Previous Val
    C 12/30/2016 153.4 =Current Val / Previous Val
    D 12/27/2016 137.22  Not to Considerd since No Previous Val For the Name 'D'
    D 12/28/2016 135.9 =Current Val / Previous Val
    D 12/29/2016 136.77 =Current Val / Previous Val
    D 12/30/2016 137.51 =Current Val / Previous Val
    E 12/27/2016 299.6  Not to Considerd since No Previous Val For the Name 'E'
    E 12/28/2016 299.5 =Current Val / Previous Val
    E 12/29/2016 303 =Current Val / Previous Val
    E 12/30/2016 305.65 =Current Val / Previous Val
    Wednesday, November 29, 2017 9:16 AM

Answers

  • User77042963 posted
    select *, Val/lag(Val) Over(Partition by Name Order by Date) as yourResult
      from test1
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 29, 2017 4:18 PM
  • User991499041 posted

    Hi Mohammed,

    declare @tb table
    (Name nvarchar(10),
    Date nvarchar(50),
    Val decimal(8,2))
    
    insert into @tb values
    ('A','12/27/2016',2633.8),
    ('A','12/28/2016',2633.15),	
    ('A','12/29/2016',2651.45),	
    ('A','12/30/2016',2632.2),	
    ('B','12/27/2016',809.75),	 
    ('B','12/28/2016',820.2),	
    ('B','12/29/2016',833.35),
    ('B','12/30/2016',842.2),	
    ('C','12/27/2016',149.9),	
    ('C','12/28/2016',150.15),	
    ('C','12/29/2016',151.5),	
    ('C','12/30/2016',153.4),	
    ('D','12/27/2016',137.22),	
    ('D','12/28/2016',135.9),	
    ('D','12/29/2016',136.77),	
    ('D','12/30/2016',137.51),	
    ('E','12/27/2016',299.6),	
    ('E','12/28/2016',299.5),	
    ('E','12/29/2016',303),	
    ('E','12/30/2016',305.65)	
    
    --New Approaches :
    --In this approach, you do not need to do self join and make it complicated. 
    --You just need to use LAG function and it will calculate the previous result row for you automatically.
    --This approach can be used in SQL Server 2012 and above.
    select *, Val/lag(Val) Over(Partition by Name Order by Date) as Result
    from @tb
    
    --Old Approaches :
    --Given below is the old approach that we generally develop using self join.
    --This approach you can use in any version of SQL Server.
    
    ;with cte as
    (
    select Name,Date,Val,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name ASC) AS Row 
    from @tb 
    )
    select a.Name,a.Date,a.Val as [Current Val],b.Val as [Previous Val],a.Val/b.Val as Result from cte as a
    left join cte as b on a.Name=b.Name and a.Row=b.Row+1
    order by a.Name
    
    

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 30, 2017 2:54 AM
  • User77042963 posted
    ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date ASC) AS Row 

    You should Use your date time for yor order by clause. When you use Name and the value is the same since you are using Name for your partition by clause. You will not be able to control the sorting order at all. That is the reason why sometimes it works and some other times it may not.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 7, 2017 8:12 PM

All replies

  • User77042963 posted
    select *, Val/lag(Val) Over(Partition by Name Order by Date) as yourResult
      from test1
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 29, 2017 4:18 PM
  • User991499041 posted

    Hi Mohammed,

    declare @tb table
    (Name nvarchar(10),
    Date nvarchar(50),
    Val decimal(8,2))
    
    insert into @tb values
    ('A','12/27/2016',2633.8),
    ('A','12/28/2016',2633.15),	
    ('A','12/29/2016',2651.45),	
    ('A','12/30/2016',2632.2),	
    ('B','12/27/2016',809.75),	 
    ('B','12/28/2016',820.2),	
    ('B','12/29/2016',833.35),
    ('B','12/30/2016',842.2),	
    ('C','12/27/2016',149.9),	
    ('C','12/28/2016',150.15),	
    ('C','12/29/2016',151.5),	
    ('C','12/30/2016',153.4),	
    ('D','12/27/2016',137.22),	
    ('D','12/28/2016',135.9),	
    ('D','12/29/2016',136.77),	
    ('D','12/30/2016',137.51),	
    ('E','12/27/2016',299.6),	
    ('E','12/28/2016',299.5),	
    ('E','12/29/2016',303),	
    ('E','12/30/2016',305.65)	
    
    --New Approaches :
    --In this approach, you do not need to do self join and make it complicated. 
    --You just need to use LAG function and it will calculate the previous result row for you automatically.
    --This approach can be used in SQL Server 2012 and above.
    select *, Val/lag(Val) Over(Partition by Name Order by Date) as Result
    from @tb
    
    --Old Approaches :
    --Given below is the old approach that we generally develop using self join.
    --This approach you can use in any version of SQL Server.
    
    ;with cte as
    (
    select Name,Date,Val,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name ASC) AS Row 
    from @tb 
    )
    select a.Name,a.Date,a.Val as [Current Val],b.Val as [Previous Val],a.Val/b.Val as Result from cte as a
    left join cte as b on a.Name=b.Name and a.Row=b.Row+1
    order by a.Name
    
    

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 30, 2017 2:54 AM
  • User-1068576637 posted

    Great, Thanks for providing a solution. I got the result as i expected. Really thanks .  Excellent.......... 

    Thursday, November 30, 2017 4:15 AM
  • User-1068576637 posted

    Hi ,

    When i am inserting the result of records in temp table ,previous value is changing all time .

    while executing ,sometimes temp table shows  correct answer of previous value , sometimes not .. Why ?

    i have written insert query inside. refer below.

    i am using below method

    ;with cte as
    (
    select Name,Date,Val,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name ASC) AS Row 
    from @tb 
    )
    Insert into #TempTable(Name,PDate,CurPrice,PrevPrice,Result) select a.Name,a.Date,a.Val as [Current Val],b.Val as [Previous Val],a.Val/b.Val as Result from cte as a left join cte as b on a.Name=b.Name and a.Row=b.Row+1 order by a.Name

    Thursday, December 7, 2017 11:56 AM
  • User77042963 posted
    ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date ASC) AS Row 

    You should Use your date time for yor order by clause. When you use Name and the value is the same since you are using Name for your partition by clause. You will not be able to control the sorting order at all. That is the reason why sometimes it works and some other times it may not.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 7, 2017 8:12 PM
  • User-1068576637 posted

    Thanks , now it is fine.

    Friday, December 8, 2017 5:59 AM