Solved

Refining a pivot table Monthly to Quarterly

Posted on 2013-10-30
9
321 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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