C RUBY-ON-RAILS MYSQL ASP.NET DEVELOPMENT RUBY .NET LINUX SQL-SERVER REGEX WINDOWS ALGORITHM ECLIPSE VISUAL-STUDIO STRING SVN PERFORMANCE APACHE-FLEX UNIT-TESTING SECURITY LINQ UNIX MATH EMAIL OOP LANGUAGE-AGNOSTIC VB6 MSBUILD

# R - count maximum number of consecutive dates

By : user2173624
Date : October 20 2020, 08:10 AM
wish helps you Let's first make a function that would find the maximum number of consecutive days, given a vector:
code :
``````gl <- function(x) {
y <- c(unclass(diff(x)))  # c and unclass -- preparing it for rle
r <- rle(y)
with(r, max(lengths[values==1]))
}
``````
``````df %>% group_by(key) %>% summarise(max.consecutive = gl(Date))

#  A tibble: 2 x 2
#     key max.consecutive
#   <int>           <int>
# 1     1               2
# 2     2               3
``````

Share :

## How do you count the number of consecutive dates in a table?

By : mccuenbr
Date : March 29 2020, 07:55 AM
it fixes the issue Here is an SQL only solution that I thought was really cool. Assuming the dates in your table are unique (not that it would be too hard to just group on them) you can use the method adapted from here http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. I ran through the example and there are some syntax errors, so hopefully I didn't repeat them in my answer below. I probably used some reserved keywords, so you may need to adapt that.
First create a table of Dates that is significantly large to cover your needs. I'm not sure what the best method for SQLite is, but in SQL Server you can insert integers into a Datetime field and it will do an implicit conversion of integers to dates. There are many methods to insert integers into tables...
code :
``````SELECT Dates.Date,
CASE
WHEN StreakTable.DATE IS NULL THEN 0
ELSE 1
END AS Result
FROM   Dates
LEFT JOIN StreakTable
ON Dates.DATE = StreakTable.DATE
WHERE  Dates.DATE BETWEEN (SELECT MIN(DATE) AS MinDate
FROM   StreakTable) AND (SELECT MAX(DATE) AS MaxDate
FROM   StreakTable)
``````
``````SELECT Date,
Result,
(SELECT COUNT(*)
FROM   (SQL 0) S
WHERE  S.Result <> SV.Result
AND S.GameDate <= SV.GameDate) AS RunGroup
FROM   (SQL 0) SV
``````
``````SELECT Result,
MIN(Date) AS StartDate,
MAX(Date) AS EndDate,
COUNT(*)      AS Days
FROM   (SQL 1) A
GROUP  BY Result,
RunGroup
ORDER  BY MIN(Date)
``````
``````SELECT TOP 1 *
FROM   (SQL 2) A
WHERE  Result = 1
ORDER  BY Games DESC
``````
``````SELECT *
FROM (SQL2) A
WHERE EndDate = (SELECT Max(Date)
FROM Streak)
``````
``````SELECT Result,
COUNT(*) as NumberOfStreaks
FROM (SQL 2) A
GROUP BY Result
WHERE Days >= 3
``````

## how to get maximum consecutive row count using sql

By : CollisionOfConcepts
Date : March 29 2020, 07:55 AM
help you fix your problem You can get the maximum consecutive row count by subtracting a sequence number from row_number and assigning a group. For instance, the following gets information about all consecutive values in the data:
code :
``````select group_name, yes_no, min(row_number), max(row_number), count(*)
from (select t.*,
(row_number - row_number() over (partition by group_name, yes_no
order by row_number)
) as grp
from table t
) t
group by group_name, grp, yes_no;
``````
``````insert into table(group_name, row_number, yes_no)
select group_name, maxrn + 1, max(cnt)
from (select group_name, yes_no, count(*) as cnt, max(row_number) as maxrn
from (select t.*,
(row_number - row_number() over (partition by group_name, yes_no
order by row_number)
) as grp
from table t
) t
group by group_name, grp, yes_no
) t
group by group_name
) t
``````

## Count the maximum date between consecutive dates and corresponding value using 3 columns

By : Alex0101
Date : March 29 2020, 07:55 AM
like below fixes the issue I have this data: , You can try the following:
code :
``````library(plyr)

maxWindDate <- function(row) {
in.range <- row\$Dates <= df\$Dates & df\$Dates - row\$duration < row\$Dates
winds <- df\$Wind[in.range]
dates <- df\$Dates[in.range]
if (length(winds) == 0) {
data.frame(maxWind=NA, maxDate=NA)
} else {
maxWind <- max(winds)
maxDate <- dates[which(winds == maxWind)[1]]
data.frame(maxWind=maxWind, maxDate=maxDate)
}
}

``````

## Count maximum number of consecutive 1s per group

By : Antis
Date : March 29 2020, 07:55 AM
should help you out Use the shifting cumsum trick to get all groups of consecutive 1s, and then use value_counts to find the largest group.
code :
``````u = df1['value'].eq(1)
v = u.ne(u.shift()).cumsum().where(u)

v.groupby(level=0).value_counts().max(level=0).reset_index(name='max_consec_wks')

userid  max_consec_wks
0     465               1
1     999               3
``````

## Count maximum consecutive row out of total row for each day Ms-Sql

By : LostStudent
Date : March 29 2020, 07:55 AM
Hope this helps I Want total number of row for each and for each day want consecutive max row count where condition numb>2 and calibration!=1 from table in pic below , I think you want conditional aggregation:
code :
``````select todaydate, count(*) as totalrow,
sum(case when (numb > 2 and calibration <> 1) then 1 else 0 end) as [max]
from table t
group by todaydate;
``````