IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[STOR].[V_GROUP_REMAINS_ON_TRN]'))
DROP VIEW [STOR].[V_GROUP_REMAINS_ON_TRN]
GO
IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[STOR].[V_GROUP_REMAINS_ON_TRN]'))
DROP TABLE [STOR].[V_GROUP_REMAINS_ON_TRN]
GO

/****** Object:  View [dbo].[V_GROUP_REMAINS_ON_TRN]    Script Date: 20211018 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [STOR].[V_GROUP_REMAINS_ON_TRN]
AS
SELECT			newid() AS GroupRemainsOnTrnID
				,	Storage
				,	bg.CTrn
				,	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
				,	r.PackageType
FROM            [STOR].T_REMAINS AS r
				join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
				left join PHARM.S_DLO_DoseView dv on dv.Id = bg.DoseView
WHERE        (r.Amount > 0)
GROUP BY Storage, bg.CTrn, bg.MedicationForms, dv.value, r.ExpirationDate,bg.EhrRus9a9g09e8kl, r.PackageType
GO
