none
TSQL Help SQL Server 2008 R2

    Question

  • Hi,

    I'm trying to figure out how to write the TSQL that produces the results in the yellow column of the attached picture.  I basically need to translate the formula in column H into TSQL. I've spent all day on this and got no where!

    Any help would greatly be appreciated.

    bruce d. orr

    Wednesday, January 15, 2014 11:39 PM

Answers

  • LAG is not an option in SQL Server 2008 R2. IT works only in SQL Server 2012.

    Please try the below(Not tested).It looks like there is some issue in your formula. So please change the formula as required to find F value.

    create Table T1(col1 int, c int, d int)
    
    Insert into T1 Select 2000,206044,240508
    Insert into T1 Select 2001,216384,739025
    Insert into T1 Select 2002,171825,862707
    
    ;with cte
    as
    (
    	Select *,c+d F From T1 where col1=2000
    	Union ALL
    	Select A.col1,A.c,A.d,A.d+A.c-B.F From T1 A
    	    Inner join cte B on A.col1-1 = B.col1  
    )
    Select * From cte
    
    Drop table T1

    • Marked as answer by orrlyfe Thursday, January 16, 2014 4:26 PM
    Thursday, January 16, 2014 12:24 AM

All replies

  • Hi,

    Use Lag with Over by clause to get it done. I am working on this query after completion will post.

    Here is link which will help u and you can give try. I can see ACCT_Year is in order so it can be done.

    http://technet.microsoft.com/en-us/library/hh231256.aspx


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, January 16, 2014 12:02 AM
  • LAG is not an option in SQL Server 2008 R2. IT works only in SQL Server 2012.

    Please try the below(Not tested).It looks like there is some issue in your formula. So please change the formula as required to find F value.

    create Table T1(col1 int, c int, d int)
    
    Insert into T1 Select 2000,206044,240508
    Insert into T1 Select 2001,216384,739025
    Insert into T1 Select 2002,171825,862707
    
    ;with cte
    as
    (
    	Select *,c+d F From T1 where col1=2000
    	Union ALL
    	Select A.col1,A.c,A.d,A.d+A.c-B.F From T1 A
    	    Inner join cte B on A.col1-1 = B.col1  
    )
    Select * From cte
    
    Drop table T1

    • Marked as answer by orrlyfe Thursday, January 16, 2014 4:26 PM
    Thursday, January 16, 2014 12:24 AM
  • Here is your final Query:


    SELECT 
    C_YEAR,INCOM,NET_INCOME,P_INCOM,P_NET_INCOME,INT_RESULT,SUM(INT_RESULT) OVER(ORDER BY C_YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FINAL_RESULT
    FROM 
    (SELECT
    C_YEAR,INCOM,NET_INCOME,P_INCOM,P_NET_INCOME,(INCOM-NET_INCOME-P_INCOM) AS INT_RESULT
    FROM 
    (
    SELECT C_YEAR,INCOM,NET_INCOME, 
    LAG (INCOM, 1, 0) OVER (ORDER BY C_YEAR) AS P_INCOM,
    LAG (NET_INCOME, 1, 0) OVER (ORDER BY C_YEAR) AS P_NET_INCOME
    FROM 
    (
    SELECT 1999 AS C_YEAR, 206044.86 AS INCOM, -240508.00 NET_INCOME
    UNION ALL
    SELECT 2000 AS C_YEAR, -216384.90 AS INCOM, -739025.53 NET_INCOME
    UNION ALL
    SELECT 2001 AS C_YEAR, -171825.98 AS INCOM, 862707.55 NET_INCOME
    UNION ALL
    SELECT 2002 AS C_YEAR, -1907770.50 AS INCOM, -1624701.57 NET_INCOME
    ) A) B) C


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, January 16, 2014 12:51 AM
  • Hi Shiven,

    I can't use Lag since on 2008.

    Thanks and let me know if you have any other ideas.

    Bruce


    bruce d. orr

    Thursday, January 16, 2014 4:04 PM
  • Hi Latheesh,

    It looks like we are very close.  The only thing I'm not seeing is how to calculate the previous year's net income.

    Any idea on this?

    Bruce


    bruce d. orr

    Thursday, January 16, 2014 4:05 PM
  • Hi Lateesh,

    You pointed me in the right direction.  I just had to add another calc to grab the prior month.

    Thanks so much, you ROCK!!!


    bruce d. orr

    Thursday, January 16, 2014 4:26 PM