SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
	Mar  4 2020 00:59:26 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



drop table if exists UholdingsPE

CREATE TABLE UholdingsPE(
   Reference_Date DATE  NOT NULL
  ,Investment     VARCHAR(14) NOT NULL
  ,Holding        VARCHAR(9) NOT NULL
  ,Price          INTEGER  NOT NULL

  primary key (Reference_Date, Investment, Holding)
);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('3/31/2017','Example Fund 1','Apple',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('3/31/2017','Example Fund 1','Microsoft',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('6/30/2017','Example Fund 1','Apple',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('6/30/2017','Example Fund 1','Microsoft',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('9/30/2017','Example Fund 1','Apple',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('9/30/2017','Example Fund 1','Microsoft',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('12/31/2017','Example Fund 1','Apple',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('12/31/2017','Example Fund 1','Microsoft',1);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('6/30/2018','Example Fund 1','Apple',22);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('6/30/2018','Example Fund 1','Microsoft',22);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('12/31/2018','Example Fund 1','Apple',33);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('12/31/2018','Example Fund 1','Microsoft',33);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('6/30/2019','Example Fund 1','Apple',44);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('6/30/2019','Example Fund 1','Microsoft',44);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('12/31/2019','Example Fund 1','Apple',55);
INSERT INTO UholdingsPE(Reference_Date,Investment,Holding,Price) VALUES ('12/31/2019','Example Fund 1','Microsoft',55);
select * from UholdingsPE


Reference_Date
Investment
Holding
Price


2017-03-31
Example Fund 1
Apple
1

2017-03-31
Example Fund 1
Microsoft
1

2017-06-30
Example Fund 1
Apple
1

2017-06-30
Example Fund 1
Microsoft
1

2017-09-30
Example Fund 1
Apple
1

2017-09-30
Example Fund 1
Microsoft
1

2017-12-31
Example Fund 1
Apple
1

2017-12-31
Example Fund 1
Microsoft
1

2018-06-30
Example Fund 1
Apple
22

2018-06-30
Example Fund 1
Microsoft
22

2018-12-31
Example Fund 1
Apple
33

2018-12-31
Example Fund 1
Microsoft
33

2019-06-30
Example Fund 1
Apple
44

2019-06-30
Example Fund 1
Microsoft
44

2019-12-31
Example Fund 1
Apple
55

2019-12-31
Example Fund 1
Microsoft
55


DECLARE @MaxDate Date = '20191231'  --Typically this would be the max Reference_Date grouped by Fund as we cannot pass parameters in our BI tool

declare @quarter_end_date date = (select min(Reference_Date) from UholdingsPE)
declare @start_date date = @quarter_end_date

declare cur cursor fast_forward for select distinct Investment, Holding from UholdingsPE

declare @Investment varchar(100), @Holding varchar(10)
declare @latest_holdings int

open cur
fetch next from cur into @Investment, @Holding
while @@FETCH_STATUS = 0
begin
	while (@quarter_end_date <= convert(date, @MaxDate))
	begin
		if ((select count(1) from UholdingsPE where Reference_Date = @quarter_end_date and Investment = @Investment and Holding = @Holding) = 0)
		begin
			set @latest_holdings = (select top 1 Price from UholdingsPE where Reference_Date <= @quarter_end_date and Investment = @Investment and Holding = @Holding order by Reference_Date desc)
			insert into UholdingsPE select @quarter_end_date, @Investment, @Holding, @latest_holdings
		end
		set @quarter_end_date = eomonth(dateadd(mm, 3, @quarter_end_date))
	end
	set @quarter_end_date = eomonth(dateadd(mm, 3, @start_date))
	fetch next from cur into @Investment, @Holding
end
close cur
deallocate cur

select * from UholdingsPE


Reference_Date
Investment
Holding
Price


2017-03-31
Example Fund 1
Apple
1

2017-03-31
Example Fund 1
Microsoft
1

2017-06-30
Example Fund 1
Apple
1

2017-06-30
Example Fund 1
Microsoft
1

2017-09-30
Example Fund 1
Apple
1

2017-09-30
Example Fund 1
Microsoft
1

2017-12-31
Example Fund 1
Apple
1

2017-12-31
Example Fund 1
Microsoft
1

2018-03-31
Example Fund 1
Apple
1

2018-03-31
Example Fund 1
Microsoft
1

2018-06-30
Example Fund 1
Apple
22

2018-06-30
Example Fund 1
Microsoft
22

2018-09-30
Example Fund 1
Apple
22

2018-09-30
Example Fund 1
Microsoft
22

2018-12-31
Example Fund 1
Apple
33

2018-12-31
Example Fund 1
Microsoft
33

2019-03-31
Example Fund 1
Apple
33

2019-03-31
Example Fund 1
Microsoft
33

2019-06-30
Example Fund 1
Apple
44

2019-06-30
Example Fund 1
Microsoft
44

2019-09-30
Example Fund 1
Apple
44

2019-09-30
Example Fund 1
Microsoft
44

2019-12-31
Example Fund 1
Apple
55

2019-12-31
Example Fund 1
Microsoft
55

Reference_Date	Investment	Holding	Price
2017-03-31	Example Fund 1	Apple	1
2017-03-31	Example Fund 1	Microsoft	1
2017-06-30	Example Fund 1	Apple	1
2017-06-30	Example Fund 1	Microsoft	1
2017-09-30	Example Fund 1	Apple	1
2017-09-30	Example Fund 1	Microsoft	1
2017-12-31	Example Fund 1	Apple	1
2017-12-31	Example Fund 1	Microsoft	1
2018-06-30	Example Fund 1	Apple	22
2018-06-30	Example Fund 1	Microsoft	22
2018-12-31	Example Fund 1	Apple	33
2018-12-31	Example Fund 1	Microsoft	33
2019-06-30	Example Fund 1	Apple	44
2019-06-30	Example Fund 1	Microsoft	44
2019-12-31	Example Fund 1	Apple	55
2019-12-31	Example Fund 1	Microsoft	55