SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab(code INT, quantity INT, effectivedate date);
INSERT INTO tab 
SELECT 1        ,2,        '20180301'
UNION ALL SELECT 1        ,3  ,      '20180401'
UNION ALL SELECT 1        ,3  ,     '20180501'
UNION ALL SELECT 1        ,7  ,     '20180501'   -- added for demo
UNION ALL SELECT 1        ,3  ,      '20180601'
UNION ALL SELECT 2      ,  5  ,      '20180301'
UNION ALL SELECT 2       , 6 ,       '20180401'
UNION ALL SELECT 3     ,   7 ,       '20180201'
UNION ALL SELECT 3    ,    8  ,      '20180301'
UNION ALL SELECT 3   ,     5 ,       '20180401'
10 rows affected

SELECT TOP 1 WITH TIES *
FROM tab
WHERE EffectiveDate <= GETDATE()
ORDER BY RANK() OVER(PARTITION BY code ORDER BY EffectiveDate DESC)


code
quantity
effectivedate


1
3
01/05/2018 00:00:00

1
7
01/05/2018 00:00:00

2
6
01/04/2018 00:00:00

3
5
01/04/2018 00:00:00


SELECT TOP 1 WITH TIES *
FROM tab
WHERE EffectiveDate <= GETDATE()
ORDER BY ROW_NUMBER() OVER(PARTITION BY code ORDER BY EffectiveDate DESC)


code
quantity
effectivedate


1
3
01/05/2018 00:00:00

2
6
01/04/2018 00:00:00

3
5
01/04/2018 00:00:00

code	quantity	effectivedate
1	3	01/05/2018 00:00:00
1	7	01/05/2018 00:00:00
2	6	01/04/2018 00:00:00
3	5	01/04/2018 00:00:00