I'm trying to find the maximum value in a column and keep the same value until the next max value is found, if found then replace it with the new one, in SQL as data lies in MS SQL Server
Algorithm would be basically keep a global variable for MaxDelay and keep rewriting that with new max or run two 'for loops' one for current row and other for row-1 until next project is found.
Should I be using a mix of recursive query and funtion in SQL? I tried Lag() but I'm unable to use Max() of Lag() due to windowed functions limitation.
SELECT *,
(select max(v) from (VALUES([Delay],Lag([Delay], 1) OVER(ORDER BY [Milestones], [Project Name])))as value(v)) as [MaxDate]
FROM dbo.[Scenario Testing with Previous Row]
error:
Msg 4108, Level 15, State 1, Line 2 Windowed functions can only appear in the SELECT or ORDER BY clauses.
Would appreciate if someone could give me some pointers.
Project Name Milestones Baseline Date Actual Date Delay Max Delay Worst Case Date
Project 1 MS_1 12/12/2016 15/12/2016 3 3 15/12/2016
Project 1 MS_2 14/12/2016 16/12/2016 2 3 17/12/2016
Project 1 MS_3 31/12/2016 09/01/2017 9 9 09/01/2017
Project 1 MS_4 11/01/2017 12/01/2017 1 9 20/01/2017
Project 1 MS_5 21/01/2017 24/01/2017 3 9 30/01/2017
Project 1 MS_6 01/02/2017 15/02/2017 14 14 15/02/2017
Project 1 MS_7 15/02/2017 16/02/2017 1 14 01/03/2017
Project 1 MS_8 26/02/2017 26/02/2017 0 14 12/03/2017
Project 1 MS_9 31/03/2017 31/03/2017 0 14 14/04/2017
If you want a cumulative max, then use the appropriate function. Something like this:
select t.*,
max(delay) over (order by [Milestones], [Project Name]) as running_max
from dbo.[Scenario Testing with Previous Row] t;