ALTER PROCEDURE [dbo].[GET_PREDEFINED_MONTHLY_YIELD_PLANT_DATA](@database varchar(50), @date varchar(50), @month integer) AS 
BEGIN
-- Function should be created in COMMONDB
-- This function calculates the total yield of a month for the amount of requested months. It also returns the Radiation Yield
-- for these months.  
-- in monthly resolution 
--
-- @author Milan Rompe
-- @version 1.9.1
-- @since 2017-01-26
-- @param date - day to be requested in plant time zone. Example: '2017-01-24 00:00:00' 
--             - hour minutes and seconds will be ignored
-- @param months - the amount of month to be return               
--
-- 2019-04-04 - MR: changed: if @date is null than the current utc date will be used
--                           if @month is null than the last 12 month will be returned
-- 2019-04-04 - MR: fixed: There is bug when recalculating the month if a day will be set which is not one of the current month.
--                         There is now a new function which only allows update if a bit has been set. This function is not allowed
--                         to update the yields any longer to make sure the data shown is always correct.
-- 24.10.2017 - MR: Uses different Radiation Sensors for random search: RADIATION_SENSOR,METEO, Device_Class_2
-- 04.10.2017 - MR: Fixed delete request which now uses all dates of pdata.
-- 26.09.2017 - MR: Requests the inverter directly and sums the 15 Minutes mean ac power values
-- 07.08.2017 - MR: Changed GET_TZ_DATE_FROM_UTC_DATE to GET_TZ_DATE_FROM_UTC_DATE_2 for implementation of day light saving 
-- 05.05.2017 - Fixed caching bug. Returns only the requested amount of month now.
-- 08.03.2017 - Fixed bug in returning result if the date is older than available cache data
-- 08.03.2017 - Fixed deletion of dirty month entries bug.
-- 07.02.2017 - Month will only called once a day. Fixed the last day of a month update bug.
-- 03.02.2017 - Added data cache. Current month will be recalculated each time. 

  SET NOCOUNT ON
  
  DECLARE @sql nvarchar(2000),
          @et datetime,
          @st datetime,
          @stStrR nvarchar(50),
          @etStrR nvarchar(50)
  
  
  IF @date IS NULL BEGIN
    SET @date = GETUTCDATE()
  END 
  
  IF  @month IS NULL BEGIN
    SET @month = 12
  END 
  
  SET @et = convert(datetime, @date, 120)
  SET @et = DATEADD(MONTH, datediff(MONTH, 0, @et), 0);
  SET @et = DATEADD(MONTH, 1, @et);
  
  
  -- minus days for starttime
  SET @st = DATEADD(MONTH, -@month, @et);
  
  
  SET @stStrR = CONVERT(VARCHAR, @st, 120)
  SET @etStrR = CONVERT(VARCHAR, @et, 120)
  
   
  SET @sql = 'SELECT date,
             yield/1000 AS yield,
             CAST(radyield AS nvarchar) AS radyield,
             CAST(pr AS nvarchar) AS pr,
             targetyield/1000 AS targetyield
             FROM tempdb.dbo.' + @database + '_plant_monthly_data
             WHERE date >= convert(datetime, ''' + @stStrR + ''', 21)
             AND date < convert(datetime, ''' + @etStrR + ''', 21);'
  EXECUTE sp_executesql
          @sql
END