Re-Attendance

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

Hi
Please let me know if this helps ,:slightly_smiling_face::slightly_smiling_face:

Rolling is possible with
Lead
Lag
functions
From SQL server 2012

It's also possible with joins
But a little complicated code
With self joins ..same table joined to itself

If you are only looking for SQL code

I am not able to access my desktop

Once i am able to access can work on it
And try

hi katie

I was able to work on it now

Here's my solution SQL

Create Data Output Script
drop table #ClientNumber 
go 

create table #ClientNumber 
(
Client_Number int,
Arrival_Date date,
Arrival_time time 
)
go 
insert into #ClientNumber select  123, '2018-01-01','09:00'
insert into #ClientNumber select  567, '2018-01-01','15:02'
insert into #ClientNumber select  456, '2018-01-02','11:00'
insert into #ClientNumber select  895, '2018-01-03','10:12'
insert into #ClientNumber select  123, '2018-01-04','12:55'
insert into #ClientNumber select  158, '2018-01-04','16:02'
insert into #ClientNumber select  158, '2018-01-05','09:36'
insert into #ClientNumber select  895, '2018-01-06','13:22'
insert into #ClientNumber select  346, '2018-01-06','15:49'
insert into #ClientNumber select  123, '2018-01-07','16:28'
insert into #ClientNumber select  158, '2018-01-15','09:01'
insert into #ClientNumber select  346, '2018-01-20','12:16'
insert into #ClientNumber select  346, '2018-01-21','14:51'
go 

drop table #output 
go 

create table #output 
(Client_Number int,
Arrival_Date date,
Next_Arrival_Date date,
Days_Between_Visits int,
Re_Visited_within_7days_Flag int
)
go 

insert into #output select 123,'2018-01-01','2018-01-04', 3, 1
insert into #output select 123,'2018-01-04','2018-01-07', 3,1
insert into #output select 158,'2018-01-04','2018-01-05', 1, 1
insert into #output select 158,'2018-01-05','2018-01-15', 10, 0
insert into #output select 346,'2018-01-06','2018-01-20', 14, 0
insert into #output select 346,'2018-01-20','2018-01-21', 1, 1
insert into #output select 895,'2018-01-03','2018-01-06', 3, 1
go
SQL
SELECT a.client_number, 
       a.arrival_date, 
       Min(b.arrival_date)                                Next_Arrival_Date, 
       Datediff(day, a.arrival_date, Min(b.arrival_date)) Days_Between_Visits, 
       CASE 
         WHEN Datediff(day, a.arrival_date, Min(b.arrival_date)) < 7 THEN 1 
         ELSE 0 
       END 
       Re_Visited_within_7days_Flag 
FROM   #clientnumber a 
       JOIN #clientnumber b 
         ON a.client_number = b.client_number 
            AND b.arrival_date > a.arrival_date 
GROUP  BY a.client_number, 
          a.arrival_date 
ORDER  BY 1 
go
Result

Thank you ever so much, will give this a go :slight_smile: