troubleshooting Question

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

Avatar of sqlcurious
sqlcuriousFlag for United States of America asked on
Microsoft SQL Server
4 Comments1 Solution60 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
sqlcurious

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros