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');

-- data di FINE
DECLARE @EndDate AS date = '20150531';
-- temp table per il ciclo
DECLARE @Operazioni AS TABLE
(
    IdOrig int NOT NULL
  , Periodicita char(3) NOT NULL
);

DECLARE @Id int = 0;
DECLARE @Periodicita char(3) = '';
DECLARE @RefData date;

DECLARE @MaxDate AS TABLE
(
    IdOrig int NOT NULL
  , Periodicita char(3) NOT NULL
  , UltimaData date NOT NULL
);

DECLARE @DateDaInserire AS table
(
    IdOrig int NOT NULL
  , Data date
);

-- pre popolamento 
INSERT INTO @Operazioni (IdOrig, Periodicita)
  SELECT IdOrig, Periodicita FROM #Originale;
  
-- impostazione massime date
INSERT INTO @MaxDate (IdOrig, UltimaData, Periodicita)
  SELECT O.IdOrig, MAX(R.Data), O.Periodicita FROM #Replicata R JOIN #Originale O ON O.IdOrig = R.OrigRef GROUP BY O.IdOrig, O.Periodicita;
    
WHILE (SELECT COUNT(1) FROM @MaxDate) > 0
BEGIN
  
  SELECT TOP 1 @Id = IdOrig, @Periodicita = Periodicita, @RefData = UltimaData FROM @MaxDate;

  DECLARE @Quit bit = 0;

  IF @Periodicita = 'Set'
  BEGIN
    -- settimanale, ripeto fino a EndDate
    WHILE @RefData <= @EndDate AND @Quit = 0
    BEGIN
      IF DATEADD(DAY, 7, @RefData) <= @EndDate
      BEGIN
        SET @RefData = DATEADD(DAY, 7, @RefData);
        INSERT INTO @DateDaInserire (IdOrig, Data)
        VALUES (@Id, @RefData);
      END
      ELSE
        SET @Quit = 1;
    END;      

    --UPDATE @MaxDate
    --SET MassimaData = @RefData
    --WHERE IdOrig = @Id;
    DELETE FROM @MaxDate WHERE IdOrig = @Id;
  END;

  SET @Quit = 0;

  IF @Periodicita = 'Men'
  BEGIN
    -- mensile, ripeto fino a EndDate
    WHILE @RefData <= @EndDate AND @Quit = 0
    BEGIN
      IF DATEADD(MONTH, 1, @RefData) < @EndDate
      BEGIN
        SET @RefData = DATEADD(MONTH, 1, @RefData);
        INSERT INTO @DateDaInserire (IdOrig, Data)
        VALUES (@Id, @RefData);
      END
      ELSE
        SET @Quit = 1;
    END;      

    --UPDATE @MaxDate
    --SET MassimaData = @RefData
    --WHERE IdOrig = @Id;
    DELETE FROM @MaxDate WHERE IdOrig = @Id;
  END;

END;

SELECT 'PRIMA', * FROM #Replicata R;

INSERT INTO #Replicata (OrigRef, Data, Desc2)
SELECT D.IdOrig, D.Data, O.Desc1 FROM @DateDaInserire D JOIN #Originale O ON O.IdOrig = D.IdOrig
LEFT JOIN #Replicata R ON R.OrigRef = D.IdOrig AND R.Data = D.Data
WHERE R.OrigRef IS NULL;

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

DROP TABLE #Originale;
GO
DROP TABLE #Replicata;
GO