CODEWITHSUNDEEP
sql-server
user12235434
user12235434

Reputation:

Consecutive day count with total

Is there a way to show if the days go consecutively? I need to show a total for when the date is 5 consecutive.

The data is shown below. Could I put a date range for April like: date between 2019-04-01 and 2019-05-31 and show the DATEPART(WEEK,date) for the first date of the 5 consecutive.

I'd like to show the total of 5 consecutive days like below. It skips the week that did not have a date for 5 consecutively.

Week Total
21    7.50
23    7.50

enter image description here

Upvotes: 0

Views: 47

Answers (1)

smoore4
smoore4

Reputation: 4866

Something like this should work for you.

SELECT DATEPART(WEEK,[Date]),SUM([Dollar])
FROM [dbo].[TEST]
GROUP BY DATEPART(WEEK,[Date])
HAVING COUNT(DATEPART(WEEK,[Date])) >= 5

Here is a SQL Fiddle.

Upvotes: 1

Related Questions