[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Question regarding time periods

Posted on 2014-12-09
2
Medium Priority
?
78 Views
Last Modified: 2014-12-11
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
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40490452
Have you checked your #tempCust table? Does it have the correct date ranges? Please note that the date ranges will be relative to the DateAdded per each customer
0
 

Author Closing Comment

by:sqlcurious
ID: 40494358
thanks!
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question