Perhaps I can't actually attach the RDL...?
Here is a script that builds a sample dataset for the above screenshot:
WITH cteBaseData AS (
SELECT
Company
, CalendarYear
, PONumber
, Quantity
, QtyRange
FROM (VALUES
('Company A' ,2012 ,'ABC' ,50 ,'1-50')
, ('Company A' ,2012 ,'BCD' ,75 ,'51-100')
, ('Company A' ,2012 ,'CDE' ,40 ,'1-50')
, ('Company A' ,2012 ,'DEF' ,110 ,'101-150')
, ('Company A' ,2012 ,'EFG' ,200 ,'151+')
, ('Company A' ,2012 ,'FGH' ,25 ,'1-50')
, ('Company A' ,2012 ,'GHI' ,50 ,'1-50')
, ('Company B' ,2012 ,'ABC' ,100 ,'51-100')
, ('Company B' ,2012 ,'BCD' ,100 ,'51-100')
, ('Company B' ,2012 ,'CDE' ,100 ,'51-100')
, ('Company B' ,2012 ,'DEF' ,100 ,'51-100')
, ('Company B' ,2012 ,'EFG' ,200 ,'151+')
, ('Company B' ,2012 ,'FGH' ,25 ,'1-50')
, ('Company B' ,2012 ,'GHI' ,50 ,'1-50')
, ('Company C' ,2012 ,'ABC' ,50 ,'1-50')
, ('Company C' ,2012 ,'BCD' ,40 ,'1-50')
, ('Company C' ,2012 ,'CDE' ,250 ,'151+')
, ('Company A' ,2013 ,'ABC2' ,50 ,'1-50')
, ('Company A' ,2013 ,'BCD2' ,75 ,'51-100')
, ('Company A' ,2013 ,'CDE2' ,40 ,'1-50')
, ('Company A' ,2013 ,'DEF2' ,110 ,'101-150')
, ('Company A' ,2013 ,'EFG2' ,200 ,'151+')
, ('Company A' ,2013 ,'FGH2' ,25 ,'1-50')
, ('Company A' ,2013 ,'GHI2' ,50 ,'1-50')
, ('Company B' ,2013 ,'ABC2' ,100 ,'51-100')
, ('Company B' ,2013 ,'BCD2' ,100 ,'51-100')
, ('Company B' ,2013 ,'CDE2' ,100 ,'51-100')
, ('Company B' ,2013 ,'DEF2' ,100 ,'51-100')
, ('Company B' ,2013 ,'EFG2' ,200 ,'151+')
, ('Company B' ,2013 ,'FGH2' ,25 ,'1-50')
, ('Company B' ,2013 ,'GHI2' ,50 ,'1-50')
, ('Company C' ,2013 ,'ABC2' ,50 ,'1-50')
, ('Company C' ,2013 ,'BCD2' ,40 ,'1-50')
, ('Company C' ,2013 ,'CDE2' ,250 ,'151+')
) AS T(Company, CalendarYear, PONumber, Quantity, QtyRange)
)
, cteSeries AS (
SELECT '1-50' AS QtyRange
UNION SELECT '51-100'
UNION SELECT '101-150'
UNION SELECT '151+'
)
, cteDataMap AS (
SELECT
c.QtyRange
, b.Company
, b.CalendarYear
, b.PONumber
FROM (
SELECT DISTINCT
Company
, CalendarYear
, PONumber
FROM cteBaseData
) AS b
CROSS JOIN cteSeries AS c
)
SELECT
m.Company
, m.CalendarYear
, m.QtyRange
, b.PONumber
, COALESCE(b.Quantity, 0) AS Quantity
FROM cteDataMap m
LEFT JOIN cteBaseData b ON
m.Company = b.Company
AND m.CalendarYear = b.CalendarYear
AND m.PONumber = b.PONumber
AND m.QtyRange = b.QtyRange
No comments:
Post a Comment