Hello, is anyone able to help me with my sql code to identify the customers/clients who come to the shop more than once within 7 days (rolling), and count how many times in the week they came?
so far i have the following but not sure if it will work?
SELECT
c.[Arrival_Date] as Next_Arrival_Date,
Datediff(dd,d.[Arrival_Date], c.[Arrival_Date]) as Days_Between_Visits,
count(c.[Client_Number]) as No_Of_Visits
FROM (SELECT a.[Client_Number], a.[Arrival_Date], a.[Arrival_Time],
rank () over (order by a.[Client_Number], a.[Arrival_Date], a.[arrival_time]) AS row
FROM tlb_Client_Visit_Tracke a) AS c
JOIN (SELECT b.[Client_Number],b.[Arrival_Date], b.[Arrival_Time],
rank () over (order by b.[Client_Number], b.[Arrival_Date], b.[arrival_time]) AS row
FROM tlb_Client_Visit_Tracker b) AS d
ON ( c.[Client_Number] = d.[Client_Number] )
and d.[Arrival_Date]>= '2018-04-01'
and c.[Arrival_Date]>= '2018-04-01'
AND ( d.row - c.row = -1 )
and (Datediff(dd,d.[Arrival_Date], c.[Arrival_Date]) <=7)
group by
c.[Client_Number],
d.[Arrival_Date],
c.[Arrival_Date]
Here is a sample from the original data table:
Client_Number Arrival_Date Arrival_time
123 2018-01-01 09:00
567 2018-01-01 15:02
456 2018-01-02 11:00
895 2018-01-03 10:12
123 2018-01-04 12:55
158 2018-01-04 16:02
158 2018-01-05 09:36
895 2018-01-06 13:22
346 2018-01-06 15:49
123 2018-01-07 16:28
158 2018-01-15 09:01
346 2018-01-20 12:16
346 2018-01-21 14:51
and im hoping for a sql code to do the following:
Client_Number Arrival_Date Next_Arrival_Date Days_Between_Visits Re_Visited_within_7days_Flag
123 2018-01-01 2018-01-04 3 1
123 2018-01-04 2018-01-07 3 1
158 2018-01-04 2018-01-05 1 1
158 2018-01-05 2018-01-15 10 0
346 2018-01-06 2018-01-20 14 0
346 2018-01-20 2018-01-21 1 1
895 2018-01-03 2018-01-06 3 1