?
Solved

SQL Query - Sales Report Pivot by Month and Year

Posted on 2014-12-21
1
Medium Priority
?
34 Views
Last Modified: 2015-05-27
I have a query that works great, but now I want to pivot it by month/year.

My report looks like this, we are on a 4-4-5 calendar and we have ship dates coming from two tables.

Customer, PartNumber, Qty, ShipDate Unit Price

BOESEA 1234     1     1/1/14  $100.00
BOESEA 1234     2     2/1/14  $100.00
BOESEA 3234     6     2/15/14 $10.00
MHIPHX 5678    10   2/15/14 $20.00
MHIPHX 1234   10   3/15/14 $20.00

This is what I would like it to look like

Customer, PartNumber, Jan15TotalQty, Jan15TotalSales, Feb15TotalQty, Feb15TotalSales, March15TotalQty, March15TotalSales
BOESEA       1234                1                   $100.00
BOESEA       1234                                                                       2                   $200.00
BOESEA       3234                                                                       6                     $60.00
MHIPHX       5678                                                                      10                 $200.00
MHIPHX       1234                                                                                                                      10                        $200.00

Here is my query.

I know I have to pivot it, but I'm not sure which part.  TIA

SELECT C.NAME, CO.CUSTOMER_PO_REF, P.DESCRIPTION, P.PRODUCT_CODE, CO.STATUS, CO.ID, P.QTY_ON_HAND,
(ISNULL(CLD.ORDER_QTY,CL.ORDER_QTY) - ISNULL(CLD.SHIPPED_QTY,CL.TOTAL_SHIPPED_QTY)) * CL.UNIT_PRICE AS ExtendedPrice,
AP.ACCT_YEAR AS Year, AP.ACCT_PERIOD AS Month,
PS.PRIMARY_WHS_ID, PS.PRIMARY_LOC_ID,
ISNULL(CLD.DESIRED_SHIP_DATE,ISNULL(CL.DESIRED_SHIP_DATE,CO.DESIRED_SHIP_DATE)) AS DESIRED_SHIP_DATE,
CLD.DESIRED_SHIP_DATE AS DEL_SHIP_DATE,
ISNULL(CLD.ORDER_QTY,CL.ORDER_QTY) AS ORDER_QTY, ISNULL(CLD.SHIPPED_QTY,CL.TOTAL_SHIPPED_QTY) AS TOTAL_SHIPPED_QTY,
ISNULL(CLD.ORDER_QTY,CL.ORDER_QTY) - ISNULL(CLD.SHIPPED_QTY,CL.TOTAL_SHIPPED_QTY) AS QTY_REMAIN,
CL.UNIT_PRICE, CL.PART_ID,
CL.CUST_ORDER_ID+'/'+CAST(CL.LINE_NO AS VARCHAR)+CASE WHEN CLD.DEL_SCHED_LINE_NO IS NOT NULL THEN '.' + CAST(CLD.DEL_SCHED_LINE_NO AS VARCHAR) ELSE '' END AS ORDER_LN
FROM CUST_ORDER_LINE CL
JOIN CUSTOMER_ORDER CO ON CL.CUST_ORDER_ID = CO.ID AND CO.STATUS = 'R'
JOIN CUSTOMER C ON CO.CUSTOMER_ID = C.ID
LEFT JOIN PART P ON CL.PART_ID = P.ID
LEFT JOIN PART_SITE PS ON CL.SITE_ID = PS.SITE_ID AND CL.PART_ID = PS.PART_ID
LEFT JOIN CUST_LINE_DEL CLD ON CL.CUST_ORDER_ID = CLD.CUST_ORDER_ID AND CL.LINE_NO = CLD.CUST_ORDER_LINE_NO
JOIN ACCOUNT_PERIOD AP ON ISNULL(CLD.DESIRED_SHIP_DATE,ISNULL(CL.DESIRED_SHIP_DATE,CO.DESIRED_SHIP_DATE)) BETWEEN AP.BEGIN_DATE AND AP.END_DATE
WHERE (CO.STATUS = 'R')
AND ((AP.ACCT_YEAR*12)+AP.ACCT_PERIOD) BETWEEN ((@BEG_YR*12)+@BEG_PD) AND ((@END_YR*12)+@END_PD)
AND ISNULL(CLD.ORDER_QTY,CL.ORDER_QTY) - ISNULL(CLD.SHIPPED_QTY,CL.TOTAL_SHIPPED_QTY) > 0
AND CL.LINE_STATUS = 'A'

Faunna Bartlett
0
Comment
Question by:cadencedba
[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
1 Comment
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40511851
You should do this in you front-end, otherwise it gets really ugly:

 
WITH Data AS 
	( 
		SELECT 	Customer, 
			PartNumber, 
			Qty, 				
			[Unit Price],
			YEAR(ShipDate) * 100 + MONTH(ShipDate) AS YearMonth
		FROM 	YourQuery 
	)
	SELECT	Customer,
		PartNumber,
		SUM( CASE WHEN YearMonth = 201501 THEN Qty ELSE 0 ) AS TotalQty201501,
		SUM( CASE WHEN YearMonth = 201501 THEN [Unit Price] ELSE 0 ) AS TotalSales201501,
		SUM( CASE WHEN YearMonth = 201502 THEN Qty ELSE 0 ) AS TotalQty201502,
		SUM( CASE WHEN YearMonth = 201502 THEN [Unit Price] ELSE 0 ) AS TotalSales201502,
		SUM( CASE WHEN YearMonth = 201503 THEN Qty ELSE 0 ) AS TotalQty201503,
		SUM( CASE WHEN YearMonth = 201503 THEN [Unit Price] ELSE 0 ) AS TotalSales201503 
		-- and so on..
	FROM	Data
	GROUP BY Customer,
		PartNumber;

Open in new window

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

762 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