Max() Over Partition By with date conditions

Question

How do I get the following result in yellow?

I want to find the maximum score over the previous year (excluding the current date) and partition by Name1 and Parent1

I've tried the following which is not giving the desired result, it just returns the max with correct partition but across all dates.

select 
[VDate]
,[Name1]
,[Parent1]
,[Score]
,max(case when [VDate] > dateadd(year, -1, [VDate]) then [Score] else null end) over (partition by [Name1], [Parent1]) AS MaxScoreInPreviousLast12Months
from [dbo].[Control]

Table Data:

CREATE TABLE Control
    ([VDate] datetime, [Name1] varchar(10), [Parent1] varchar(10), [Score] int);

INSERT INTO Control ([VDate], [Name1], [Parent1], [Score])
VALUES
('2018-08-01 00:00:00', 'Name1', 'Parent1', 80),
('2018-07-01 00:00:00', 'Name1', 'Parent1', 85),
('2018-06-01 00:00:00', 'Name1', 'Parent1', 90),
('2017-09-01 00:00:00', 'Name1', 'Parent1', 100),
('2017-08-01 00:00:00', 'Name1', 'Parent1', 95),
('2017-07-01 00:00:00', 'Name1', 'Parent1', 70),

('2018-08-01 00:00:00', 'Name2', 'Parent2', 80),
('2018-07-01 00:00:00', 'Name2', 'Parent2', 85),
('2018-06-01 00:00:00', 'Name2', 'Parent2', 90),
('2017-10-01 00:00:00', 'Name2', 'Parent2', 60),
('2017-08-01 00:00:00', 'Name2', 'Parent2', 95),
('2017-07-01 00:00:00', 'Name2', 'Parent2', 70),

('2018-08-01 00:00:00', 'Name3', 'Parent3', 80),
('2018-07-01 00:00:00', 'Name3', 'Parent3', 96),
('2018-06-01 00:00:00', 'Name3', 'Parent3', 90),
('2017-10-01 00:00:00', 'Name3', 'Parent3', 96),
('2017-08-01 00:00:00', 'Name3', 'Parent3', 99),
('2017-07-01 00:00:00', 'Name3', 'Parent3', 105)
;

This is for SQL Server 2016+

@MJH Null or zero means there are no previous scores to find the max. I'm trying to find the max of data in the previous year EXCLUDING the current date. I have no preference for null or zero in this case. — , Sep 28 '18 at 13:29

score 5 · Accepted Answer · answered Sep 28 '18 at 13:24

5

Here is one way to accomplish using outer apply to a correlated subquery. Thanks for posting ddl and sample data. Made this nice and easy to work with.

select c.*
    , x.MaxScore
from Control c
outer apply
(
    select MaxScore = max(Score)
    from Control c2
    where c2.VDate < c.VDate
        and c2.VDate >= dateadd(year, -1, c.VDate)
        and c.Name1 = c2.Name1
        and c.Parent1 = c2.Parent1
) x

answered Sep 28 '18 at 13:24

Sean Lange

33,028
3
25
40

Thanks for your answer. Potentially I could have hundreds of outer apply statements with additional calcs. Out of interest is it possible to do what I want using just `over partition by` ? – Sep 28 '18 at 13:30
I don't see how partitioning would work here. But if you have hundreds of columns in a query I question what is going on as that sounds pretty sketchy. – Sean Lange Sep 28 '18 at 13:36
1

@pathDongle - one could imagine some future version of SQL that supports some more logic in a `RANGE` window framing clause, but as it currently stands (both in the SQL Server product and the standard, so far as I'm aware) you can only specify simple numbers of rows to include. There's no way to say `RANGE BETWEEN {row with date > current rows date - 1 year} AND 1 PRECEDING`. – Damien_The_Unbeliever Sep 28 '18 at 13:45
@SeanLange - my base table is time series data with approx 50 independent variables which update with new data every day. I need to produce analysis over different time frames with many aggregates and combined aggregates which creates hundreds of new columns. I can however use 1 outer apply per timeframe so it's not too bad. I'm not sue how else I could do this to reduce the sketchyness :) – Sep 28 '18 at 13:59

MJH · Answer 2 · 2018-09-28T13:45:55.230

2

Something like this (a correlated subquery):

select  c1.[VDate]
        ,c1.[Name1]
        ,c1.[Parent1]
        ,c1.[Score]
        ,(select max(c2.score) from [dbo].[Control] c2 where c2.Name1 = c1.Name1 and c2.Parent1 = c1.Parent1 and c2.vdate > dateadd(year, -1, c1.vdate) and c2.vdate < c1.vdate) MS
from    [dbo].[Control] c1

edited Sep 28 '18 at 13:45

answered Sep 28 '18 at 13:40

MJH

1,710
1
9
19

score 0 · Answer 3 · answered Sep 28 '18 at 13:58

0

Assuming you have one row per month:

select c.*,
       max(score) over (partition by name1, parent1 
                        rows between 12 preceding and 1 preceding
                       ) as rolling_max_12
from [dbo].[Control] c;

If you don't have rows for every month, you can still solve the problem, but you data seems to have such data.

answered Sep 28 '18 at 13:58

Gordon Linoff

1,242,037
58
646
786

1

Eh? The sample data seems to have two rows for July/August 2017, then a row for either September or October 2017, then jumps to June 2018 and may have a further jump. How are you seeing one row per month here? – Damien_The_Unbeliever Sep 28 '18 at 14:09

Max() Over Partition By with date conditions

3 Answers3