By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- AsOfDate MarketV(ColA) MarketV(ColB) Return(ColC) (ColD)
create table abc (
AsOfDate date not null,
ColA decimal (12,2) not null,
ColB decimal (12,2) not null,
ColC decimal (12,8) not null,
ColD int not null
)
;
insert abc values
({d '2021-05-25'}, 8891171.14, 8891171.14, -0.81166911, 123)
, ({d '2021-05-25'}, 14219713.92, 14219713.92, 2.12135625, 123)
, ({d '2021-05-25'}, 3102248.42, 3102248.42, 0.36907554, 123)
, ({d '2021-05-26'}, 8819004.25, 8819004.25, 0.88822511, 123)
, ({d '2021-05-26'}, 14521364.71, 14521364.71, 1.84104756, 123)
, ({d '2021-05-26'}, 3113698.06, 3113698.06, 0.44211577, 123)
;
with a as (
select AsOfDate
, ColA
, sum(ColB) over (partition by AsOfDate) as ColB
, ColC
from abc
where AsOfDate BETWEEN '2021-05-25' AND '2021-05-26'
and ColD like '123%'
)
select AsOfDate
, cast(round(sum(ColA * ColC / ColB), 4) as decimal(6,4)) as 'Monthly_Return'
from a
group by AsOfDate
;
| AsOfDate | Monthly_Return |
|---|---|
| 2021-05-25 | 0.9191 |
| 2021-05-26 | 1.3587 |