Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Pivot Puzzle – V

The requirement is you have to write a SQL statements to achieve the desired result.

For details please check out the sample input and the expected output below-

Sample Inputs

Id SoldAt Amount
1 2014-01-05 15.00
2 2015-01-05 1.50
3 2015-05-05 2.00
4 2015-05-06 4.00
5 2015-08-07 8.00
6 2015-09-25 16.00
7 2016-02-05 12.00
8 2016-04-05 6.00
9 2016-04-08 18.00
10 2016-05-07 56.00
11 2016-05-07 16.00
12 2016-07-21 1232.00

Expected Output

Year Jan Feb Mar Q01 Apr May Jun Q02 Jul Aug Sep Q03 Oct Nov Dec Q04 YTD
2014 15.00 NULL NULL 15.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15.00
2015 1.50 NULL NULL 1.50 NULL 6.00 NULL 6.00 NULL 8.00 16.00 24.00 NULL NULL NULL NULL 31.50
2016 NULL 12.00 NULL 12.00 24.00 72.00 NULL 96.00 1232.00 NULL NULL 1232.00 NULL NULL NULL NULL 1340.00

Rules/Restrictions

  • The challenge is to do it with SQL statements.
  • CTEs, Temp Tables are not allowed.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

CREATE TABLE Sale 
(
	  Id INT
	, SaleOn DATE
	, Amount MONEY
)
GO

Insert Into Sale Values 
  (1, '20140105', $15)
, (2, '20150105', $1.5)
, (3, '20150505', $2)
, (4, '20150506', $4)
, (5, '20150807', $8)
, (6, '20150925', $16)
, (7, '20160205', $12)
, (8, '20160405', $6)
, (9, '20160408', $18)
, (10, '20160507', $56)
, (11, '20160507', $16)
, (12, '20160721', $1232);

GO

--

Solution 1


--

/*
** SOLUTION
*/

SELECT * FROM
(
       SELECT [Year], Amount, Period FROM
       (
             SELECT Year(SoldAt) [Year]
             ,Amount
             ,CAST(FORMAT(SoldAt,'MMM') AS VARCHAR(3)) M            
             ,'Q'+RIGHT('0'+CONVERT(VARCHAR(2), DATEPART(q,SoldAt)), 2) Q
             ,'YTD' Y 
           FROM Sale
       ) t1
       UNPIVOT (PERIOD FOR PeriodType In (M, Q, Y)) u
) t2
PIVOT (Sum(Amount) FOR PERIOD IN (Jan, Feb, Mar, Q01, Apr, May, Jun, Q02, Jul, Aug, Sep, Q03, Oct, Nov, Dec, Q04, YTD))p


--

Solution 2


--

/*
** SOLUTION
*/


select  [year] 
,sum(case [Month] when 'January' Then amount else null end) January
,sum(case [Month] when 'February' Then amount else null end) February
,sum(case [Month] when 'March' Then amount else null end) March
,sum(case when [Month] = 'March' OR [Month] = 'February' OR [Month] = 'January' Then amount else null end) Q01
,sum(case [Month] when 'April' Then amount else null end) April
,sum(case [Month] when 'May' Then amount else null end) May
,sum(case [Month] when 'June' Then amount else null end) June
,sum(case when [Month] = 'April' OR [Month] = 'May' OR [Month] = 'June' Then amount else null end) Q02
,sum(case [Month] when 'July' Then amount else null end) July
,sum(case [Month] when 'August' Then amount else null end) August
,sum(case [Month] when 'September' Then amount else null end) September
,sum(case when [Month] = 'July' OR [Month] = 'August' OR [Month] = 'September' Then amount else null end) Q03
,sum(case [Month] when 'October' Then amount else null end) October
,sum(case [Month] when 'November' Then amount else null end) November
,sum(case [Month] when 'December' Then amount else null end) December
,sum(case when [Month] = 'October' OR [Month] = 'November' OR [Month] = 'December' Then amount else null end) Q04
,sum(amount) YTD
from (
select year(soldat) [Year], DATENAME ( month , soldat )  [Month], convert(float,sum(amount)) Amount from sale group by year(soldat)
, DATENAME ( month , soldat )
) A group by [year]


--

Solution 3


--

/*
** SOLUTION
*/


SELECT 
        A.[YEAR] [YEAR],
       CAST(JAN AS FLOAT) Jan, 
        CAST(FEB AS FLOAT) Feb, 
        CAST(MAR AS FLOAT) Mar,
       CAST(Q1 AS FLOAT)  Q01, 
        CAST(APR AS FLOAT) Apr, 
        CAST(MAY AS FLOAT) May, 
        CAST(JUN AS FLOAT) Jun,
       CAST(Q2 AS FLOAT)  Q02, 
        CAST(JUL AS FLOAT) Jul, 
        CAST(AUG AS FLOAT) Aug, 
        CAST(SEP AS FLOAT) Sep,
       CAST(Q3 AS FLOAT)  Q03, 
        CAST(OCT AS FLOAT) Oct, 
        CAST(NOV AS FLOAT) Nov, 
        CAST([DEC] AS FLOAT) [Dec], 
        CAST(Q4 AS FLOAT)  Q04, 
        CAST(C.YTD AS FLOAT) YTD
FROM
(
       SELECT 
              [YEAR] [YEAR], JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC]
       FROM
       (
              SELECT 
                     YEAR(SOLDAT) AS [YEAR], LEFT(DATENAME(MONTH,SOLDAT),3) AS [MONTH], AMOUNT 
              FROM 
                     SALE

       ) 
       AS SOURCETABLE
       PIVOT
       (
              SUM(AMOUNT) FOR [MONTH] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC])
       )
       AS PIVOTTABLE
) A

INNER JOIN 
(
       SELECT 
              [YEAR] [Year], [1] Q1,[2] Q2,[3] Q3,[4] Q4
       FROM
       (
              SELECT 
                     YEAR(SOLDAT) AS YEAR, DATEPART(QUARTER, SOLDAT) AS QUARTERNAME, SUM(AMOUNT) AS AMOUNT
              FROM 
                     SALE
              GROUP BY 
                     DATEPART(QUARTER, SOLDAT), YEAR(SOLDAT)      
       ) 
       AS 
              SOURCETABLE
       PIVOT
       (
              SUM(AMOUNT) FOR QUARTERNAME IN ([1],[2],[3],[4])
       ) 
       AS 
              PIVOTTABLE

) B ON A.YEAR = B.YEAR
INNER JOIN
(
       SELECT 
              SUM(AMOUNT) AS YTD,YEAR(SOLDAT) AS [YEAR]
       FROM 
              SALE
       GROUP BY 
              YEAR(SOLDAT)
) C ON B.YEAR = C.YEAR



--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements