IBANK - PROJECT

??USE [CAPDB_CORP_SIT]

GO

/****** Object:  StoredProcedure [dbo].[usp_ProfitMarginCriteriaTempGetList]    Script Date: 4/28/2021 9:08:03 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Santisuk Kamlangyong>

-- Create date: <2020-01-09>

-- Description:	<Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[usp_ProfitMarginCriteriaMOUTempGetList]

	 @TempId	UNIQUEIDENTIFIER = NULL

	,@ProfitGroupCode NVARCHAR(10) = NULL

	,@TypeCode	NVARCHAR(MAX) = NULL

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	BEGIN

		SELECT   TempRunningId

				,RowNo

				,TempId	

				,IsCheck

				,pgmProfitId

				,pgmProMid

				,ProfitGroupCode

				,TypeCode

				,TypeDesc

				,DescCode

				,DescText

				,incFrom

				,incTo

				,profitIncome

				,profitRteCde

				,profitRteNam

				,recsts

				,createBy

				,createDte

				,createWrkstn

				,updateBy

				,updateDte

				,updateWrkstn

				,CountTMP

		FROM (

			SELECT	 CONVERT(INT,ROW_NUMBER() OVER(ORDER BY IsCheck DESC,TypeDesc ASC,incTo ASC)) AS RowNo

					,TempRunningId

					,TMP062.TempId	

					,IsCheck

					,TMP062.pgmProfitId

					,pgmProMid

					,ProfitGroupCode

					,TypeCode

					,TypeDesc

					,DescCode

					,DescText

					,incFrom

					,incTo

					,CONVERT(NVARCHAR(50),incFrom) +' - '+ CONVERT(NVARCHAR(50),incTo) AS profitIncome

					,TMP063.profitRteCde

					,TMP063.profitRteNam

					,recsts

					,createBy

					,createDte

					,createWrkstn

					,updateBy

					,updateDte

					,updateWrkstn

					,TMP.CountTMP

			FROM CBTRTMP062 (NOLOCK) AS TMP062

			LEFT JOIN (

				SELECT	 TempId

						,COUNT(1) AS CountTMP

				FROM CBTRTMP062

				WHERE TempId = @TempId

				AND ProfitGroupCode = @ProfitGroupCode

				AND (@TypeCode IS NULL OR TypeCode IN (SELECT VALUE FROM STRING_SPLIT(@TypeCode,',')))

				GROUP BY TempId

			) AS TMP ON TMP062.TempId = TMP.TempId

			LEFT JOIN (

				SELECT DISTINCT TempId

					,pgmMid

					,pgmProfitId

					,profitRteCde

					,profitRteNam 

				FROM CBTRTMP063 WHERE TempId = @TempId

			) AS TMP063 ON TMP062.TempId = TMP063.TempId AND TMP062.pgmProMid = TMP063.pgmMid AND TMP062.pgmProfitId = TMP063.pgmProfitId

			WHERE TMP062.TempId = @TempId

			AND ProfitGroupCode = @ProfitGroupCode

			AND (@TypeCode IS NULL OR TypeCode IN (SELECT VALUE FROM STRING_SPLIT(@TypeCode,',')))

			--ORDER BY IsCheck DESC,TypeDesc ASC

		) AS TMP

		--ORDER BY TMP.IsCheck,TMP.TypeDesc

	END

END