CODEWITHSUNDEEP
sqlsql-server-2008
Jad
Jad

Reputation: 43

SQL FUNCTIONS returning a row

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

Answers (1)

S3S
S3S

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

Related Questions