CODEWITHSUNDEEP
sqlsql-servert-sql
Eclipse
Eclipse

Reputation: 309

selecting data for the first 12 months

This is a brain teaser and I'm not able to solve it. I have a table where the data looks like this:

Year    January February    March   April   May June    July    August  September   October November    December
2017    NULL    NULL    NULL    NULL    NULL    NULL    2259.30 1998.72 1790.73 1612.39 1445.99 1275.14
2018    1148.13 997.40  917.40  839.07  778.41  714.67  643.40  590.50  551.88  501.99  445.25  385.54
2019    354.34  315.85  295.80  271.16  252.00  231.18  208.58  190.06  172.44  155.82  138.32  118.72
2020    105.94  87.16   74.55   63.07   50.96   39.12   27.77   23.82   22.38   18.73   12.93   8.15
2021    7.76    6.97    6.82    6.35    6.38    6.39    6.42    6.41    6.38    6.37    5.62    5.70
2022    5.37    4.73    4.60    4.33    4.31    4.40    4.31    4.31    4.35    4.33    4.30    4.40
2023    4.14    3.75    3.66    3.48    3.42    3.50    3.47    3.42    3.50    3.47    3.42    3.50
2024    3.25    2.88    2.80    2.61    2.62    2.57    2.59    2.61    2.62    2.57    2.59    2.61
2025    2.50    2.23    2.23    2.16    2.13    2.20    2.16    2.18    2.18    2.15    2.14    2.22
2026    2.10    1.86    1.85    1.74    1.73    1.74    1.74    1.67    1.77    1.74    1.73    1.74
2027    1.62    1.40    1.40    1.32    1.34    1.24    1.29    1.27    1.37    1.26    1.30    1.30
2028    1.09    0.49    0.30    0.01    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

I would like to split this view into two. Would like to select the first 12 months and in this case, July of 2017 through June of 2018 and display it in the same way (current calendar view). Part 2: I'd like to select the rest of the data (except the first 12 months) and display in the same manner. There's another table where the distribution is much shorter. What I mean is, the current example has 10+ years, the other one is 4 years. I'd like to apply the distribution query on both of the tables.

Thanks.

Expected output 1

Year    January February    March   April   May June    July    August  September   October November    December
2017    NULL    NULL    NULL    NULL    NULL    NULL    2259.30 1998.72 1790.73 1612.39 1445.99 1275.14
2018    1148.13 997.40  917.40  839.07  778.41  714.67  NULL    NULL    NULL    NULL    NULL    NULL

Output 2:

Year    January February    March   April   May June    July    August  September   October November    December
2018    NULL    NULL    NULL    NULL    NULL    NULL    643.40  590.50  551.88  501.99  445.25  385.54
2019    354.34  315.85  295.80  271.16  252.00  231.18  208.58  190.06  172.44  155.82  138.32  118.72
2020    105.94  87.16   74.55   63.07   50.96   39.12   27.77   23.82   22.38   18.73   12.93   8.15
2021    7.76    6.97    6.82    6.35    6.38    6.39    6.42    6.41    6.38    6.37    5.62    5.70
2022    5.37    4.73    4.60    4.33    4.31    4.40    4.31    4.31    4.35    4.33    4.30    4.40
2023    4.14    3.75    3.66    3.48    3.42    3.50    3.47    3.42    3.50    3.47    3.42    3.50
2024    3.25    2.88    2.80    2.61    2.62    2.57    2.59    2.61    2.62    2.57    2.59    2.61
2025    2.50    2.23    2.23    2.16    2.13    2.20    2.16    2.18    2.18    2.15    2.14    2.22
2026    2.10    1.86    1.85    1.74    1.73    1.74    1.74    1.67    1.77    1.74    1.73    1.74
2027    1.62    1.40    1.40    1.32    1.34    1.24    1.29    1.27    1.37    1.26    1.30    1.30
2028    1.09    0.49    0.30    0.01    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

Upvotes: 1

Views: 58

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Another option is to unpivot a targeted subset and then pivot

Example

;with cte as (
        Select A.Year
              ,Mnth   = DateName(MONTH,B.Per)
              ,B.Per 
              ,B.Value
         From  YourTable A
         Cross Apply ( values (1 ,cast(str(Year)+'-01-01' as date),[January])
                             ,(2 ,cast(str(Year)+'-02-01' as date),[February])
                             ,(3 ,cast(str(Year)+'-03-01' as date),[March])
                             ,(4 ,cast(str(Year)+'-04-01' as date),[April])
                             ,(5 ,cast(str(Year)+'-05-01' as date),[May])
                             ,(6 ,cast(str(Year)+'-06-01' as date),[June])
                             ,(7 ,cast(str(Year)+'-07-01' as date),[July])
                             ,(8 ,cast(str(Year)+'-08-01' as date),[August])
                             ,(9 ,cast(str(Year)+'-09-01' as date),[September])
                             ,(10,cast(str(Year)+'-10-01' as date),[October])
                             ,(11,cast(str(Year)+'-11-01' as date),[November])
                             ,(12,cast(str(Year)+'-12-01' as date),[December])
                     ) B (Mnth,Per,Value)
) , cte2 as ( Select FirstPer = max(Per) from cte where Value is null and Year=(Select min(Year) from cte) )
Select *
 From (
        Select OutPut = case when Per > DateAdd(MONTH,12,FirstPer) then 2 else 1 end
               ,Year,Mnth,Value 
         From  cte 
         Cross Join cte2
      ) A
 Pivot (sum([Value]) For [Mnth] in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]) ) p

Returns

enter image description here

Upvotes: 1

Noxthron
Noxthron

Reputation: 492

For the 1st part are you looking for a query like this?

Select 
 case when Year = 2017 then null else January end as January,
 case when Year = 2017 then null else February end as February,
 case when Year = 2017 then null else March end as March,
 case when Year = 2017 then null else April end as April,
 case when Year = 2017 then null else May end as May,
 case when Year = 2017 then null else June end as June,
 case when Year = 2018 then null else July end as July,
 case when Year = 2018 then null else August end as August,
 case when Year = 2018 then null else September end as September,
 case when Year = 2018 then null else October end as October,
 case when Year = 2018 then null else November end as November,
 case when Year = 2018 then null else December end as December
from mytable
Where Year in (2017, 2018)

not the best solution probably but still may get the job done...

Upvotes: 0

Related Questions