USE tempdb;
GO 

CREATE TABLE #Originale
(
  [IdOrig] [int] NOT NULL,
  [Periodicita] [nvarchar](3) NULL,
  [Desc1] [nvarchar](250) NULL
);

CREATE TABLE #Replicata
(
  [OrigRef] [int] NULL,
  [Data] date NULL,
  [Desc2] [nvarchar](250) NULL,
  [IdReplic] [int] IDENTITY(1,1) NOT NULL
);

INSERT #Originale ([IdOrig], [Periodicita], [Desc1]) 
VALUES 
(101, N'Set', N'scale'),
(102, N'Set', N'temperature'),
(103, N'Set', N'addolcitore'),
(104, N'Men', N'giardini'), 
(105, N'Set', N'spese varie');

INSERT #Replicata ([OrigRef], [Data], [Desc2]) 
VALUES 
(101, '20150331', N'scale'),
(101, '20150407', N'scale'),
(102, '20150406', N'temperature'),
(102, '20150413', N'temperature'),
(103, '20150425', N'addolcitore'),
(104, '20150310', N'giardini'),
(104, '20150410', N'giardini');

SELECT 'Prima', * FROM #Replicata R ORDER BY R.OrigRef, R.Data

-- data di FINE   La Data a cui voglio aggiornare la replicata
DECLARE @EndDate AS date = '20150531';   -- questo diventera GETDATE()

DECLARE @IdOrig  AS int               --  Il campo IdOrig di #Originale
DECLARE @Periodicita AS nvarchar(3)  --  Il campo Periodicita di #Originale
DECLARE @Desc1 AS nvarchar(250)      --  Il campo Desc1 di #Originale
DECLARE @MaxData AS datetime         --  Il Max(Data) di #Replicata 

DECLARE Curs1 CURSOR FOR
SELECT IdOrig, Periodicita, Desc1 FROM #Originale
OPEN Curs1
FETCH NEXT FROM Curs1
INTO @IdOrig, @Periodicita, @Desc1
WHILE @@FETCH_STATUS = 0
BEGIN

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

-- Originale(IdOrig, Periodicita, Desc1)   Replicata(OrigRef, Data, Desc2, IdReplic)  @EndDate

Ciclo:
SET @MaxData = (SELECT Max(Data) FROM #Replicata WHERE OrigRef=@IdOrig)

IF @MaxData IS NULL   -- Recupero i record che NON sono ancora presenti nella tabella #Replicata
INSERT INTO #Replicata (OrigRef, Data, Desc2) VALUES (@IdOrig, @EndDate, @Desc1 + ' <---Nuova')

IF @MaxData <= @EndDate
  BEGIN

    IF @Periodicita = 'Set'
    INSERT INTO #Replicata (OrigRef, Data, Desc2) VALUES (@IdOrig, DATEADD(DAY, 7, @MaxData), @Desc1 + ' <---Settimanale')

    IF @Periodicita = 'Men'
    INSERT INTO #Replicata (OrigRef, Data, Desc2) VALUES (@IdOrig, DATEADD(MONTH, 1, @MaxData), @Desc1 + ' <---Mensile')

    GOTO Ciclo
  END

-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

FETCH NEXT FROM Curs1
INTO @IdOrig, @Periodicita, @Desc1
END
CLOSE Curs1
DEALLOCATE Curs1



SELECT 'Dopo', * FROM #Replicata R ORDER BY R.OrigRef, R.Data

DROP TABLE #Originale;
GO
DROP TABLE #Replicata;
GO
-- Finito