IF EXISTS
(
	SELECT *
	FROM   sys.objects
	WHERE  object_id = OBJECT_ID(N'[dbo].[fn_GetCommentDrugRequestList]')
		   AND [type] IN(N'FN', N'IF', N'TF', N'FS', N'FT')
)
	BEGIN
		DROP FUNCTION [dbo].[fn_GetCommentDrugRequestList]
	END
GO
IF EXISTS
(
	SELECT *
	FROM   sys.objects
	WHERE  object_id = OBJECT_ID(N'[rds].[fn_GetCommentDrugRequestList]')
		   AND [type] IN(N'FN', N'IF', N'TF', N'FS', N'FT')
)
	BEGIN
		DROP FUNCTION [rds].[fn_GetCommentDrugRequestList]
	END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Anatoliy.Pokus
-- Create date: 08.09.2017
-- Description:	Возвращает заявочный лист
-- =============================================


CREATE FUNCTION [rds].[fn_GetCommentDrugRequestList]
(@ID uniqueidentifier
)
RETURNS uniqueidentifier
AS
	BEGIN
		RETURN
		(
			SELECT
			(
				SELECT TOP 1 [drl].[DrugApplicationListItemID]
				FROM         [RDS].[T_DRUG_APPLICATION_LIST_ITEM] AS [drl]
				join [RDS].T_DRUG_APPLICATION_LIST da on da.DrugApplicationListID = [drl].DrugApplicationList
				WHERE        [drl].[RequestCode] = [dd].[RequestCode]
							 AND da.[BeginDate]/*Элемент зс*/ <= [dr].[BeginPeriod]/*Заявка*/
							 AND da.[EndDate]/*Элемент зс*/ >= [dr].[EndPeriod]/*Заявка*/
							 AND ([dr].[RequestType] = [drl].[BenefitType])
			)
			FROM [RDS].T_DRUG_REQUEST_COMMENTS AS dd
				 JOIN [RDS].T_DRUG_REQUEST AS dr ON dd.DrugRequest = dr.DrugRequestID
			WHERE [dd].DrugRequestCommentsID = @ID
		)
	END
GO