Reputation: 43
I'm having trouble wrapping my head around Multi statement table valued functions in SQL server, I want to use the query below in a function but unsure of how it should be done or if it can be done, any direction is greatly appreciated
DECLARE @start_time DATETIME ='2016-6-27 07:00:00';
DECLARE @end_time DATETIME ='2016-6-27 15:00:00';
DECLARE @address INT = 53;
BEGIN
SELECT (t1-totalhopper_01)* 0.002204622621 AS HPR1,(t2-totalhopper_02 )* 0.002204622621 AS HPR2,(t3-totalhopper_03 )*0.002204622621 AS HPR3,(t4-totalhopper_04 )*0.002204622621 AS HPR4,(t5-totalhopper_05)*0.002204622621 AS HPR5,(t6-totalhopper_06 )* 0.002204622621 AS HPR6
FROM(
SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY totalhopper_01 ASC) AS rownuma,TotalHopper_01,TotalHopper_02,TotalHopper_03,TotalHopper_04,TotalHopper_05,TotalHopper_06 FROM mm_Cycle_Data
WHERE TotalHopper_01 IS NOT NULL
AND TotalHopper_02 IS NOT NULL
AND TotalHopper_03 IS NOT NULL
AND TotalHopper_05 IS NOT NULL
AND TotalHopper_06 IS NOT NULL
AND t_stamp= (SELECT MIN(t_stamp) FROM mm_Cycle_Data WHERE get_address = @address AND T_stamp BETWEEN @start_time AND @end_time)
)a
LEFT JOIN(
SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY totalhopper_01 ASC) AS rownum,TotalHopper_01 AS T1,TotalHopper_02 AS t2,TotalHopper_03 AS t3 ,TotalHopper_04 AS t4,TotalHopper_05 AS t5,TotalHopper_06 AS t6 FROM mm_Cycle_Data
WHERE TotalHopper_01 IS NOT NULL
AND TotalHopper_02 IS NOT NULL
AND TotalHopper_03 IS NOT NULL
AND TotalHopper_05 IS NOT NULL
AND TotalHopper_06 IS NOT NULL
AND t_stamp= (SELECT MAX(t_stamp) FROM mm_Cycle_Data WHERE get_address = @address AND T_stamp BETWEEN @start_time AND @end_time)
)ac
ON rownuma=rownum;
Query result
HPR1 HPR2 HPR3 HPR4 HPR5 HPR6
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
5800.602419716689 0.000000000000 133.772091397038 0.000000000000 0.000000000000 0.000000000000
Upvotes: 0
Views: 59
Reputation: 25152
Here is a basic layout for you to follow using your code. Be sure to read up on Scalar and Table-valued functions to understand how they should be used.
CREATE FUNCTION [schema].[yourFUnctionname] (@start_time datetime, @end_time datetime, @address int)
RETURNS
@ReturnTable table(
column1 decimal(18,2),
column2 varchar(16)
--etc... do this for all return columns
)
AS
BEGIN
--DECLARE @start_time DATETIME ='2016-6-27 07:00:00';
-- DECLARE @end_time DATETIME ='2016-6-27 15:00:00';
-- DECLARE @address INT = 53;
-- BEGIN
INSERT INTO @ReturnTable (column1, column2) --make sure you use all the columns
SELECT (t1-totalhopper_01)* 0.002204622621 AS HPR1,(t2-totalhopper_02 )* 0.002204622621 AS HPR2,(t3-totalhopper_03 )*0.002204622621 AS HPR3,(t4-totalhopper_04 )*0.002204622621 AS HPR4,(t5-totalhopper_05)*0.002204622621 AS HPR5,(t6-totalhopper_06 )* 0.002204622621 AS HPR6
FROM(
SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY totalhopper_01 ASC) AS rownuma,TotalHopper_01,TotalHopper_02,TotalHopper_03,TotalHopper_04,TotalHopper_05,TotalHopper_06 FROM mm_Cycle_Data
WHERE TotalHopper_01 IS NOT NULL
AND TotalHopper_02 IS NOT NULL
AND TotalHopper_03 IS NOT NULL
AND TotalHopper_05 IS NOT NULL
AND TotalHopper_06 IS NOT NULL
AND t_stamp= (SELECT MIN(t_stamp) FROM mm_Cycle_Data WHERE get_address = @address AND T_stamp BETWEEN @start_time AND @end_time)
)a
LEFT JOIN(
SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY totalhopper_01 ASC) AS rownum,TotalHopper_01 AS T1,TotalHopper_02 AS t2,TotalHopper_03 AS t3 ,TotalHopper_04 AS t4,TotalHopper_05 AS t5,TotalHopper_06 AS t6 FROM mm_Cycle_Data
WHERE TotalHopper_01 IS NOT NULL
AND TotalHopper_02 IS NOT NULL
AND TotalHopper_03 IS NOT NULL
AND TotalHopper_05 IS NOT NULL
AND TotalHopper_06 IS NOT NULL
AND t_stamp= (SELECT MAX(t_stamp) FROM mm_Cycle_Data WHERE get_address = @address AND T_stamp BETWEEN @start_time AND @end_time)
)ac
ON rownuma=rownum
RETURN
END
GO
Upvotes: 1