ALTER TABLE STOR.T_PATIENT_DRUG_DELIVERY ADD "totalapplicationcount" decimal(20, 6) NULL;

update stor.T_PATIENT_DRUG_DELIVERY pd
set TotalApplicationCount = pdd.Amount * rr.Amount
from stor.T_PATIENT_DRUG_DELIVERY pdd
CROSS JOIN LATERAL
(
	select rlpt.OrdinalNumber 
	from stor.T_REMAINS_LINK_PACKAGE_TYPE rlpt
	where rlpt.PackageType = pdd.PackageType and rlpt.Remains = pdd.Remains limit 1
) ptCurr
CROSS JOIN LATERAL
(
	select rlpt.OrdinalNumber 
	from stor.T_REMAINS_LINK_PACKAGE_TYPE rlpt
	where rlpt.WriteOff = '1' and rlpt.Remains = pdd.Remains limit 1
) ptWoF
CROSS JOIN LATERAL
(
	select exp(SUM(log(abs(rlpt.Amount)))) as Amount
	from stor.T_REMAINS_LINK_PACKAGE_TYPE rlpt
	where rlpt.Remains = pdd.Remains and rlpt.OrdinalNumber <= ptCurr.OrdinalNumber and rlpt.OrdinalNumber >= ptWoF.OrdinalNumber
	group by rlpt.remains
) rr
where pdd.PatientDrugDeliveryID = pd.PatientDrugDeliveryID and  pdd.Amount > 0;

update stor.T_PATIENT_DRUG_DELIVERY set TotalApplicationCount = Amount where TotalApplicationCount is null;