extrobe
9/12/2017 - 7:32 PM

Repeating Row Numbers

This allows you to have repeating row numbers - eg, to group rows into weeks (7 rows)

SELECT PeriodID, min(AnalysisDate) as PeriodStartDate, SUM(ASU)/SUM(ASU+LSU) AS Score

FROM

(

SELECT q1.*, (ROW_NUMBER() OVER (Order by ID) + (7-1))/7 AS PeriodID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY AnalysisDate ASC) as ID, AnalysisDate, ASU, LSU FROM lastestdata ) q1

) q2

GROUP BY PeriodID