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
ASKER