IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[PHARM].[V_GROUPED_BILL_OF_GOODS]'))
DROP VIEW [PHARM].[V_GROUPED_BILL_OF_GOODS]
GO

/***** Object:  View [dbo].[V_GROUPED_BILL_OF_GOODS]    Script Date: 21.07.2020 *****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [PHARM].[V_GROUPED_BILL_OF_GOODS]
AS

WITH CTE AS
(
SELECT	ROW_NUMBER() OVER( PARTITION BY
			bg.MedicationForms,
			bg.PackQuantity,
			bg.DoseView,
			pt.PaymentType,
			--pt.DateBegin,
			--pt.DateEnd,
			(case when trn.ProductType = 0 then trn.CMnn end),
			(case when (trn.ProductType = 0 and mnn.NameRus = '~') or trn.ProductType <> 0 then trn.CTrnID end)
			ORDER BY bg.EhrRus2m69i03gtd DESC) AS ROW_N,
		Cast(
			Isnull(Cast((case when trn.ProductType = 0 then trn.CMnn else null end) as varchar(36)), '') + 
			Isnull(Cast((case when (trn.ProductType = 0 and mnn.NameRus = '~') or trn.ProductType <> 0 then trn.CTrnID else null end) as varchar(36)), '') + 
			Isnull(Cast(bg.DoseView as varchar(36)), '') + 
			Isnull(Cast(PackQuantity as varchar(36)), '') + 
			Isnull(Cast(MedicationForms as varchar(36)), '') +
			Isnull(Cast(PaymentType as varchar(36)), '')  
			--+Isnull(convert(varchar(10), DateBegin, 20), '') +
			--Isnull(convert(varchar(10), DateEnd, 20), '') 
			as varchar(256))   AS GroupedBillOfGoodsID, 
			bg.BillOfGoodsID,
			(case when trn.ProductType = 0 then trn.CMnn else null end) as CMnn,
			(case when (trn.ProductType = 0 and mnn.NameRus = '~') or trn.ProductType <> 0 then trn.CTrnID else null end) as CTrn,
			bg.DoseView,
			bg.PackQuantity,
			bg.MedicationForms, 
			pt.PaymentType,
			pt.DateBegin,
			pt.DateEnd
FROM [PHARM].T_BILL_OF_GOODS AS bg
join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
left join PHARM.T_C_MNN mnn on mnn.CMnnID = trn.CMnn
join PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE pt on pt.BillOfGoods = bg.BillOfGoodsID
where bg.Active = 1 and pt.DateBegin <= getdate() and (pt.DateEnd is null or pt.DateEnd >= getdate())
)
SELECT GroupedBillOfGoodsID, BillOfGoodsID as BillOfGoods, CMnn, CTrn, DoseView, PackQuantity, MedicationForms, PaymentType, DateBegin, DateEnd 
	FROM CTE
		WHERE ROW_N = 1
GO
