Thursday, September 22, 2011

Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL

As of late I have been camping out at the SQL Developer Center's  Transact-SQL Forum and I have to say, I have learned a great deal from my fellow SQL Server MVPs. Very alike the Microsoft Dynamics GP MVPs, these folks are willing to resolve pretty much any T-SQL request that comes across the forum.

So long you follow some simple posting guides, like including your table definitions, providing some sample data and detailing your expected results, there's nothing these folks won't do for you.

I decided to put some of what I have learned to the test by creating a T-SQL query that would produce a Year-to-year Inventory Margin Report (in currency) by using the T-SQL PIVOT operator. My fellow MVP Mark Polino is very well versed with Microsoft Excel and PowerPivot and I thought this would be a way to demonstrate that you can still use T-SQL to resolve some very complex issues which otherwise would require the use of front-end tool or some back-end cubes.

The following query can be run against the Fabrikam test company and adjusted to meet your specific needs:

;WITH CTE AS (
 SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description], 
 COALESCE([2014], 0) AS Y2014, 
 COALESCE([2015], 0) AS Y2015,
 COALESCE([2016], 0) AS Y2016, 
 COALESCE([2017], 0) AS Y2017 
FROM (
 SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN 
 FROM CTE
) p 
PIVOT (
 SUM(MARGIN)
 FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
 ) AS pvt
ORDER BY ITEMNMBR;

The script uses a query encapsulated in a Common Table Expression (CTE) to produce a temporary result for the sales data by item, grouped over each year. However, this result would produce rows of records for each year, as shown below:

SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
 FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE) 
 WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0) 
 GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC

/* Results */

TRXYEAR ITEMNMBR ITEMDESC XTNDPRCE EXTDCOST MARGIN
2014 ACCS-CRD-12WH                   Phone Cord - 12' White                                                                                39.80000 13.16000 26.64000
2014 ACCS-RST-DXBK                   Shoulder Rest-Deluxe Black                                                                            29.85000 13.65000 16.20000
2014 ACCS-RST-DXWH                   Shoulder Rest - Deluxe White                                                                          39.80000 18.20000 21.60000
2014 ANSW-ATT-1000                   Attractive Answering System 1000                                                                      119.95000 59.29000 60.66000
2014 ANSW-PAN-1450                   Panache KX-T1450 answer                                                                               219.90000 100.50000 119.40000
2014 FAXX-CAN-9800                   Cantata FaxPhone 9800                                                                                 23999.50000 11970.00000 12029.50000
2014 FAXX-HLP-5433                   Hewlett Packard FAX-310                                                                               854.50000 395.10000 459.40000
2014 FAXX-SLK-0172                   Sleek UX-172 fax                                                                                      2699.90000 1349.00000 1350.90000
2014 HDWR-PNL-0001                   Control Panel                                                                                         609.95000 303.85000 306.10000
2014 HDWR-PRO-4862                   Processor 486/25MHz                                                                                   5999.95000 2998.15000 3001.80000
2014 PHON-ATT-53BL                   Cordless-Attractive 5352-Blue                                                                         1139.70000 561.30000 578.40000
2014 PHON-ATT-53WH                   Cordless-Attractive 5352-White                                                                        189.95000 92.59000 97.36000
2014 PHON-BUS-1250                   Handset,multi-line                                                                                    359.95000 165.85000 194.10000
2014 PHON-PAN-2315                   Panache KX-T231 wall                                                                                  119.90000 59.50000 60.40000
2014 WIRE-SCD-0001                   Single conductor wire                                                                                 8.75000 4.00000 4.75000
2016 100XLG                          Green Phone                                                                                           4136.55000 3829.50000 307.05000

But in order to produce the results we want, having a true year by year comparison, it is necessary to pivot the results of the above query. Here's where the PIVOT operator comes into play, as shown in the initial query. This is the output produced:

Item Number       Item Description      Y2014  Y2015  Y2016  Y2017
100XLG            Green Phone                                            0.00000 0.00000  307.05000 40.05000
3-B3813A          Keyboard                                               0.00000 0.00000  0.00000  40.00000
3-C2924A          SCSI Cable, 2.5m. 68-pin HI-Density                    0.00000 0.00000  0.00000  148.50000
3-C2924A          T0101 - SCSI Cable, 2.5m. 68-pin HI-Density            0.00000 0.00000  0.00000  0.00000
3-C2924A          T0102 - SCSI Cable, 2.5m. 68-pin HI-Density            0.00000 0.00000  0.00000  0.00000
3-D2657A          T0101 - DB 15 Male Adapter                             0.00000 0.00000  0.00000  0.00000
3-D2657A          T0102 - DB 15 Male Adapter                             0.00000 0.00000  0.00000  0.00000
3-D2659A          T0101 - DB 25 Female Adapter                           0.00000 0.00000  0.00000  0.00000
3-D2659A          T0102 - DB 25 Female Adapter                           0.00000 0.00000  0.00000  0.00000
3-E4471A          HP Extractor Fan, 200-240V                             0.00000 0.00000  0.00000  134.00000
3-E4592A          SurgeArrest Plus                                       0.00000 0.00000  0.00000  180.00000
3-E4592A          T0101 - SurgeArrest Plus                               0.00000 0.00000  0.00000  0.00000
3-E4592A          T0106 - SurgeArrest Plus                               0.00000 0.00000  0.00000  0.00000
3-J2094A          HP-PB 16 Channel RS-232C Modem Conn MUX                0.00000 0.00000  0.00000  473.00000
4-A3666A          4.2GB LP Disk Drive                                    0.00000 0.00000  0.00000  350.00000
5-FEE             Per Call Fee                                           0.00000 0.00000  0.00000  0.00000
5-FEE             T0101 - Per Call Fee                                   0.00000 0.00000  0.00000  0.00000
5-FEE             T0102 - Per Call Fee                                   0.00000 0.00000  0.00000  0.00000
5-OVTLABOR        T0101 - Overtime service labor                         0.00000 0.00000  0.00000  0.00000
5-OVTLABOR        T0102 - Overtime service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0101 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0102 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-STDLABOR        T0106 - Standard service labor                         0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0101 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0102 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
5-TVLLABOR        T0106 - Travel Labor                                   0.00000 0.00000  0.00000  0.00000
ACCS-CRD-12WH     Phone Cord - 12' White                                 26.64000 0.00000  199.80000 226.44000
ACCS-CRD-25BK     Phone Cord - 25' Black                                 0.00000 0.00000  69.85000 83.82000
ACCS-HDS-1EAR     Headset-Single Ear                                     0.00000 0.00000  1034.00000 813.35000
ACCS-HDS-2EAR     Headset - Dual Ear                                     0.00000 0.00000  0.00000  527.67000
ACCS-RST-DXBK     Shoulder Rest-Deluxe Black                             16.20000 0.00000  226.80000 226.80000
ACCS-RST-DXWH     Shoulder Rest - Deluxe White                           21.60000 0.00000  163.20000 205.20000
ANSW-ATT-1000     Attractive Answering System 1000                       60.66000 0.00000  242.64000 303.30000
ANSW-PAN-1450     Panache KX-T1450 answer                                119.40000 0.00000  1194.00000 1134.30000
ANSW-PAN-2460     Panache KX-T2460 answer                                0.00000 0.00000  149.60000 169.60000
FAXX-CAN-9800     Cantata FaxPhone 9800                                  12029.50000 0.00000  66987.41000 51129.85000
FAXX-HLP-5433     Hewlett Packard FAX-310                                459.40000 0.00000  0.00000  0.00000
FAXX-RIC-060E     Richelieu Fax 60E                                      0.00000 0.00000  2404.50000 2404.50000
FAXX-SLK-0172     Sleek UX-172 fax                                       1350.90000 0.00000  1350.90000 675.45000

Happy pivoting.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

2 comments:

CoreyC said...

This is definitely GREAT!! Do you know if it could be adapted to have dynamic columns so that it can include any year returned by the CTE when you perform the pivot?

In most cases, I wouldn't want to hard code the columns that could vary (i.e. Years).

Thanks for this valuable knowledge either way!

Mariano Gomez said...

Corey,
Thanks for your question.. this would be a really good follow up post to this article!

MG.-
Mariano Gomez, MVP