Calculate occurrences in the last 7 days in SQL

Question

I have a table with CustomerID and ArrivalDateTime (datetime). I would like to calculate the occurrence of the CutomerID within the last 7 days of each Arrival Date.

I came up with the below SQL statement however it is not working.

Can someone help me what I am doing wrong?
Thanks

SELECT d1.DateValue,
       cr1.CustomerID,
       COUNT(ISNULL(cr2.CustomerID,0)) as [No_of_presentation]
FROM [dbo].[CustomerPresentation] cr1 join DimDate d1 on d1.DateValue = cr1.Arrivaldate
    Left JOIN [dbo].[CustomerPresentation] cr2 ON cr1.CustomerID = cr2.CustomerID
    join DimDate d2 on d2.DateValue= cr2.ArrivalDate
WHERE (d1.DateValue BETWEEN DATEADD(dd,-7,d2.DateValue) AND d2.DateValue)
GROUP BY d1.DateValue, cr1.CustomerID

Answer 1

You can tweak your query as:

SELECT d1.DateValue, cr1.CustomerID,
       COUNT(*) as No_of_presentation
FROM dbo.CustomerPresentation cr1 JOIN
     DimDate d1 
     ON d1.DateValue = cr1.Arrivaldate JOIN
     dbo.CustomerPresentation cr2
     ON cr1.CustomerID = cr2.CustomerID JOIN
     DimDate d2 
     ON d2.DateValue= cr2.ArrivalDate
WHERE d2.DateValue >= DATEADD(day, -7, d1.DateValue) AND 
      d2.DateValue <= d1.DateValue
GROUP BY d1.DateValue, cr1.CustomerID;

Note that LEFT JOIN is not necessary. Each row is going to match itself. I think the issue in your query is that the dates are backward.

That said, I would recommend writing the query using a correlated subquery or apply. I also don't think that joining to the date dimension is necessary. The key you are using seems to be a date. So:

select cr.*, cd.cnt_7
from dbo.CustomerPresentation cr cross apply
     (select count(*) as cnt_7
      from  dbo.CustomerPresentation cr2
      where cr2.CustomerID = cr.CustomerID and
            cr2.ArrivalDate >= dateadd(day, -7, cr.ArrivalDate) and
            cr2.ArrivalDate <= cr.ArrivalDate
     ) cd;