Here is the case, every time I do the project, I need to use the company's calculating formula to get the result, but every project might base on different dataset, so I need to create a new stored procedure for each new project and modify calculation query to meet the requirement, then I start to think is it possible to create a stored procedure to deal with different dataset.
The raw dataset in the Excel looks as follows:
ID
|
C1
|
1
|
XXXX
|
2
|
XXXX
|
3
|
XXXX
|
4
|
XXXX
|
5
|
XXXX
|
6
|
XXXX
|
7
|
XXXX
|
8
|
XXXX
|
9
|
XXXX
|
10
|
XXXX
|
I found that it's very easy deal with the dataset which its row counts is even number in the SQL Server, I can use AVG(), ORDER BY, and sub-query to get result. But if I encounter the row counts is odd number, it's not very precisely calculate it in SQL Server, unless manually calculate in the Excel:
ID
|
C1
|
1
|
XXXX
|
2
|
XXXX
|
3
|
XXXX
|
4
|
XXXX
|
5
|
XXXX
|
6
|
XXXX
|
7
|
XXXX
|
8
|
XXXX
|
9
|
XXXX
|
10
|
XXXX
|
11
|
XXXX
|
I can get rough result if I still use the same query to calculate it, because the calculation will include complete single row value. Please see the following steps how to precisely calculate it with table value parameter in the stored procedure:
1.Create User-Defined Table type. The first column is sequence and incremental number, and the rest depend on how many columns I want to calculate.
USE [Target_DB]2.Create stored procedure. There are two parameters in this stored procedure, first one is table value variable created above, second one is used for controlling which range I want to calculate, or type ALL to return every range.
GO
CREATE TYPE [dbo].[Table_Value_Name] AS TABLE
(
[ID] [int] NOT NULL,
[C1] [float] NULL,
[C2] [float] NULL,
[C3] [float] NULL,
[C4] [float] NULL,
[C5] [float] NULL,
[C6] [float] NULL,
[C7] [float] NULL,
[C8] [float] NULL,
[C9] [float] NULL,
[C10] [float] NULL,
[C11] [float] NULL,
[C12] [float] NULL,
[C13] [float] NULL,
[C14] [float] NULL,
[C15] [float] NULL,
[C16] [float] NULL,
[C17] [float] NULL,
[C18] [float] NULL,
[C19] [float] NULL,
[C20] [float] NULL,
[C21] [float] NULL,
[C22] [float] NULL,
[C23] [float] NULL,
[C24] [float] NULL,
[C25] [float] NULL,
[C26] [float] NULL,
[C27] [float] NULL,
[C28] [float] NULL,
[C29] [float] NULL,
[C30] [float] NULL
)
GO
USE [Target_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
CREATE PROCEDURE [dbo].[Stored_Procedure_Name]
@T dbo.Table_Value_Name READONLY,
@Range VARCHAR(300)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @p INT,
@counts INT,
@loop_i INT,
@bm_records FLOAT,
@bm_records_int FLOAT,
@bm_records_decimal FLOAT,
@bp_records FLOAT,
@bp_records_int FLOAT,
@bp_records_decimal FLOAT,
@sql_cmd NVARCHAR(MAX),
@column NVARCHAR(50)
--============================================================
--Split @Range variable into temp table
IF RIGHT(@Range, 1) = ','
SET @Range = SUBSTRING(@Range, 1, LEN(@Range)-1)
DECLARE @range_tmp TABLE ( range_id INT )
DECLARE @range_counts INT,
@range_pos INT
SET @range_counts = 1
SET @range_pos = 0
WHILE CHARINDEX(',', @range, @range_pos) > 0
BEGIN
SET @range_pos = CHARINDEX(',', @range, @range_pos) + 1
SET @range_counts = @range_counts + 1
END
SET @range_pos = 0
WHILE @range_counts > 0
BEGIN
IF @range_counts = 1 --Get the last item.
INSERT INTO @range_tmp SELECT CONVERT(INT, RTRIM(LTRIM(SUBSTRING(@range, @range_pos, LEN(@range) - @range_pos + 1))))
ELSE
INSERT INTO @range_tmp SELECT CONVERT(INT, RTRIM(LTRIM(SUBSTRING(@range, @range_pos, CHARINDEX(',', @range, @range_pos)- @range_pos))))
SET @range_pos = CHARINDEX(',', @range, @range_pos) + 1
SET @range_counts = @range_counts - 1
END
--============================================================
--Retrieve column counts/structure of dbo.Table_Value_Name
CREATE TABLE #tmp_C_P (C NVARCHAR(5))
DECLARE @utcolumns TABLE (column_name NVARCHAR(30))
INSERT INTO @utcolumns
SELECT sac.name
FROM [sys].[table_types] stt
JOIN [sys].[all_columns] sac
ON stt.type_table_object_id = sac.object_id
WHERE stt.user_type_id = TYPE_ID('Table_Value_Name')
--SELECT * FROM @utcolumns
SELECT @counts = COUNT(*) - 1 FROM @utcolumns
--SELECT @counts
SELECT * INTO #tmp_P
FROM @T
SET @loop_i = 1
SET @sql_cmd = ''
--Use first row's value to control which column need to be calculated and returned
WHILE @loop_i <= @counts
BEGIN
SET @sql_cmd = @sql_cmd + 'IF (SELECT C'+CONVERT(NVARCHAR(2), @loop_i)+' FROM #tmp_P WHERE ID = 0) = 1 '
SET @sql_cmd = @sql_cmd + 'INSERT INTO #tmp_C_P VALUES(''C'+CONVERT(NVARCHAR(2), @loop_i)+''') '
SET @loop_i = @loop_i + 1
END
--SELECT @sql_cmd
EXEC(@sql_cmd)
SET @sql_cmd = ''
--SELECT * FROM #tmp_P
--SELECT * FROM #tmp_C_P
SELECT @counts = COUNT(*) FROM #tmp_P WHERE ID > 0
SET @p = 5
--This temp table help me to identify calculated base
DECLARE @p_range TABLE
( p INT,
range_value FLOAT,
range_value_int INT,
range_value_decimal FLOAT )
--SELECT * FROM @p_range
DECLARE @RV FLOAT,
@RVI INT,
@RVD FLOAT
--The following section of statement, I just explain which method/function I used, and I also need to consider about the NULL value inside the dataset, I tried to simulate SQL Server, if encounter NULL value then don't include the row. If you have any questions, please let me know.
--1. Identity which range I want to calculate, use IF...ELSE..., WHILE loop
--2. Create dynamic string for column structure of #tmp_P, use CURSOR, UNION ALL
--3. Ignore NULL value, use SUM(CASE WHEN ... IS NOT NULL THEN ... ELSE 0 END)
--4. Prevent divided by zero error, NULLIF(SUM(CASE WHEN...END), 0)
3.Demonstrate how to use this stored procedure.
DECLARE @T1 AS dbo.Table_Value_Name
--This part is used for control how many columns need to be calculated, the rest won't return to the result, in this case are C26 to C30
INSERT INTO @T1
(ID, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25)
VALUES(0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
INSERT INTO @T1
(ID, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25)
SELECT
--ID is used to reorder the dataset in the stored procedure
ROW_NUMBER() OVER (ORDER BY [Column_1] * [Column_2]),
[Column_1],
[Column_2],
[Column_3],
[Column_4],
[Column_5],
[Column_6],
[Column_7],
[Column_8],
[Column_9],
[Column_10],
[Column_11],
[Column_12],
[Column_13],
[Column_14],
[Column_15],
[Column_16],
[Column_17],
[Column_18],
[Column_19],
[Column_20],
[Column_21],
[Column_22],
[Column_23],
[Column_24],
[Column_25]
FROM [Original_DataSet]
INSERT INTO [Target_DateSet]
( [Title]
[Number_Of_DataSet],
[Column_1],
[Column_2],
[Column_3],
[Column_4],
[Column_5],
[Column_6],
[Column_7],
[Column_8],
[Column_9],
[Column_10],
[Column_11],
[Column_12],
[Column_13],
[Column_14],
[Column_15],
[Column_16],
[Column_17],
[Column_18],
[Column_19],
[Column_20],
[Column_21],
[Column_22],
[Column_23],
[Column_24],
[Column_25] )
EXEC dbo.Stored_Procedure_Name @T1, 'Range_String'
No comments:
Post a Comment