USE [COMMONDB]
GO

ALTER PROCEDURE [dbo].[GET_SUN_AZIMUTH] ( @database varchar(50),  @starttimeUTC nvarchar(50), @endtimeUTC nvarchar(50), @interval nvarchar(10))
AS BEGIN

-- function returns a series of elevation for the given time interval in the given resolution
 
-- @author Milan Rompe
-- @version 1.0
--
-- @changed MR 2018-04-12 First implementation for getting a series of elevation

SET NOCOUNT ON

DECLARE @tsInterval integer,
        @utcSt datetime, 
        @utcEt datetime,
        @long real, 
        @lat real,
        @ParamDefinition NVARCHAR(100),
        @sql NVARCHAR(200)

SET @utcSt = CONVERT(datetime, @starttimeUTC, 120)
SET @utcEt = CONVERT(datetime, @endtimeUTC, 120)

print 'stUTC: ' + convert(nvarchar, @utcSt, 21)
print 'etUTC: ' + convert(nvarchar, @utcEt, 21)

SET @tsInterval = CAST(@interval AS integer);



;WITH cSequence AS
    (
        SELECT
           @utcSt AS date,
           DATEADD(MINUTE, @tsInterval, @utcSt) AS et
        UNION ALL
        SELECT
          et,
          DATEADD(MINUTE, @tsInterval, et)
        FROM cSequence
        WHERE DATEADD(MINUTE, @tsInterval, et) <= @utcEt
    )

SELECT date INTO #dateTable FROM cSequence OPTION (MAXRECURSION 0);


SET @ParamDefinition = '@valueOUT nvarchar(100) OUTPUT'
                                
SET @sql = 'SELECT TOP 1 @valueOut = cast(Value AS real) FROM ' + @database + '.dbo.xxx_Plant_Device_Properties
            WHERE Value_Key=''plant.location.longitude'''

EXECUTE sp_executesql
        @sql,
        @ParamDefinition,
        @valueOUT = @long OUTPUT


SET @sql = 'SELECT TOP 1 @valueOut = cast(Value AS real) FROM ' + @database + '.dbo.xxx_Plant_Device_Properties
            WHERE Value_Key=''plant.location.latitude'''

EXECUTE sp_executesql
        @sql,
        @ParamDefinition,
        @valueOUT = @lat OUTPUT

SELECT [date], round(COMMONDB.dbo.GET_Azimuth([date], @long, @lat), 2) as value FROM #dateTable  

END
