We help IT Professionals succeed at work.

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

sqlcurious
sqlcurious asked
on
55 Views
Last Modified: 2017-04-15
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

CERTIFIED EXPERT

Commented:
can you explain what is messing up here?

some sample data and expected results would be good for understanding
Nitin SontakkeDeveloper
CERTIFIED EXPERT

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

  AND YEAR(MH.Added) < '2000'
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Was able to resolve the issue

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions