Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL – Follow up!

Ok, of course my dear friend Corey in Louisville, Ohio had to challenge my post from yesterday and ask a question that is, frankly, a logical and natural progression of things – see Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL.

In yesterday’s post I showed a query that could create a year-to-year Inventory Margin report using T-SQL’s PIVOT operator. The query, while very useful, hardcodes the years you want to display in the pivoted columns list. An excerpt of the PIVOT operator and the pivoted columns list as follows:

.
.
.
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
) AS pvt

So naturally, Corey’s question was, “What if I want to add the pivoted columns list dynamically, instead of hardcoding them?“. Since all transactions have a date, it would make sense to add the list of years based on the document dates. The good news is this is possible. For this we would need to create a dynamic pivoted list using, well, dynamic SQL, as follows:

DECLARE @listCol NVARCHAR(MAX), @sqlstmt NVARCHAR(MAX);

SELECT @listCol = STUFF(
( SELECT DISTINCT '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
FROM SOP30200
ORDER BY '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
FOR XML PATH('')
),
1,
2,
'') + ']'

SET @sqlstmt =
N';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],' + @listCol + N'
FROM (
SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN
FROM CTE
) p
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN (' + @listCol + N')
) AS pvt
ORDER BY ITEMNMBR;';

EXEC sp_executesql @sqlstmt;

Couple observations…

We use the FOR XML clause with the PATH mode to build a list of elements and attributes based on the distinct year values stored in the document date (DOCDATE) column in our SOP30200 (SOP History) table, which we store in the @listcol variable length Unicode character data type (NVARCHAR).

Our CTE is now embedded in an NVARCHAR variable, which we call @sqlstmt, with just the right breaks to concatenate our pivoted column list variable, @listcol as part of the overall SQL statement character string that will be executed.

We finally use the sp_executesql system stored procedure to run our dynamic SQL query and produce the results below:

Item Number Item Description 2014 2015 2016 2017 2018
100XLG Green Phone NULL NULL 307.05000 40.05000 NULL
3-B3813A Keyboard NULL NULL NULL 40.00000 NULL
3-C2924A SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL 148.50000 NULL
3-C2924A T0101 - SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL NULL 84.38000
3-C2924A T0102 - SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL NULL 67.50000

Finally, you will notice NULL values for some of the years where there was no sales activity for an item. You can take care of these and how they display, directly on your report or Excel spreadsheet.

Not bad at all!

For a primer on the Do’s and Don’ts of dynamic SQL, I invite you to read SQL Server MVP, Earland Sommarskog article, The Curse and Blessings of Dynamic SQL.

Until next post!

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

Advertisements

One Response to Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL – Follow up!

  1. CoreyC says:

    Thanks for the follow-up on this Mariano. This is definitely one of those posts I'm going to toss into my toolbox (with credit to you of course).This is wonderful information and very useful in most report writing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: