Solved

Refining a pivot table Monthly to Quarterly

Posted on 2013-10-30
9
326 Views
Last Modified: 2013-10-30
Hello,
I have the following query:

select *
  from
  (
      SELECT
       [Custnum]
      ,[Custname]
      ,period,amount
     
      ,[Inventory code]

                                    
       FROM [VisionTools].[dbo].[SupplySalesEAUTOOMD]
       ) x
  PIVOT (SUM(Amount) FOR PERIOD IN ([201201],[201202],[201203],[201204]
                                                      ,[201205],[201206],[201207],[201208],[201209],[201210],[201211],[201212],[201301]
                                                      ,[201302],[201303],[201304],[201305],[201306],[201307],[201308],[201309],[201310],[201311],[201312])) P
      WHERE  [INVENTORY CODE] LIKE 'SUPINK'
      ORDER BY [Custname]

and partial result is seen in attachment.
What I need is to sum and group the months into quarterly columns. not month by month .

If you could point me in the right direction.

THank you
sqlsupply.PNG
0
Comment
Question by:acardullo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
ID: 39611565
assuming you follow the January - December year you need to do two things:

select *
  from
  (
      SELECT
       [Custnum]
      ,[Custname]
      ,period
      ,amount      
      ,[Inventory code]
      --create case statement for quarter
      ,case when month(period) between 1 and 3 then 1
              when month(period) between 4 and 6 then 2
              when month(period) between 7 and 9 then 3
              when month(period) between 10 and 12 then 4
        EMD Quarter

                                   
       FROM [VisionTools].[dbo].[SupplySalesEAUTOOMD]
       ) x
--update pivot.
PIVOT (SUM(Amount) FOR Quarter IN (1,2,3,4) P
      WHERE  [INVENTORY CODE] LIKE 'SUPINK'
      ORDER BY [Custname]
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 250 total points
ID: 39611568
The only thing you would need to do is convert your Period column in a value that represents how you want to display your Quarters.

Something like:
LEFT(CAST([period] AS VARCHAR), 4) + 
CASE RIGHT(CAST([period] AS VARCHAR), 2)
    WHEN '01' THEN 'Q1'
    WHEN '02' THEN 'Q1'
    WHEN '03' THEN 'Q1'
    WHEN '04' THEN 'Q2'
    WHEN '05' THEN 'Q2'
    WHEN '06' THEN 'Q2'
    WHEN '07' THEN 'Q3'
    WHEN '08' THEN 'Q3'
    WHEN '09' THEN 'Q3'
    WHEN '10' THEN 'Q4'
    WHEN '11' THEN 'Q4'
    ELSE 'Q4' END As QuarterPeriod
 

Then change your PIVOT to be over QuarterPeriod instead of Period, and change the columns you want to see in your results (i.e. 2012Q1, 2012Q2, etc.)

Open in new window

0
 

Author Comment

by:acardullo
ID: 39611572
I'm getting this error

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'EMD'.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:acardullo
ID: 39611583
btw. I didn't mention it but the column "period" is a float.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39611591
There's a slight typo . . . "EMD" should be "END".  What does period represent?  You can always cast it as a datetime

eg:

case when month(cast (period as smalldatetime)) between 1 and 3 then 1
-- update the rest.
0
 

Author Comment

by:acardullo
ID: 39611618
I just did a right(period,2)  and that works

I didn't however mention that I still need separation between years.
We need these numbers to compare quarterly sales form year to year.
0
 

Author Comment

by:acardullo
ID: 39611661
So I need
Custnum    Custname          2012Q1     2012Q2    2012Q3    2012Q4    2013Q1    2013Q2  etc

Thanks
0
 

Author Comment

by:acardullo
ID: 39611737
So I did this  but it still doesn't group it right. See attached file for result
select *
  from
  (
      SELECT
       [Custnum]
     
      ,period
      ,amount      
      ,[Inventory code]
      --create case statement for quarter
      ,case when right(period,2) between 1 and 3 and Left(period,4) = '2012' then '2012-Q1'
              when right(period,2) between 4 and 6 and Left(period,4) = '2012' then '2012-Q2'
              when right(period,2) between 7 and 9 and Left(period,4) = '2012' then '2012-Q3'
              when right(period,2) between 10 and 12 and Left(period,4) = '2012' then '2012-Q4'
             
              when right(period,2) between 1 and 3 and Left(period,4) = '2013' then '2013-Q1'
              when right(period,2) between 4 and 6 and Left(period,4) = '2013' then '2013-Q2'
              when right(period,2) between 7 and 9 and Left(period,4) = '2013' then '2013-Q3'
              when right(period,2) between 10 and 12 and Left(period,4) = '2013' then '2013-Q4'
        END as [Quarter]

                                   
       FROM [VisionTools].[dbo].[SupplySalesEAUTOOMD]
       ) x
--update pivot.
PIVOT (SUM(Amount) FOR [Quarter] IN ([2012-Q1],[2012-Q2],[2012-Q3],[2012-Q4],[2013-Q1],[2013-Q2],[2013-Q3],[2013-Q4])) P
      WHERE  [INVENTORY CODE] = 'SUPINK' and [custnum] = 'DH4461'
supply1.PNG
0
 

Author Comment

by:acardullo
ID: 39611742
ugh.  all I had to do is drop the period column and it worked fine.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question