Hi Likiraght and welcome aboard!
Your question is good and so is your test data but you'll find that almost everyone likes to test their coded solutions before they post a reply. They (including me) don't want to have to take the time to convert your test data to code and many heavy hitters on this forum will simply skip over posts that don't provide the test data in a "readily consumable" format.
With that thought in mind, please read'n'heed the article at the link in my signature line below. It'll really help us help you in the future.
Since you're brand new here, I've converted your test data into "readily consumable" code for you as follows (it's just one of many methods that result in the same thing... "readily consumable" test data):
--===== Create the given test data in a readily consumable format.
-- You should do this whenever you post test data.
IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL
DROP TABLE #TestData
SELECT [TimeStamp] = CONVERT(DATE,v.BrFrDate,103)
,Flow = CONVERT(INT,v.Flow)
Here's one solution to your problem... it uses a rather old fashion method of subtracting a partitioned ever-increasing number from the date to form group "numbers", which appear to be dates but are just a way of grouping rows. I've also taken the liberty to include a few more things that I think they may ask you for in the future.
WITH cteGrp AS
(--==== Substact an increasing number of days from the TimeStamp to form groups of dates based on
-- whether or not they have a FLOW of < 20.
SELECT GrpNum = DATEADD( dd
,-ROW_NUMBER() OVER (PARTITION BY CASE WHEN Flow >= 20 THEN 1 ELSE 0 END
ORDER BY [TimeStamp])
(--===== Determine a count for each group so we can figure out if there are 5 or more in a group.
,GrpCnt = COUNT(*) OVER (PARTITION BY GrpNum)
(--==== Find the maximum TimeStamp of any group having a FLOW of <20 for 5 days or more
SELECT CutOffDate = MAX([TimeStamp])
WHERE Flow < 20 AND GrpCnt >=5
SELECT StartDate = MIN([TimeStamp])
,EndDate = MAX([TimeStamp])
,AvgFlow = AVG(Flow+0.0)
,DaysMeasured = COUNT(*)
,TotalFlow = SUM(Flow)
FROM #TestData td
WHERE [TimeStamp] > (SELECT CutOffDate FROM cteCutOffDate)
For the test data you provided, that results in the following:
I suppose there's probably a simpler solution that uses Lead/Lag but I'm currently working on a machine that only has SQL Server 2008 on it and so don't have the ability to use Lead/Lag.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)