Need similar results but guess messing up at the pivot query, please help

sqlcurious
sqlcurious used Ask the Experts™
on
IF OBJECT_ID('tempdb.dbo.#Customers') IS NOT NULL
  DROP TABLE #Customers;

IF OBJECT_ID('tempdb.dbo.#ConsignedByYear') IS NOT NULL
  DROP TABLE #ConsignedByYear;

-- Customers:
SELECT MH.Customer_No,
       CASE
         WHEN YEAR(MH.Added) < '1993' THEN '1992'
         ELSE YEAR(MH.Added)
       END AS YearAdded,
       Added
  INTO #Customers
  FROM Herit.dbo.ML_HCC MH
 WHERE MH.Customer_No NOT IN (200003,407319,397630)
   AND YEAR(MH.Added) < '2000'
 ORDER BY Customer_No, YearAdded;

-- Commissions:
SELECT YEAR(C.Added) AS YearConsignmentAdded,
       C1.YearAdded,
       COUNT(C1.Customer_No) AS CountOfClientsWithCommissions,
       SUM(CLF.TotalFee) AS CommissionsGenerated
  INTO #ConsignedByYear
  FROM HNAI.dbo.Consignor C
 INNER JOIN #Customers C1
    ON C.Customer_No = C1.Customer_No
 INNER JOIN HNAI.dbo.AllSales [AS]
    ON C.Sale_No = [AS].Sale_No
   AND C.Consignor_No = [AS].Consignor_No
 INNER JOIN HNAI.dbo.Consignor_Lot_Fees CLF
    ON C.Sale_No = CLF.Sale_No
   AND [AS].Lot_No = CLF.Lot_No
   AND C.Consignor_No = CLF.Consignor_No
 WHERE [AS].Transaction_Date > C1.Added
 GROUP BY C1.YearAdded, YEAR(C.Added)
 ORDER BY YearConsignmentAdded;

SELECT *
  FROM #ConsignedByYear CBY
 ORDER BY CBY.YearConsignmentAdded

SELECT *
  FROM #ConsignedByYear CBY
 PIVOT (SUM(CBY.CommissionsGenerated) FOR YearConsignmentAdded IN ([1992], [1993], [1994], [1995], [1996], [1997], [1998], [1999], [2000], [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2014], [2015], [2016], [2017]) )AS unpvt
 ORDER BY 1,3

SELECT *
  FROM #ConsignedByYear CBY
 PIVOT (SUM(CBY.CountOfClientsWithCommissions) FOR YearConsignmentAdded IN ([1992], [1993], [1994], [1995], [1996], [1997], [1998], [1999], [2000], [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2014], [2015], [2016], [2017]) )AS unpvt
 ORDER BY 1,3
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
can you explain what is messing up here?

some sample data and expected results would be good for understanding
You are limiting data upto 1999 only. Is that the concern?

  AND YEAR(MH.Added) < '2000'
Hi Ryan Chong, thanks for the reply. Like I mentioned in the question, I was messing up at creating the pivot table:
I figured out the issue and corrected as below:
SELECT *
INTO #Temp
FROM (
    SELECT
       'CountOfClientsWithCommissions' AS [1] ,YearAdded as [YearAdded], CAST(YearConsignmentAdded AS INT) AS YearConsignmentAdded ,
        CountOfClientsWithCommissions
    FROM #ConsignedByYear
) as s
PIVOT
(
    SUM(s.CountOfClientsWithCommissions)
    FOR s.YearConsignmentAdded IN ([1992], [1993], [1994], [1995], [1996], [1997],[1998],[1999],[2000],[2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2014], [2015], [2016], [2017] )
)AS pvt

UNION

SELECT *
FROM (
    SELECT
        'CommissionsGenerated' AS [1],YearAdded as [YearAdded], CAST(YearConsignmentAdded AS INT) AS YearConsignmentAdded ,
        CommissionsGenerated
    FROM #ConsignedByYear
) as s
PIVOT
(
    SUM(s.CommissionsGenerated)
    FOR s.YearConsignmentAdded IN ([1992], [1993], [1994], [1995], [1996], [1997],[1998],[1999],[2000], [2001], [2002], [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2014], [2015], [2016], [2017])
)AS pvt

Author

Commented:
Was able to resolve the issue

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial