Solved

SQL Query - Sales Report Pivot by Month and Year

Posted on 2014-12-21
1
30 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
1 Comment
 
LVL 33

Accepted Solution

by:
ste5an earned 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

808 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