UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Count Consecutive Dates (streak Of Dates), Any Version    
 
   
daschrislydon
post Nov 24 2017, 06:59 AM
Post#1



Posts: 76
Joined: 28-August 15



Hello

Tried searching for this online and on the forum but can't seem to see the answer out there...

I have a table with data entered only once a day, but not every day. I managed to get a query to show me how many days since the last entry quite happily, with the assistance of Google:

SQL
SELECT T.TheDate, T.PrevDate, DateDiff("d",[PrevDate],[TheDate]) AS DaysSince
FROM (SELECT TheDate,
( SELECT MAX(TheDate)
FROM _Dates T2
WHERE T2.TheDate < T1.TheDate
) AS PrevDate
FROM _Dates T1
) AS T
ORDER BY T.TheDate DESC;


Now, I almost want the inverse of that. I want to be able to count the number of consecutive dates in a streak. The results can just show me the start or end date of the streak (as I could always do a sum to display a date range, if needed, as I'd have an integer of how many days are in the streak)

Base data looks like:


























DateID TheDate
11-Jan-17
22-Jan-17
33-Jan-17
44-Jan-17
513-Jan-17
614-Jan-17
715-Jan-17
816-Jan-17
917-Jan-17
1022-Jan-17
1124-Jan-17
1227-Jan-17
1329-Jan-17
1431-Jan-17
155-Feb-17
166-Feb-17
177-Feb-17
188-Feb-17
199-Feb-17
2010-Feb-17


The result should be something like:














Last Date in streak Number of days in streak
4-Jan-174
17-Jan-175
22-Jan-171
24-Jan-171
27-Jan-171
29-Jan-171
31-Jan-171
10-Feb-176


I feel like it should be possible... but can't quite wrap my head around it....

Thanks for any tips/advice!

Chris

P.S. Don't know why there are huge gaps before each table....
Go to the top of the page
 
Jeff B.
post Nov 24 2017, 07:23 AM
Post#2


UtterAccess VIP
Posts: 9,880
Joined: 30-April 10
From: Pacific NorthWet


Just trying to wrap my head around the concepts ...

I might try approaching this using a procedure. Start a "counter" when the date is not the previous date. Move to the next record. If the date is the (now) previous date plus one, add one to the counter. Keep iterating until the "current" date is more than one more than the "previous". Note the previous date and the counter. Reset the counter. Keep iterating until the records run out.

?Is that a fair paraphrase you would use to tell an intern how to do it manually?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
daschrislydon
post Nov 24 2017, 07:36 AM
Post#3



Posts: 76
Joined: 28-August 15



I think that's about right.

I feel like there must be something in the fact that in the SQL right now I can get if a date is only 1 day since the previous date, but then how do I group a series of those 1s and then count them...
(If the days since previous date is more than 1, then the streak is only ever 1, so that's fine...)

*continues to scratch head*
Go to the top of the page
 
GroverParkGeorge
post Nov 24 2017, 07:37 AM
Post#4


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


It seems to me that this problem is a least somewhat similar to one for which Doug Steele has previously published a solution. One might find that by searching on his name.

--------------------
Go to the top of the page
 
daschrislydon
post Nov 24 2017, 08:01 AM
Post#5



Posts: 76
Joined: 28-August 15



Hi there George

Thanks for the pointer - I've tried searching through Doug Steele's previous posts but I can't seem to find anything.

Maybe he'll stumble on this post in the mean time... (or perhaps I can direct message him on here somehow...)

Thanks again
Go to the top of the page
 
GroverParkGeorge
post Nov 24 2017, 08:41 AM
Post#6


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Did you look here?

--------------------
Go to the top of the page
 
ADezii
post Nov 24 2017, 09:12 AM
Post#7



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I have solved similar type problems by using Nested Recordset where the Inner Recordset is a Clone of the Outer. You loop thru both Recordset simultaneously with the Inner Recordset being Offset by +1. If the difference in Dates is 1, then the Date are consecutive, so Increment a Counter, record the last Consecutive Date as well as the Value of the Counter. If the difference in Dates <> 1 list the Date and Reset the Counter. This Looping Process continues until you reach the EOF of the Inner Recordset which is compared to the Next-to-Last Record in the Outer Recordset. The Coding should not be difficult at all, if you are interested in this approach, let me know and I'll give it a try.

P.S. - Obviously, this is purely a Code Based solution, or Non SQL which is why I will not attempt it unless you are comfortable with this approach. The Results would be written to a Results Table.
Go to the top of the page
 
daschrislydon
post Nov 24 2017, 09:26 AM
Post#8



Posts: 76
Joined: 28-August 15



This *seems* to work, on initial testing.

SQL
SELECT Min([_TheDates].TheDate) AS StartingDate, Max([_TheDates].TheDate) AS EndingDate, Count(*) AS ConsecutiveDays
FROM _TheDates GROUP BY [TheDate]-DCount("*","_TheDates","TheDate<=#" & Format([TheDate],"mm/dd/yyyy") & "#");


Gives the start date of streak in col1, then end date of streak in col2, then the number of days in col3

(Mostly garnered from here: https://groups.google.com/forum/#!msg/m...E/zFL3GC7mtdwJ)
(EDIT: and here: http://access.mvps.org/access/queries/qry0018.htm)
Go to the top of the page
 
daschrislydon
post Nov 24 2017, 09:28 AM
Post#9



Posts: 76
Joined: 28-August 15



Hello

You must have posted as I was composing my last reply.

Thanks - I am ideally looking for an SQL solution.
I could probably have put some code together to cycle through and pump out the data to a table, but it seems I might have found an SQL solution.

Thanks, though!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 07:59 PM