Solved

SQL Query - Sales Report Pivot by Month and Year

Posted on 2014-12-21
1
27 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 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

21 Experts available now in Live!

Get 1:1 Help Now