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:
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:
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:
Happy pivoting.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
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/
Comments
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!
Thanks for your question.. this would be a really good follow up post to this article!
MG.-
Mariano Gomez, MVP