Solved

SQL Query - Sales Report Pivot by Month and Year

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

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

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

17 Experts available now in Live!

Get 1:1 Help Now