Solved

Refining a pivot table Monthly to Quarterly

Posted on 2013-10-30
9
310 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
  • 6
  • 2
9 Comments
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
Comment Utility
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 25

Accepted Solution

by:
Shaun Kline earned 250 total points
Comment Utility
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
Comment Utility
I'm getting this error

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

Author Comment

by:acardullo
Comment Utility
btw. I didn't mention it but the column "period" is a float.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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
Comment Utility
So I need
Custnum    Custname          2012Q1     2012Q2    2012Q3    2012Q4    2013Q1    2013Q2  etc

Thanks
0
 

Author Comment

by:acardullo
Comment Utility
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
Comment Utility
ugh.  all I had to do is drop the period column and it worked fine.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now