Group By get the currently matching Effective data out of past and future date in SQL Server

Question

I'm having list of rows with a column EffectiveOn in SQL Server database table. I want to fetch the currently applicable EffectiveOn for each AccountId respective to current date. Consider the following table

In the above table I have to fetch the rows whose (Id: 11 to 15) because the current date (i.e., today) is 2020-11-19

I tried the following solution but I can't How do I get the current records based on it's Effective Date?

Kindly assist me how to get the expected result-set.

Sample Data:

CREATE TABLE [dbo].[DataInfo]
(
    [Id] INT NOT NULL,
    [AccountId] INT NOT NULL,
    [EffectiveOn] DATE NOT NULL
)
GO
INSERT INTO [dbo].[DataInfo](Id, AccountId, EffectiveOn)
VALUES (1, 1, '2020-01-01'), (2, 2, '2020-01-02'), (3, 3, '2020-01-03'), (4, 4, '2020-01-04'), (5, 5, '2020-01-05'),
(6, 1, '2020-05-01'), (7, 2, '2020-05-02'), (8, 3, '2020-05-03'), (9, 4, '2020-05-04'), (10, 5, '2020-05-05'),
(11, 1, '2020-10-01'), (12, 2, '2020-10-02'), (13, 3, '2020-10-03'), (14, 4, '2020-10-04'), (15, 5, '2020-10-05'),
(16, 1, '2021-02-01'), (17, 2, '2021-02-02'), (18, 3, '2021-02-03'), (19, 4, '2021-02-04'), (20, 5, '2021-02-05')

The logic is effectively identical to your [last question](https://stackoverflow.com/questions/64910760/get-the-current-effective-date-from-the-list-of-records-has-past-and-future-date), just per group. — Thom A, Nov 19 '20 at 12:30
@Larnu - Yes its identical but I got the answer for the said question so I can't able to update the question. Because of the reason I posted this as a new question. — B.Balamanigandan, Nov 19 '20 at 12:32

score 0 · Answer 1 · answered Nov 19 '20 at 12:29

You can use a correlated subquery to get the most recent date as of a particular date:

select di.*
from datainfo di
where di.effectiveon = (select max(di2.effecctiveon)
                        from datainfo di2
                        where di2.accountid = di.accountid and
                              di2.effectiveon < getdate()
                       );

You can also do this with window functions:

select di.*
from (select di.*,
             row_number() over (partition by accountid order by effective on desc) as seqnum
      from datainfo di
      where di.effectiveon < getdate() 
     ) di
where seqnum = 1;

Group By get the currently matching Effective data out of past and future date in SQL Server

1 Answers1