Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query - Sales Report Pivot by Month and Year

Posted on 2014-12-21
1
Medium Priority
?
36 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

609 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