none
Duda con Funciones en SQL RRS feed

  • Pregunta

  • Saludos, estoy realizando la siguiente función:

    CREATE FUNCTION [dbo].[Choras]
    ( 
    	@fecha datetime
    )
    RETURNS /* Que tipo de variable para varias variables */
    as
    BEGIN
    	declare @horasf float, @horash float, @horas525 float, @horas1030 float, @horas1535 float
    
    	set @horasf = (select cast(round(count(temperatura),1,0)/4.0 as float) as horasf from datos15minutos where temperatura < 7.2 and fecha = @fecha group by fecha)
    
    	if @horasf is null
    	begin
    		set @horasf = 0.0
    	end
    	
    	set @horash = (select cast(round(count(temperatura),1,0)/4.0 as float) as horash from datos15minutos where temperatura <= 0 and fecha = @fecha group by fecha)
    
    	if @horash is null
    	begin
    		set @horash = 0.0
    	end
        
    	set @horas525 = (select (round(avg(case when TEMPERATURA > 25 then 25 else TEMPERATURA end),1,0) - 5.0) as horas525 from datos15minutos where fecha = @fecha)
    
    	if @horas525 is null
    	begin
    		set @horas525 = 0.0
    	end
    
    	set @horas1030 = (select (round(avg(case when TEMPERATURA > 30 then 30 else TEMPERATURA end),1,0) - 10.0) as horas1030 from datos15minutos where fecha = @fecha)
    
    	if @horas1030 is null
    	begin
    		set @horas1030 = 0.0
    	end
    
    	set @horas1535 = (select (round(avg(case when TEMPERATURA > 35 then 35 else TEMPERATURA end),1,0) - 15.0) as horas1535 from datos15minutos where fecha = @fecha)
    
    	if @horas1535 is null
    	begin
    		set @horas1535 = 0.0
    	end
       
    			
    	Return /* regresar variables */
    END
    

    como se puede ver estoy haciendo diferentes cálculos cada unos guardado en una variable, mi duda es en la parte de return como lo realizo para que me regrese todas las variables cada una en una columna, no se si me explico.

    Gracias.



    José Israel Casas F.

    viernes, 24 de febrero de 2017 20:57

Respuestas

  • Israel Casas,

    Porque desconozco el tipo de la columna [fecha], pensé que podría ser datetime (por tanto contiene fecha y tiempo) y lo que hice es escribir una expresión que permita contener valores de fecha con independencia del tiempo. Si la columna [fecha] es de tipo date entonces la comparación que realizas es suficiente.

    Respecto a tu segunda pregunta puedes hacer lo siguiente:

    CREATE FUNCTION dbo.Choras
    ( 
    	@fecha date
    )
    RETURNS table
    AS
    RETURN
    (
        SELECT
    	   CASE WHEN T.horasf < 0 THEN 0 ELSE T.horasf END AS [horasf],
    	   CASE WHEN T.horash < 0 THEN 0 ELSE T.horash END AS [horash],
    	   CASE WHEN T.horas525 < 0 THEN 0 ELSE T.horas525 END AS [horas525],
    	   CASE WHEN T.horas1030 < 0 THEN 0 ELSE T.horas1030 END AS [horas1030],
    	   CASE WHEN T.horas1535 < 0 THEN 0 ELSE T.horas1535 END AS [horas1535]
        FROM    
    	   (SELECT
    		  COUNT(CASE WHEN d.temperatura < 7.2 THEN temperatura END) / 4.0 [horasf],
    		  COUNT(CASE WHEN d.temperatura <= 0 THEN temperatura END) / 4.0 [horash],
    		  AVG(CASE WHEN d.temperatura > 25 THEN 25 ELSE d.temperatura END) - 5 [horas525],
    		  AVG(CASE WHEN d.temperatura > 30 THEN 30 ELSE d.temperatura END) - 10 [horas1030],
    		  AVG(CASE WHEN d.temperatura > 35 THEN 35 ELSE d.temperatura END) - 15 [horas1535]
    	   FROM
    		  datos15minutos d
    	   WHERE
    		  fecha = @fecha) T
    )
    GO



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Marcado como respuesta Israel Casas lunes, 27 de febrero de 2017 22:06
    lunes, 27 de febrero de 2017 18:52
  • Israel Casas,

    Debes implementar una función que retorne un tipo table (funciones con valores de tabla).

    CREATE FUNCTION dbo.Choras
    ( 
    	@fecha date
    )
    RETURNS table
    AS
    RETURN
    (
        SELECT
    	   COUNT(CASE WHEN d.temperatura < 7.2 THEN temperatura END) / 4.0 AS [horasf],
    	   COUNT(CASE WHEN d.temperatura <= 0 THEN temperatura END) / 4.0 AS [horash],
    	   AVG(CASE WHEN d.temperatura > 25 THEN 25 ELSE d.temperatura END) - 5 AS [horas525],
    	   AVG(CASE WHEN d.temperatura > 30 THEN 30 ELSE d.temperatura END) - 10 AS [horas1030],
    	   AVG(CASE WHEN d.temperatura > 35 THEN 35 ELSE d.temperatura END) - 15 AS [horas1535]
        FROM
    	   datos15minutos d
        WHERE
    	   fecha >= @fecha AND fecha < DATEADD(DAY, 1, @fecha)
    )
    GO


    - Invocar

    SELECT * FROM dbo.Choras('20170101');
    GO

    Te recomiendo revisar las expresiones en la cláusula CASE WHEN porque son inclusivas, por ejemplo, la columna [horasf] considera incluso cuando la temperatura es menor o igual a cero cosa que entiendo debería ser exclusivo de la columna [horash], y lo mismo con las demás columnas.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    viernes, 24 de febrero de 2017 21:22

Todas las respuestas

  • Israel Casas,

    Debes implementar una función que retorne un tipo table (funciones con valores de tabla).

    CREATE FUNCTION dbo.Choras
    ( 
    	@fecha date
    )
    RETURNS table
    AS
    RETURN
    (
        SELECT
    	   COUNT(CASE WHEN d.temperatura < 7.2 THEN temperatura END) / 4.0 AS [horasf],
    	   COUNT(CASE WHEN d.temperatura <= 0 THEN temperatura END) / 4.0 AS [horash],
    	   AVG(CASE WHEN d.temperatura > 25 THEN 25 ELSE d.temperatura END) - 5 AS [horas525],
    	   AVG(CASE WHEN d.temperatura > 30 THEN 30 ELSE d.temperatura END) - 10 AS [horas1030],
    	   AVG(CASE WHEN d.temperatura > 35 THEN 35 ELSE d.temperatura END) - 15 AS [horas1535]
        FROM
    	   datos15minutos d
        WHERE
    	   fecha >= @fecha AND fecha < DATEADD(DAY, 1, @fecha)
    )
    GO


    - Invocar

    SELECT * FROM dbo.Choras('20170101');
    GO

    Te recomiendo revisar las expresiones en la cláusula CASE WHEN porque son inclusivas, por ejemplo, la columna [horasf] considera incluso cuando la temperatura es menor o igual a cero cosa que entiendo debería ser exclusivo de la columna [horash], y lo mismo con las demás columnas.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.

    viernes, 24 de febrero de 2017 21:22
  • Willams, gracias por tu respuesta, probando la consulta funciona perfectamente, pero tengo varias dudas.

    Una seria porque planteas de esta manera la fecha:

     fecha >= @fecha AND fecha < DATEADD(DAY, 1, @fecha)

    Ya que de esta manera:

    fecha = '15-02-2017' /* EJEMPLO */

    funciona también, pero no se cual es tu analogía para hacerlo como lo planteas o que mejora esta con tu propuesta.

    Tengo otra duda, como puedo validar si el resultado final de alguno de los campos no es menor a cero para que si es asi ponga cero como resultado.

    A tu comentario de las expresiones si están correctas aunque estas inclusivas, por cuestión de criterio de lo que se necesita pero es correcta tu observación.

    Saludos y buen inicio de semana.


    José Israel Casas F.

    lunes, 27 de febrero de 2017 18:23
  • Israel Casas,

    Porque desconozco el tipo de la columna [fecha], pensé que podría ser datetime (por tanto contiene fecha y tiempo) y lo que hice es escribir una expresión que permita contener valores de fecha con independencia del tiempo. Si la columna [fecha] es de tipo date entonces la comparación que realizas es suficiente.

    Respecto a tu segunda pregunta puedes hacer lo siguiente:

    CREATE FUNCTION dbo.Choras
    ( 
    	@fecha date
    )
    RETURNS table
    AS
    RETURN
    (
        SELECT
    	   CASE WHEN T.horasf < 0 THEN 0 ELSE T.horasf END AS [horasf],
    	   CASE WHEN T.horash < 0 THEN 0 ELSE T.horash END AS [horash],
    	   CASE WHEN T.horas525 < 0 THEN 0 ELSE T.horas525 END AS [horas525],
    	   CASE WHEN T.horas1030 < 0 THEN 0 ELSE T.horas1030 END AS [horas1030],
    	   CASE WHEN T.horas1535 < 0 THEN 0 ELSE T.horas1535 END AS [horas1535]
        FROM    
    	   (SELECT
    		  COUNT(CASE WHEN d.temperatura < 7.2 THEN temperatura END) / 4.0 [horasf],
    		  COUNT(CASE WHEN d.temperatura <= 0 THEN temperatura END) / 4.0 [horash],
    		  AVG(CASE WHEN d.temperatura > 25 THEN 25 ELSE d.temperatura END) - 5 [horas525],
    		  AVG(CASE WHEN d.temperatura > 30 THEN 30 ELSE d.temperatura END) - 10 [horas1030],
    		  AVG(CASE WHEN d.temperatura > 35 THEN 35 ELSE d.temperatura END) - 15 [horas1535]
    	   FROM
    		  datos15minutos d
    	   WHERE
    		  fecha = @fecha) T
    )
    GO



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Marcado como respuesta Israel Casas lunes, 27 de febrero de 2017 22:06
    lunes, 27 de febrero de 2017 18:52
  • Gracias por tu tiempo Willams, funciono perfectamente, ya agregue otras comparaciones que me faltaban y genero perfectamente la tabla.

    Un saludo.


    José Israel Casas F.

    lunes, 27 de febrero de 2017 22:08