Avatar of sqlcurious
sqlcurious
Flag for United States of America asked on

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
Microsoft SQL Server

Avatar of undefined
Last Comment
sqlcurious

8/22/2022 - Mon
Ryan Chong

can you explain what is messing up here?

some sample data and expected results would be good for understanding
Nitin Sontakke

You are limiting data upto 1999 only. Is that the concern?

  AND YEAR(MH.Added) < '2000'
ASKER CERTIFIED SOLUTION
sqlcurious

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sqlcurious

ASKER
Was able to resolve the issue
Your help has saved me hundreds of hours of internet surfing.
fblack61