How to select columns from different rows in SQL Server

Question

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.

It might be a good idea, if possible, to change the entire structure of your table so that you just have a single date column and a single value column. Having separate columns for each month in a given year is very inefficient. Even just a year column, month column and value column would be a good idea. — ZLK, Oct 26 '16 at 03:47
@zlk that's the difficulty i'm having trying to have a query to transform the structure. Can you help with such a query. I'm having difficulty uploading an image of the result set. I hope it's viewable now. — Coded Tunes, Oct 26 '16 at 03:52
If you wanted to transform the data you could use an unpivot / cross apply. e.g. `SELECT year, umonth, uval FROM myTable CROSS APPLY (VALUES (1, JAN), (2, FEB), (3, MAR), (4, APR), (5, MAY), (6, JUN), (7, JUL), (8, AUG), (9, SEP), (10, OCT), (11, NOV), (12, DEC)) U(umonth, uval);` should give you the unpivoted values as well as the month numbers. — ZLK, Oct 26 '16 at 03:58
@zlk the cross apply worked perfectly but I'm still stuck with getting the product of previous 12 month beginning from sep 2016. I'd appreciate your help a lot. — Coded Tunes, Oct 26 '16 at 04:09
prdp's answer is probably what you're after. Or some modification of that. — ZLK, Oct 26 '16 at 04:18

score 1 · Answer 1 · edited May 23 '17 at 12:19

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

How to select columns from different rows in SQL Server

1 Answers1