sqlcurious
asked on
Need similar results but guess messing up at the pivot query, please help
IF OBJECT_ID('tempdb.dbo.#Cus tomers') IS NOT NULL
DROP TABLE #Customers;
IF OBJECT_ID('tempdb.dbo.#Con signedByYe ar') 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 CountOfClientsWithCommissi ons,
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_Fee s 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.CommissionsGenera ted) 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.CountOfClientsWit hCommissio ns) 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
DROP TABLE #Customers;
IF OBJECT_ID('tempdb.dbo.#Con
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 CountOfClientsWithCommissi
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_Fee
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.CommissionsGenera
ORDER BY 1,3
SELECT *
FROM #ConsignedByYear CBY
PIVOT (SUM(CBY.CountOfClientsWit
ORDER BY 1,3
You are limiting data upto 1999 only. Is that the concern?
AND YEAR(MH.Added) < '2000'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Was able to resolve the issue
some sample data and expected results would be good for understanding