CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
user3904868
user3904868

Reputation:

Max() Over Partition By with date conditions

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+

Upvotes: 7

Views: 4997

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

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.

Upvotes: 0

MJH
MJH

Reputation: 1750

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

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 5

Related Questions