DROP VIEW IF EXISTS STOR.V_GROUP_REMAINS_ON_MNN;
CREATE VIEW STOR.V_GROUP_REMAINS_ON_MNN
AS
SELECT
	uuid_generate_v4() AS GroupRemainsOnMnnID
,	r.Storage
,	trn.CMnn
,	SUM(r.Amount) AS Amount
,	bg.MedicationForms
,	dv.value as DoseView
, (case when dv.value like '%¹%' then SUBSTRING(dv.value, 0,Charindex('¹', dv.value) -1) end) as Dose
,	r.ExpirationDate
,	bg.EhrRus9a9g09e8kl
FROM STOR.T_REMAINS AS r
inner join
	PHARM.T_BILL_OF_GOODS bg
	on bg.BillOfGoodsID = r.BillOfGoods
inner join
	PHARM.T_C_TRN trn
	on trn.CTrnID = bg.CTrn
left join
	PHARM.S_DLO_DoseView dv
	on dv.Id = bg.DoseView
WHERE r.Amount > 0
and trn.CMnn is not null
GROUP BY r.Storage, trn.CMnn, bg.MedicationForms, dv.value, r.ExpirationDate,bg.EhrRus9a9g09e8kl;