SQL Server : Calculate Going Step (Yutthana Thanomklay), Sql Server

SQL Server : Calculate Going Step (Yutthana Thanomklay)

--/ Table Function สร้างตัวเลขเรียงลำดับ เป็น Row
CREATE FUNCTION [dbo].[fnGenNumber] (
@MinNumber INTEGER
, @MaxNumber INTEGER
)
RETURNS @ReturnTable TABLE (
NumberValue INTEGER
)
AS
BEGIN
;WITH TBL(NumberValue) AS
(
SELECT @MinNumber AS NumberValue

UNION ALL

SELECT TBL.NumberValue + 1 AS NumberValue
FROM TBL
WHERE TBL.NumberValue + 1 <= @MaxNumber
)
INSERT INTO @ReturnTable
SELECT *
FROM TBL
OPTION(MAXRECURSION 32767)

RETURN
END

--/ Table Function คำนวณการสลับตำแหน่งทั้งหมดที่เป็นไปได้ของจำนวน (Permutation)
CREATE FUNCTION [dbo].[fnListAllPermutation] (
@MaxNumber INTEGER
)
RETURNS @ReturnTable TABLE (
NumberOrder NVARCHAR(MAX) 
)
AS
BEGIN

;WITH PERMUTATION(NumberOrder, NumberTotal) AS
(
SELECT ',' + CONVERT(NVARCHAR(MAX), TBL.NumberValue) + ',' AS NumberOrder
, CONVERT(INTEGER, 1) AS NumberTotal
FROM fnGenNumber(1, @MaxNumber) AS TBL

UNION ALL

SELECT DT.NumberOrder + CONVERT(NVARCHAR(MAX), TBL.NumberValue) + ',' AS NumberOrder
, DT.NumberTotal + 1 AS NumberTotal
FROM PERMUTATION AS DT,
fnGenNumber(1, @MaxNumber) AS TBL
WHERE DT.NumberTotal < @MaxNumber
AND DT.NumberOrder NOT LIKE '%,' + CONVERT(NVARCHAR(MAX), TBL.NumberValue) + ',%'
)
INSERT INTO @ReturnTable
SELECT DT.NumberOrder AS NumberOrder
FROM PERMUTATION AS DT
WHERE DT.NumberTotal = @MaxNumber

RETURN
END

--/ Table Function เรียงลำดับข้อมูลใหม่ ตามลำดับ
CREATE FUNCTION [dbo].[fnReOrderNumber] (
@StepTotal INTEGER
, @NumberOrder NVARCHAR(MAX)
, @NumberList NVARCHAR(MAX)
)
RETURNS @ReturnTable TABLE (
NumberReOrder NVARCHAR(MAX) 
)
AS
BEGIN

;WITH TBL(ReplaceNumber, ReplaceWithIndexStart, ReplaceWithIndexEnd, ReplaceWith, NumberOrder) AS
(
SELECT 1 AS ReplaceNumber
, CHARINDEX(',', @NumberList, 0) AS ReplaceWithIndexStart
, CHARINDEX(',', @NumberList, CHARINDEX(',', @NumberList, 0) + 1) AS ReplaceWithIndexEnd
, ',[' + SUBSTRING(@NumberList, CHARINDEX(',', @NumberList, 0) + 1, CHARINDEX(',', @NumberList, CHARINDEX(',', @NumberList, 0) + 1) - CHARINDEX(',', @NumberList, 0) - 1) + '],' AS ReplaceWith
, REPLACE(@NumberOrder, ',1,', ',[' + SUBSTRING(@NumberList, CHARINDEX(',', @NumberList, 0) + 1, CHARINDEX(',', @NumberList, CHARINDEX(',', @NumberList, 0) + 1) - CHARINDEX(',', @NumberList, 0) - 1) + '],') AS NumberOrder

UNION ALL

SELECT TBL.ReplaceNumber + 1 AS ReplaceNumber
, CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) AS ReplaceWithIndexStart
, CHARINDEX(',', @NumberList, CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) + 1) AS ReplaceWithIndexEnd
, ',[' + SUBSTRING(@NumberList, CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) + 1, CHARINDEX(',', @NumberList, CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) + 1) - CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) - 1) + '],' AS ReplaceWith
, REPLACE(TBL.NumberOrder, ',' + CONVERT(NVARCHAR, TBL.ReplaceNumber + 1) + ',', ',[' + SUBSTRING(@NumberList, CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) + 1, CHARINDEX(',', @NumberList, CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) + 1) - CHARINDEX(',', @NumberList, TBL.ReplaceWithIndexEnd) - 1) + '],') AS NumberOrder
FROM TBL
WHERE TBL.ReplaceNumber < @StepTotal
)
INSERT INTO @ReturnTable
SELECT REPLACE(REPLACE(REPLACE(DT.NumberOrder, '],[', ','), ',[', ''), '],', '') AS NumberOrder
FROM TBL AS DT
WHERE DT.ReplaceNumber = @StepTotal

RETURN
END

--/ Store Procedure คำนวณตามโจทย์
CREATE PROCEDURE [dbo].[spCalculateStep]
@FinalValue INTEGER
, @StepValue1 INTEGER
, @StepValue2 INTEGER
, @StepValue3 INTEGER
AS
BEGIN

SELECT DISTINCT
ALL_CASE.FinalValue AS 'จำนวนทั้งหมด (ขั้น)'
, ALL_CASE.StepTotal AS 'ใช้ทั้งหมด (ขั้นตอน)'
, ALL_CASE.Step1Value AS 'กระโดดแบบที่ 1 (ขั้น)'
, ALL_CASE.Step1Total AS 'ใช้กระโดดแบบที่ 1 (จำนวน)'
, ALL_CASE.Step2Value AS 'กระโดดแบบที่ 2 (ขั้น)'
, ALL_CASE.Step2Total AS 'ใช้กระโดดแบบที่ 2 (จำนวน)'
, ALL_CASE.Step3Value AS 'กระโดดแบบที่ 3 (ขั้น)'
, ALL_CASE.Step3Total AS 'ใช้กระโดดแบบที่ 3 (จำนวน)'
, CALC.NumberReOrder AS 'ลำดับการกระโดด'
FROM (
SELECT DT.FinalValue
, DT.Step1Value
, VAL1.NumberValue AS Step1Total
, DT.Step2Value
, VAL2.NumberValue AS Step2Total
, DT.Step3Value
, VAL3.NumberValue AS Step3Total
, VAL1.NumberValue + VAL2.NumberValue + VAL3.NumberValue AS StepTotal
, ',' + REPLICATE(CONVERT(NVARCHAR, DT.Step1Value) + ',', VAL1.NumberValue)
+ REPLICATE(CONVERT(NVARCHAR, DT.Step2Value) + ',', VAL2.NumberValue)
+ REPLICATE(CONVERT(NVARCHAR, DT.Step3Value) + ',', VAL3.NumberValue) AS NumberList
FROM (
SELECT @FinalValue AS FinalValue
, @StepValue1 AS Step1Value
, @FinalValue / @StepValue1 AS Step1TotalMax
, @StepValue2 AS Step2Value
, @FinalValue / @StepValue2 AS Step2TotalMax
, @StepValue3 AS Step3Value
, @FinalValue / @StepValue3 AS Step3TotalMax
) AS DT
CROSS APPLY fnGenNumber(0, DT.Step1TotalMax) AS VAL1
CROSS APPLY fnGenNumber(0, DT.Step2TotalMax) AS VAL2
CROSS APPLY fnGenNumber(0, DT.Step3TotalMax) AS VAL3
WHERE DT.FinalValue = (DT.Step1Value * VAL1.NumberValue)
+ (DT.Step2Value * VAL2.NumberValue)
+ (DT.Step3Value * VAL3.NumberValue)
) ALL_CASE
CROSS APPLY fnListAllPermutation(ALL_CASE.StepTotal) AS ALL_SWAP
CROSS APPLY fnReOrderNumber(ALL_CASE.StepTotal, ALL_SWAP.NumberOrder, ALL_CASE.NumberList) AS CALC

END

--/ เรียกใช้งาน เพื่อทดสอบ
EXEC spCalculateStep 14 -- จำนวนทั้งหมด
, 2 -- กระโดดแบบที่ 1
, 3 -- กระโดดแบบที่ 2
, 4 -- กระโดดแบบที่ 3

--/ ลบ Function และ Store ทั้งหมด
DROP PROCEDURE spCalculateStep
DROP FUNCTION fnReOrderNumber
DROP FUNCTION fnListAllPermutation
DROP FUNCTION fnGenNumber


run \| edit \| history \| help	0