Max() Over Partition By with date conditions

6
votes

How do I get the following result in yellow?

enter image description here

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+

3
sqlsql-servertsqlsql-server-2016
Why is line 7 "Null or zero"? – MJH
@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. – user3904868

3 Answers

5
votes

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
2
votes

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
0
votes

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.