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/detectingrunsorstreaksinyourdata. 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)
}
}
df < adply(df, 1, maxWindDate)

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 MsSql
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;

