DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them, but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

Product Aging Report using PIVOT

Must set sp_dbcmptlevel AdventureWorks2008, 90 Second PIVOT Example - take a set of raw invoices and product an Aging Report (columns for invoices > 30 days, > 60 days, etc.)
use AdventureWorks2008
GO
-- create table variable to hold results
DECLARE @tInvoices TABLE (CustomerID char(15), InvoiceNo Char(20),
   InvoiceDate DateTime,InvoiceAmount decimal(14,2), ReceivedAmount decimal(14,2))
 
-- create some dummy data
INSERT INTO @tInvoices  VALUES ('Customer 1', 'ABC', '09-01-2005', 1000, 0)
INSERT INTO @tInvoices  VALUES ('Customer 1', 'DEF','10-01-2005', 2000, 100)
INSERT INTO @tInvoices  VALUES ('Customer 1', 'GHI','11-01-2005', 3000, 3000)
INSERT INTO @tInvoices  VALUES ('Customer 1', 'JKL','12-01-2005', 4000, 175)
INSERT INTO @tInvoices  VALUES ('Customer 1', 'MNO','12-18-2005', 4000, 175)
INSERT INTO @tInvoices  VALUES ('Customer 2', 'PQR','05-01-2005', 500, 250)
INSERT INTO @tInvoices  VALUES ('Customer 2', 'STU','08-01-2005', 12000, 0)
INSERT INTO @tInvoices  VALUES ('Customer 2', 'WYX','10-01-2005', 7000, 70)
INSERT INTO @tInvoices  VALUES ('Customer 2', 'YYZ','12-01-2005', 3200, 1750)
 
-- every aging report must have an 'as of' aging date
DECLARE @dAgingDate DATETIME
SET @dAgingDate = '12-15-2005'
 
-- create brackets...could be configurable [1-45 days, etc.]
DECLARE @tAgingBrackets TABLE ( StartDay int, EndDay int, BracketNumber int, BracketLabel char(20))
INSERT INTO @tAgingBrackets VALUES (0,        30,1,   '< 30 Days')
INSERT INTO @tAgingBrackets VALUES (31,       60,2,  '31-60 Days')
INSERT INTO @tAgingBrackets VALUES (61,       90,3,  '61-90 Days')
INSERT INTO @tAgingBrackets VALUES (91,      120,4,  '91-120 Days' )
INSERT INTO @tAgingBrackets VALUES (121,999999,5,  '> 120 Days')
 
;WITH TempInvoicesCTE AS 
	(SELECT CustomerID,InvoiceNo,invoicedate, 
		InvoiceAmount-ReceivedAmount AS AmountOwed, tBR.BracketNumber 
		FROM @tInvoices TI, @tAgingBrackets TBR     
		WHERE InvoiceAmount-ReceivedAmount <> 0 and     
-- GRAB WHERE AMT DUE IS GREATER THAN ZERO, AND THE DIFFERENCE IN DAYS FALLS BETWEEN
			DATEDIFF(dd,invoicedate,@dagingdate) BETWEEN TBR.StartDay and TBR.EndDay  )
SELECT * FROM TempInvoicesCTE
	     PIVOT (  sum(AmountOwed) FOR BracketNumber In (  [1],  [2],[3],[4],[5])) As X
Media Format: 
Topics: