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

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
sqlcuriousAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you explain what is messing up here?

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

  AND YEAR(MH.Added) < '2000'
0
sqlcuriousAuthor Commented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlcuriousAuthor Commented:
Was able to resolve the issue
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.