Inserare date lipsa (O SOLUTIE):
--DROP TABLE Curs
CREATE TABLE Curs (DataCurs DATETIME PRIMARY KEY, CursBNR MONEY)
INSERT INTO Curs VALUES ('2007-01-01',3.8888)
INSERT INTO Curs VALUES ('2007-01-02',3.7777)
INSERT INTO Curs VALUES ('2007-01-03',3.5555)
--lipsa 4 si 5
INSERT INTO Curs VALUES ('2007-01-06',3.4000)
INSERT INTO Curs VALUES ('2007-01-07',3.3000)
INSERT INTO Curs VALUES ('2007-01-08',3.2000)
--lipsa 9, 10, 11, 12, 13
INSERT INTO Curs VALUES ('2007-01-14',3.1010)
INSERT INTO Curs VALUES ('2007-01-15',3.2020)
--reset table DELETE FROM Curs WHERE DAY(DataCurs) IN (4,5,9,10,11,12,13)
DECLARE @tbl TABLE (DataCurs DATETIME, NrZileDif INT)
INSERT INTO @tbl
SELECT T1.DataCurs , DATEDIFF(dd,T1.DataCurs,T2.DataCurs)-1 AS NrZileDif
FROM
(SELECT Q1.DataCurs , COUNT(*) AS Nr
FROM
(SELECT TOP 100 PERCENT DataCurs FROM Curs ORDER BY DataCurs) AS Q1
INNER JOIN
(SELECT TOP 100 PERCENT DataCurs FROM Curs ORDER BY DataCurs) AS Q2
ON Q1.DataCurs > Q2.DataCurs
GROUP BY Q1.DataCurs) AS T1
INNER JOIN
(SELECT Q1.DataCurs , COUNT(*) AS Nr
FROM
(SELECT TOP 100 PERCENT DataCurs FROM Curs ORDER BY DataCurs) AS Q1
INNER JOIN
(SELECT TOP 100 PERCENT DataCurs FROM Curs ORDER BY DataCurs) AS Q2
ON Q1.DataCurs > Q2.DataCurs
GROUP BY Q1.DataCurs) AS T2
ON T1.Nr+1 = T2.Nr AND DATEADD(dd,1,T1.DataCurs) <> T2.DataCurs
--un select cu datele lipsa
/*
SELECT Q.DataLipsa ,
(SELECT TOP 1 DataCurs FROM Curs WHERE DataCurs = (SELECT MAX(DataCurs) FROM Curs WHERE DataCurs < Q.DataLipsa )) AS DataReper,
(SELECT TOP 1 CursBNR FROM Curs WHERE DataCurs = (SELECT MAX(DataCurs) FROM Curs WHERE DataCurs < Q.DataLipsa )) AS CursReper
FROM
(
SELECT DATEADD(dd,1,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 1
UNION
SELECT DATEADD(dd,2,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 2
UNION
SELECT DATEADD(dd,3,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 3
UNION
SELECT DATEADD(dd,4,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 4
UNION
SELECT DATEADD(dd,5,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 5
UNION
SELECT DATEADD(dd,6,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 6
UNION
SELECT DATEADD(dd,7,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif = 7
) AS Q
*/
--datele lipsa sunt inserate in tabela Curs
INSERT INTO Curs (DataCurs,CursBNR)
SELECT Q.DataLipsa ,
(SELECT TOP 1 CursBNR FROM Curs WHERE DataCurs = (SELECT MAX(DataCurs) FROM Curs WHERE DataCurs < Q.DataLipsa )) AS CursReper
FROM
(
SELECT DATEADD(dd,1,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 1
UNION
SELECT DATEADD(dd,2,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 2
UNION
SELECT DATEADD(dd,3,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 3
UNION
SELECT DATEADD(dd,4,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 4
UNION
SELECT DATEADD(dd,5,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 5
UNION
SELECT DATEADD(dd,6,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif >= 6
UNION
SELECT DATEADD(dd,7,V.DataCurs) AS DataLipsa FROM @tbl V WHERE V.NrZileDif = 7
) AS Q
SELECT *
FROM Curs
ORDER BY DataCurs