CODEWITHSUNDEEP
sqlsql-server
Coded Tunes
Coded Tunes

Reputation: 21

How to select columns from different rows in SQL Server

enter image description here

Table above shows a team's performance for each month in a given year

Given the table above, my task is to compute 1-year and 3-year performance knowing the month-and-year(ex. Feb 2016). 1-year or 3-year performance is calculated by getting the product of the previous 12 months or 36 months, respectively, beginning from the given month-and-year.

For example: if date is Sep 2016,

1 year performance = (2015 oct)(2015 nov)(2015 dec)(2016 jan)(2016 feb)(2016 mar)(2016 apr)(2016 may)(2016 jun)(2016 jul)(2016 aug)*(2016 sep)

I would like a query that computes 1-year and 3-year performance for any month and year provided.

Any assistance is appreciated.

Upvotes: 2

Views: 478

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

You can unpivot the data to make the manipulation easier. Probably your original data itself you should stored for each month/year in different rows

To calculate running multiplication you can use EXP and LOG function. Referred from this answer.

Mutiplication aggregate operator in SQL

DECLARE @input_date  DATE = '2016-02-01',
        @no_of_years INT = 1 

;with cte as
(select * from (values 
(2013  , -1 ,  3 ,  4 , -10 , 4 ,  6  , 13, -3,   5   , 3  ,    8   ,  -6   ),
(2014  ,  3 ,  2 , -5 ,  4  , 1 ,  7  , 8 , -8,   11  , 9  ,   -1   ,   4    ),
(2015  ,  2 ,  4 , -3 ,  4  ,-8 ,  2  , 1 ,  9,   3   , 4  ,   -6   ,   9    ),
(2016  ,  5 ,  4 ,  2 ,  6  , 8 ,  9  , 2 , -4,  -3   ,NULL,   NULL ,  NULL ))
tc(year, jan, feb, mar ,apr, may ,jun, jul ,aug ,sep, oct, nov, dec)
)
SELECT dates,
       CASE
         WHEN Min(Abs(VALUE))OVER(ORDER BY dates) = 0 THEN 0
         ELSE Exp(Sum(Log(Abs(NULLIF(VALUE, 0))))
                    OVER(ORDER BY dates)) 
              * Round(0.5 - Count(NULLIF(Sign(Sign(VALUE) + 0.5), 1))
                              OVER(ORDER BY dates)%2, 0)
       END
FROM   cte 

        CROSS apply(VALUES (jan,Datefromparts(year, 1, 1)),
                            (feb,Datefromparts(year, 2, 1)),
                            (mar,Datefromparts(year, 3, 1)),
                            (apr,Datefromparts(year, 4, 1)),
                            (may,Datefromparts(year, 5, 1)),
                            (jun,Datefromparts(year, 6, 1)),
                            (jul,Datefromparts(year, 7, 1)),
                            (aug,Datefromparts(year, 8, 1)),
                            (sep,Datefromparts(year, 9, 1)),
                            (oct,Datefromparts(year, 10, 1)),
                            (nov,Datefromparts(year, 11, 1)),
                            (dec,Datefromparts(year, 12, 1))) tc (VALUE, dates)
WHERE  dates > Dateadd(mm, -12 * @no_of_years, @input_date)
        AND dates <= @input_date

Upvotes: 1

Related Questions