ALTER PROCEDURE [dbo].[GET_PREDEFINED_MONTHLY_YIELD_PLANT_DATA_MAIN](@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 
-- standard xml protocol with get_prefefined_yield_daily_data for example
-- @author Milan Rompe
-- @version 1.9.4
-- @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 the function name to make sure only our update service will call the function
-- 2018-09-12 - MR: Fixed: yield calculation bases on energy meter power value
-- 2018-08-30 - MR: Fixed: uses ac power from energy meter if only available
-- 2018-06-19 - MR: Fixed empty parameter exceptions.
-- 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),
        @selectSQL nvarchar(2000),
        @ParamDefinition NVARCHAR(500),
        @udid nvarchar(100) = null,
        @uvid nvarchar(100) = null,
        @st datetime,
        @et datetime,
        @stUTC datetime,
        @etUTC datetime,
        @stStr varchar(50),
        @etStr varchar(50),
        @stTZ datetime,
        @stStrTZ varchar(50),
        @etStrTZ varchar(50),
        @stR datetime,
        @etR datetime,        
        @stStrR varchar(50), 
        @etStrR varchar(50),
        @interval integer = 1,
        @plantTZOffset integer,
        @plantTZDayLightSaving varchar(10),
        @dc_power_installed float,
        @mSt datetime, 
        @mEt datetime,
        @mStUTC datetime, 
        @mEtUTC datetime,
        @mStStr nvarchar(50),
        @mEtStr nvarchar(50),
        @ac_power_col varchar(100)

    CREATE TABLE #pdata (
                RowID INT IDENTITY ( 1 , 1 ),
                date datetime NOT NULL,
                yield bigint NULL,
                radyield float NULL,
                pr float NULL,
                targetyield bigint NULL
            );

    -- Fetch Timezone    
    SET @ParamDefinition = '@tsOUT integer OUTPUT'
    SET @sql = 'SELECT @tsOUT = cast(value AS integer) 
                FROM '+ @database + '.dbo.xxx_plant_device_properties 
                WHERE Value_Key = ''Time_Zone_Offset'''

    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @tsOUT = @plantTZOffset OUTPUT
    
    -- Fetch Day Light Saving        
    SET @ParamDefinition = '@dlsOUT varchar(10) OUTPUT'
    SET @sql = 'SELECT @dlsOUT = value 
                FROM '+ @database + '.dbo.xxx_plant_device_properties 
                WHERE Value_Key = ''plant.timezone.dst'''
    --print @sql
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @dlsOUT = @plantTZDayLightSaving OUTPUT
    
    print '@plantTZDayLightSaving' + @plantTZDayLightSaving        
    IF @plantTZDayLightSaving is NULL BEGIN
       SET @plantTZDayLightSaving = 'true' 
    END

    -- Setup startdate and enddate in utc
    SET @et = convert(datetime, @date, 120)
    -- reset hour, minute, seconds and millis
    SET @et = DATEADD(MONTH, datediff(MONTH, 0, @et), 0);

    -- add one day
    SET @mSt = @et;
    SET @et = DATEADD(MONTH, 1, @et);
    SET @mEt = @et;
    
    -- minus days for starttime
    SET @st = DATEADD(MONTH, -@month, @et);  

    print '-----request interval------'
    SET @stR = @st
    SET @etR = @et
    
    SET @stStrR = CONVERT(VARCHAR, @st, 120)
    SET @etStrR = CONVERT(VARCHAR, @et, 120)
    
    print @stStrR
    print @etStrR
    print '---------------------------'
    
    SET @stUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@st, @plantTZOffset, @plantTZDayLightSaving)
    SET @etUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@et, @plantTZOffset, @plantTZDayLightSaving)

    SET @stStr = CONVERT(VARCHAR, @stUTC, 120)
    SET @etStr = CONVERT(VARCHAR, @etUTC, 120)

    print @stStr
    print @etStr

    SET @mStUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@mSt, @plantTZOffset, @plantTZDayLightSaving)
    SET @mEtUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@mEt, @plantTZOffset, @plantTZDayLightSaving)
    SET @stTZ = COMMONDB.DBO.GET_TZ_DATE_FROM_UTC_DATE_2(@mStUTC, @plantTZOffset, @plantTZDayLightSaving)


    SET @mStStr = CONVERT(VARCHAR, @mStUTC, 120)
    SET @mEtStr = CONVERT(VARCHAR, @mEtUTC, 120)

    print 'mSt: ' + @mStStr
    print 'mEt: ' + @mEtStr

    print 'SYSD 1: ' + convert(nvarchar, SYSDATETIME(), 21)

    -- CHECK CACHE CONTENT
    IF OBJECT_ID('tempdb..'+ @database + '_plant_monthly_data') IS NULL BEGIN
        print 'create tmp table'
        SET @sql = 'USE tempdb CREATE TABLE ' + @database + '_plant_monthly_data(
                               date datetime PRIMARY KEY,
                               yield bigint, 
                               radyield bigint, 
                               pr float, 
                               targetyield bigint, 
                               updatetime datetime)'
        EXECUTE sp_executesql
                @sql
        -- we need to fetch all data
    END
    ELSE BEGIN
        print 'SYSD 2: ' + convert(nvarchar, SYSDATETIME(), 21)
        print 'check if we can use cache'
        SET @ParamDefinition = '@countOUT integer OUTPUT'

        DECLARE @count integer = 12,
                @maxDate datetime

        SET @sql = 'SELECT @countOUT = count(date)
                    FROM tempdb..'+ @database + '_plant_monthly_data
                    WHERE date >= convert(datetime, ''' + @stStrR + ''', 21)
                      AND date  < convert(datetime, ''' + @etStrR + ''', 21);'
        print @sql
        EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @countOUT = @count OUTPUT
                
        SET @ParamDefinition = '@maxDateOUT datetime OUTPUT'
        SET @sql = 'SELECT @maxDateOUT = max(date)
                    FROM tempdb..'+ @database + '_plant_monthly_data'
        EXECUTE sp_executesql
                @sql,
                @ParamDefinition,
                @maxDateOUT = @maxDate OUTPUT
        
        --SET @count = null
        print 'count: ' + cast(@count as varchar)
        print 'maxDate: ' + convert(varchar, @maxDate, 21)
        
        
        IF @count <= @month AND @maxDate <= @etR
        BEGIN
            print 'tmp yield table exist. update only current month if the month is outdated.'

            SET @et = @mEt;
            SET @st = @mSt;

            SET @etUTC = @mEtUTC;
            SET @stUTC = @mStUTC;

            SET @stStr = @mStStr;
            SET @etStr = @mEtStr;

            --
            DECLARE @updatetime datetime,
                    @dirtydate  datetime

            SET @ParamDefinition = '@dirtyOUT datetime OUTPUT, 
                                    @updatetimeOUT datetime OUTPUT'

            -- returns the oldest date of dirty months. Those months need to be recalculated
            SET @sql = 'SELECT TOP 1 @dirtyOUT = date, @updatetimeOUT = updatetime FROM
                            (SELECT date, updatetime,
                                DATEPART(YEAR,updatetime) - DATEPART(YEAR,date) AS yeardiff,
                                DATEPART(MONTH,updatetime) - DATEPART(MONTH,date) AS monthdiff
                          FROM tempdb..'+ @database + '_plant_monthly_data) AS A
                        WHERE A.yeardiff = 0 AND A.monthdiff = 0
                        ORDER BY date ASC'
            print @sql
            EXECUTE sp_executesql
                    @sql,
                    @ParamDefinition,
                    @dirtyOUT = @dirtydate OUTPUT,
                    @updatetimeOUT = @updatetime OUTPUT
            print @sql
            -- @dirtydate is the date we need to update to.
            print '@dirtydate: ' + convert(varchar, @dirtydate, 21)
            print '@updatetime: ' + convert(varchar, @updatetime, 21)

            --check if the @dirtydate is the same month as @date
            IF DATEPART(YEAR, @dirtydate) = DATEPART(YEAR, @date) AND
               DATEPART(MONTH, @dirtydate) = DATEPART(MONTH, @date) BEGIN
                -- if the age of the @updatetime is below one day, do not update tmp
                -- table. Just return the current tmp tbl 

                IF DATEDIFF(mi, @updatetime, GETDATE()) < 1440 BEGIN

                    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
                    print 'use cached data only'
                    RETURN
                END
            END
            -- update the @stStr to the @dirtydate
            IF @dirtydate IS NOT NULL BEGIN
                SET @st = @dirtydate;
                SET @stUTC = COMMONDB.DBO.GET_UTC_DATE_FROM_TZ_DATE_2(@st, @plantTZOffset, @plantTZDayLightSaving)
                SET @stStr = CONVERT(VARCHAR, @stUTC, 120)
                SET @stStrTZ = CONVERT(VARCHAR, @stTZ, 120)
                
                print 'use @dirtydate as startdate'
            END
         END
         ELSE BEGIN
            print 'not using cache'
         END
         print 'SYSD 3: ' + convert(nvarchar, SYSDATETIME(), 21)
    END
    
    
    

    -- Fetch Radiation sensor value
    SET @uvid = NULL
    SET @udid = NULL
    
    -- fill #pdata table with timestamps
    print 'insert timestamps into #pdata table'
    
    ;WITH cSequence AS
    (
        SELECT
           @st AS st, 
           DATEADD(MONTH, @interval, @st) AS et
        UNION ALL
        SELECT
          et, 
          DATEADD(MONTH, @interval, et)
        FROM cSequence 
        WHERE DATEADD(MONTH, @interval, et) <= @et
    )
    
    INSERT INTO #pdata (date, yield, radyield, pr, targetyield)
    SELECT st, null, null, null, null FROM cSequence OPTION (MAXRECURSION 0);
    print 'SYSD 4: ' + convert(nvarchar, SYSDATETIME(), 21)
    --SELECT * FROM #pdata
       
    -- fetch radiation sensor
    SET @ParamDefinition = '@udidOUT nvarchar(100) OUTPUT,
                            @uvidOUT nvarchar(100) OUTPUT'

    SET @sql = 'SELECT @udidOUT = LEFT(col, CHARINDEX(''.'', col)-1), 
                       @uvidOUT = RIGHT(col, LEN(col)-CHARINDEX(''.'', col)) FROM
                (SELECT TOP 1 Value as col FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                 WHERE Value_Key=''plant.radiation.plane.device.value.power'' AND Value <> '''')x'
    EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT

    -- If there is no radiation sensor defined choose a random sensor
    IF @udid IS NULL OR @uvid IS NULL
    BEGIN
        print 'find radiation sensor' 
        SET @sql = 'SELECT TOP 1 @udidOUT = Table_Name, 
                                 @uvidOUT=Column_Name
                     FROM ' + @database + '.dbo.xxx_Virtual_Devices
                     WHERE Device_Type = ''RADIATION_SENSOR'' OR Device_Type=''METEO''  AND Device_Class_2 like ''%RADIATION%''
                     ORDER BY Virtual_Device_Name ASC'
        EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT
        
        print 'random radiation sensor:' + @udid + '.' + @uvid
        
    END
    print 'SYSD 5: ' + convert(nvarchar, SYSDATETIME(), 21)
    print 'Radiation: ' + @udid + '.' + @uvid
    -- if the @udid or @uvid is null we have no chance to get an radiation value
    IF @udid IS NOT NULL AND @uvid IS NOT NULL 
      BEGIN
        print 'fetch radiation power from: ' + @udid + '.' + @uvid 
        -- FETCH LAST RADIATION DATA
        SET @selectSQL =   'SELECT min(B.date) AS date, sum(B.value) AS value 
                            FROM (SELECT min(A.date) AS date, 
                                  round(avg(CASE WHEN A.value < 0 THEN 0 ELSE CASE WHEN A.value > 1600 THEN null ELSE A.value END END),2) AS value 
                                  FROM (SELECT Date_and_time AS date, ' + @uvid + ' AS value FROM ' + @database + '.dbo.' + @udid + '
                                  WHERE Date_and_time >= convert(datetime,  ''' + @stStr + ''',21)
                                    AND Date_and_time < convert(datetime, ''' + @etStr + ''',21) ) AS A 
                                  GROUP BY DATEPART(YEAR, A.date),
                                           DATEPART(MONTH, A.date),
                                           DATEPART(DAY, A.date),
                                           DATEPART(HOUR, A.date)
                                  ) AS B
                            GROUP BY 
                            DATEPART(YEAR, B.date),
                            DATEPART(MONTH, B.date)'

        print  @selectSQL
        SET @sql = 'UPDATE #pdata 
                      SET radyield = C.value
                    FROM (' + @selectSQL + ') AS C
                    WHERE DATEPART(YEAR, #pdata.date) = DATEPART(YEAR, C.date)
                      AND DATEPART(MONTH, #pdata.date) = DATEPART(MONTH, C.date) '
        --print @sql
        EXECUTE sp_executesql
               @sql
        print 'SYSD 6: ' + convert(nvarchar, SYSDATETIME(), 21)
    END
      -- FETCH YIELD data from energy meter if any is defined as plant energy meter
      SET @uvid = NULL
      SET @udid = NULL
      
      SET @sql = 'SELECT @udidOUT = LEFT(col, CHARINDEX(''.'', col)-1), 
                       @uvidOUT = RIGHT(col, LEN(col)-CHARINDEX(''.'', col)) FROM
                (SELECT TOP 1 Value as col FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                 WHERE Value_Key=''plant.em.export.device.value.yield'' AND Value <> '''')x'

      EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT
      
      
      print 'em yield: ' + @udid + '.' + @uvid
      
      DECLARE @invertACPower varchar(10),
              @ParamDefinition2 NVARCHAR(100),
              @invertSQL varchar(10),
              @isACPower int = 0
     
      -- If we dont have a energy meter we collect the ac power of each inverter
      print 'SYSD 7: ' + convert(nvarchar, SYSDATETIME(), 21)
      IF @udid IS NULL OR @uvid IS NULL BEGIN
      
         CREATE TABLE #inverter(
            udid nvarchar(100) NULL,
            ac_power_uvid nvarchar(64) NOT NULL)
      
         -- Try to use ac power from energy meter        
         print 'try to fetch energy meter ac power from plant parameter'
         -- FETCH AC_POWER data from energy meter if any is defined as plant energy meter
         SET @sql = 'SELECT @udidOUT = LEFT(col, CHARINDEX(''.'', col)-1), 
                        @uvidOUT = RIGHT(col, LEN(col)-CHARINDEX(''.'', col)) FROM
                 (SELECT TOP 1 Value as col FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                  WHERE Value_Key=''plant.em.export.device.value.acpower'' AND Value <> '''')x'
         EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @udidOUT = @udid OUTPUT,
            @uvidOUT = @uvid OUTPUT
         print @sql
      
         IF @udid is NULL OR @uvid IS NULL BEGIN
            SET @udid = 'xxx_Plant_Device'
            SET @uvid = 'Inverter_Power_AC'

            -- fetch all inverters from this plant   
            SET @sql = 'INSERT INTO #inverter
                     SELECT DISTINCT Table_Name as udid, Column_Name AS ac_power_uvid
                     FROM ' + @database + '.dbo.xxx_Virtual_Devices
                     WHERE Device_Type=''Inverter'' AND Device_Class=''P_AC'' AND (Device_Class_2=''P_AC'' OR Device_Class_2=''P_AC_TOTAL'')'
            EXECUTE sp_executesql
                    @sql                
         END
         ELSE BEGIN 
            print 'calculating power by energy meter ac power'
            INSERT INTO #inverter (udid, ac_power_uvid) VALUES(@udid,@uvid) 
         END 

         SET @isACPower = 1
      END
      ELSE BEGIN
        -- grep inverted flag for energy meter power data
        SET @ParamDefinition2 = '@invertOUT nvarchar(100) OUTPUT'

        SET @sql = 'SELECT TOP 1 @invertOut = Value FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                    WHERE Value_Key=''plant.em.export.device.value.acpower.invert'' AND Value <> '''''

        EXECUTE sp_executesql
            @sql,
            @ParamDefinition2,
            @invertOUT = @invertACPower OUTPUT

        IF @invertACPower = 'true' BEGIN
            SET @invertSQL = '*-1'
        END
        ELSE BEGIN
            SET @invertSQL = ''
        END
      END

      -- for yield calculation values are selected 
      IF @isACPower = 0 BEGIN
        print 'yield calculation by energy meter yield'
        SET @selectSQL = 'SELECT DATEADD(MONTH, datediff(MONTH, 0, B.date), 0) AS date, B.value 
                            FROM (SELECT MIN(A.date) AS date, 
                                           MAX(A.value) - MIN(A.value) AS value
                                    FROM (SELECT COMMONDB.DBO.GET_TZ_DATE_FROM_UTC_DATE_2(Date_and_time, 0, ''' + @plantTZDayLightSaving + ''') AS date, 
                                                 ' + @uvid + ' AS value 
                                            FROM ' + @database + '.dbo.' + @udid + ' 
                                            WHERE Date_and_time >= convert(datetime,  ''' + @stStr + ''',21)
                                              AND Date_and_time < convert(datetime, ''' + @etStr + ''',21)) AS A
                                    GROUP BY DATEPART(YEAR, A.date),
                                             DATEPART(MONTH, A.date)) AS B'
                                             
        SET @sql = 'UPDATE #pdata 
                      SET yield = A.value
                    FROM (' + @selectSQL + ') AS A
                    WHERE #pdata.date = A.date'
        EXECUTE sp_executesql
                @sql
      END
      ELSE BEGIN
        print 'SYSD 8: ' + convert(nvarchar, SYSDATETIME(), 21)
        print 'yield calculation by plant ac power'

        DECLARE data_cursor CURSOR LOCAL FAST_FORWARD FOR
            SELECT * FROM #inverter AS A
        OPEN data_cursor                            
        
        FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col
        print 'SYSD 9: ' + convert(nvarchar, SYSDATETIME(), 21)
        WHILE @@FETCH_STATUS = 0 BEGIN
            SET @selectSQL = 'SELECT min(B.date) as date, sum(B.ac_power)*0.25 AS value
                        FROM (SELECT MIN(A.date) AS date, AVG(ac_power) AS ac_power 
                                 FROM (SELECT Date_and_Time AS date, ' + @ac_power_col + ' AS ac_power
                                                FROM ' + @database + '.dbo.' + @udid + '
                                                WHERE Date_and_time >= CONVERT(datetime, ''' +  @stStr + ''', 21)
                                                AND Date_and_time < CONVERT(datetime, ''' + @etStr + ''',21) 
                                                AND ' + @ac_power_col + ' IS NOT NULL) AS A
                                       GROUP BY DATEPART(YEAR, A.date),
                                                DATEPART(MONTH, A.date),
                                                DATEPART(DAY, A.date),
                                                DATEPART(HOUR, A.date),
                                                (DATEPART(MINUTE, A.date) / 15) 
                        ) AS B
                        GROUP BY DATEPART(YEAR, B.date),
                                 DATEPART(MONTH, B.date)'
                                 
            print @selectSQL
            
            SET @sql = 'UPDATE #pdata 
                          SET yield = ISNULL(yield,0) + ISNULL(C.value,0)
                        FROM (' + @selectSQL + ') AS C
                        WHERE DATEPART(YEAR, #pdata.date) = DATEPART(YEAR, C.date)
                          AND DATEPART(MONTH, #pdata.date) = DATEPART(MONTH, C.date)'
            --print @sql
            EXECUTE sp_executesql
                    @sql
            
            FETCH NEXT FROM data_cursor INTO @udid, @ac_power_col
            print 'SYSD loop: ' + convert(nvarchar, SYSDATETIME(), 21)
        END
        print 'SYSD 10: ' + convert(nvarchar, SYSDATETIME(), 21)
      END
      --print @selectSQL
              
      -- CALCULATE PR ON #pdata
      SET @ParamDefinition = '@dataOUT integer OUTPUT'
            
      SET @sql = 'SELECT @dataOUT=cast(Value as float)/1000.0
                  FROM ' + @database + '.dbo.xxx_Plant_Device_Properties 
                  WHERE Value_Key=''plant.dc.installedpower.total'' '
      EXECUTE sp_executesql
              @sql,
              @ParamDefinition,
              @dataOUT = @dc_power_installed OUTPUT
      
      print 'plant dc power installed: ' + cast(@dc_power_installed AS varchar) + 'kWp'

      UPDATE #pdata SET pr = CASE WHEN radyield <= 0 THEN null ELSE round((yield/10.0/@dc_power_installed) / CASE WHEN radyield/1000.0 = 0 THEN 1 ELSE radyield/1000.0 END, 2) END
      UPDATE #pdata SET pr = null WHERE pr <= 0 OR pr > 100
      --UPDATE #pdata SET acpower = 0 WHERE acpower < 0
      UPDATE #pdata SET radyield = 0 WHERE radyield < 0 

      DECLARE @avgpr float, 
              @avgmonthlypr float
      
      SET @avgpr = (SELECT SUM(yield)/@dc_power_installed  / (SUM(radyield)) FROM #pdata)
      --TODO we need to check the plausibility of the pr. We can to that again the monthly PR
      SET @avgmonthlypr = (SELECT avg(CASE WHEN pr < 100 THEN pr ELSE null END)/100 FROM #pdata)
      IF @avgpr > @avgmonthlypr BEGIN
        print 'avg is greater then @avgmonthlypr'
        SET @avgpr = @avgmonthlypr
      END
      IF @avgpr > 100 BEGIN
        SET @avgpr = 100
      END

      UPDATE #pdata SET targetyield=radyield*@dc_power_installed*@avgpr
    
    
      print 'stStr: ' + @stStr
    
      SET @sql = 'DELETE FROM tempdb.dbo.' + @database + '_plant_monthly_data
                  WHERE (date >= convert(datetime, ''' + @stStrTZ + ''', 21)
                    AND date < convert(datetime, ''' + @etStrR + ''', 21))
                    OR date in (SELECT date FROM #pdata);'
                   
      print @sql                                        
      EXECUTE sp_executesql
              @sql

      -- insert new calculated data 
      SET @sql = 'INSERT INTO tempdb.dbo.' + @database + '_plant_monthly_data
                  (date, yield, radyield, pr, targetyield, updatetime) 
                  SELECT date, yield, radyield, pr, targetyield, GETDATE() FROM #pdata
                  WHERE date < convert(datetime, ''' + @etStrR + ''', 21) ' 
      EXECUTE sp_executesql
              @sql


      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
      print 'SYSD 11: ' + convert(nvarchar, SYSDATETIME(), 21)
END
