Hi experts,
Below is my store proc for a bike sale, I am getting the results of individual CTE's correclty when I hardcode the time period values but at the end result I am getting the same result for CTE4, CTE5 and CTE6.
I have checked if the timeperiods are coming out right and they is no problem with that, please suggest if I am going wrong somewhere in the pivot
SELECT
M.Added AS DateAdded,
M.Customer_No,
M.FirstName,
M.LastName,
M.List_No,
MC.Birthyear,
M.[State],
M.Country,
M.Source_Code,
replace(M.Sub_Source_Code, ',', '&') AS Sub_Source_Code
, M.Added T1, DATEADD(YEAR, 1, DATEADD(DAY, -1, M.Added)) T2, DATEADD(YEAR, 1, M.Added) T3, DATEADD(YEAR, 1, DATEADD(DAY, -1, M.Added)) T4, DATEADD(YEAR, 1, M.Added) T5, DATEADD(YEAR, 1, DATEADD(DAY, -1, M.Added)) T6
INTO #CTESOURCE FROM He.dbo.MLCC M WITH (NOLOCK)
cross apply (
SELECT charindex('~', M.Sub_Source_Code, 1) AS pos1
) AS xapply1 (pos1)
cross apply (
SELECT CASE WHEN xapply1.pos1 > 0 THEN left(M.Sub_Source_Code,xapply1.pos1 - 1)
ELSE M.Sub_Source_Code
END AS consider_code
) AS xapply2 (consider_code)
WHERE
M.Customer_No < 50000000
; WITH cteYearRanges(Label, YearsToAdd, DaysToSubtract) AS
(
SELECT 'T1', 0, 0
UNION ALL
SELECT 'T2', 1, 1
UNION ALL
SELECT 'T3', 1, 0
UNION ALL
SELECT 'T4', 2, 1
UNION ALL
SELECT 'T5', 2, 0
UNION ALL
SELECT 'T6', 3, 1
)
SELECT * INTO #TEMPCUST FROM
(
SELECT C.Customer_no,
C.DateAdded,
cteYearRanges.Label,
DATEADD(YEAR, cteYearRanges.YearsToAdd, DATEADD(DAY, -cteYearRanges.DaysToSubtract, C.DateAdded)) d
FROM #CTESource C
CROSS JOINcteYearRanges
WHERE DATEADD(YEAR, cteYearRanges.YearsToAdd, DATEADD(DAY, -cteYearRanges.DaysToSubtract, C.DateAdded)) <= GETDATE()
) p
PIVOT (MIN(d) FOR Label IN ([T1], [T2], [T3], [T4], [T5], [T6])) pvt ;
WITH CTE4( CustomerNo, LotsPurBikes0910, AucPurBikes09To10) AS(
SELECT DISTINCT S.Customer_No, COUNT(S.Lot_No) AS LotsPurBikes0910, SUM(S.PRwithBP) AS AucPurBikes09To10
FROM HI.dbo.AllSales S WITH (NOLOCK)
LEFT OUTER JOIN HI.dbo.BikeSale A WITH (NOLOCK)
ON S.Sale_No = A.Sale_No
LEFT OUTER JOIN #TEMPCUST CS
ON CS.Customer_No = S.Customer_No
AND A.BikeSale_End_Date >= T1
AND A.BikeSale_End_Date <= T2
WHERE Buy_Back = 0
AND A.BikeSaleSubTypeID IN (5, 18)
GROUP BY S.Customer_No
),
CTE5( CustomerNo, LotsPurBikes1011, AucPurBikes10To11) AS(
SELECT DISTINCT S.Customer_No, COUNT(S.Lot_No) AS LotsPurBikes1011, SUM(S.PRwithBP) AS AucPurBikes10To11
FROM HI.dbo.AllSales S WITH (NOLOCK)
LEFT OUTER JOIN HI.dbo.BikeSale A WITH (NOLOCK)
ON S.Sale_No = A.Sale_No
LEFT OUTER JOIN #TEMPCUST CS
ON CS.Customer_No = S.Customer_No
AND A.BikeSale_End_Date >= T3
AND A.BikeSale_End_Date <= T4
WHERE Buy_Back = 0
AND A.BikeSaleSubTypeID IN (5, 18)
GROUP BY S.Customer_No
),
CTE6( CustomerNo, LotsPurBikes1112, AucPurBikes11To12) AS(
SELECT DISTINCT S.Customer_No, COUNT(S.Lot_No) AS LotsPurBikes1112, SUM(S.PRwithBP) AS AucPurBikes11To12
FROM HI.dbo.AllSales S WITH (NOLOCK)
LEFT OUTER JOIN HI.dbo.BikeSale A WITH (NOLOCK)
ON S.Sale_No = A.Sale_No
LEFT OUTER JOIN #TEMPCUST CS
ON CS.Customer_No = S.Customer_No
AND A.BikeSale_End_Date >= T5
AND A.BikeSale_End_Date <= T6
WHERE Buy_Back = 0
AND A.BikeSaleSubTypeID IN (5, 18)
GROUP BY S.Customer_No
),
SELECT A.*,L.* FROM #CTESOURCE A
LEFT OUTER JOIN CTE11 L on L.CustomerNo = A.Customer_No
WHERE A.Customer_No = 1363871
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.