/****************************************************************************************
 Purpose:
 This code demonstrates that the estimated and actual execution plans in SQL Server can 
 be 100% INCORRECT and that the execution plan should only be relied on to provide hints
 as to what may be wrong with a query rather than an absolute indication.  This code runs
 in SQL Server 2005 only.

 The code creates 30 years worth of dates starting with 2000-01-01 using two different 
 methods.  The first method uses a recursive CTE and the second method uses a "Tally" 
 table.  The output of each method is directed to a "throw-away" variable to take 
 display delays out of the picture.

 Please check both the actual and estimated execution plans and compare the % of batch.

 Please see the following article on how to build a Tally table and how they can be used
 to replace certain While Loops.
 http://www.sqlservercentral.com/articles/T-SQL/62867/
****************************************************************************************/
SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by (Name with-held)
--=======================================================================================
  PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
DECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.

--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
    SET @DateVal = '2000-01-01'

;with mycte as
     (
       select @DateVal AS DateVal
       union all
       select DateVal + 1
         from    mycte    
         where   DateVal + 1 < DATEADD(yy, 30, @DateVal)
     )
select @BitBucket = d.dateval
from mycte d
OPTION (MAXRECURSION 0)

--===== Turn off the performance counters and print a separator =========================
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
  PRINT REPLICATE('=',90)
GO

--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
  PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
DECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.

--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
    SET @StartDate = '2000-01-01'

SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,30,@StartDate)))
        @BitBucket = @StartDate-1+t.N 
   FROM dbo.Tally t
  ORDER BY N

--===== Turn off the performance counters and print a separator =========================
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
  PRINT REPLICATE('=',90)
GO

========== Recursive method ==========

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 2, logical reads 65749, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 449 ms.
==========================================================================================
========== Tally table method ==========

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Tally'. Scan count 1, logical reads 20, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 27 ms.
==========================================================================================

CREATE TABLE dbo.UpdateGaps(
   MachineID int NOT NULL,
   RecordedDate datetime NOT NULL,
   Pressure float NULL,
 CONSTRAINT PK_UpdateGaps PRIMARY KEY CLUSTERED (MachineID,RecordedDate)
)

INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-02', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-03', 300);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-04', 300);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-05', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-06', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-07', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-08', 330);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-09', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-10', 300);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-02', 380);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-03', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-04', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-05', 350);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-06', 350);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-07', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-08', 0);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-09', 400);
INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-10', 0);
GO
select * from dbo.UpdateGaps;
go

with Base0Data as (
select
    MachineID,
    RecordedDate,
    Pressure,
    rn = row_number() over (partition by MachineID, case when Pressure = 0 then 0 else 1 end order by RecordedDate)
from
    dbo.UpdateGaps
)
,BaseData as (
select
    MachineID,
    RecordedDate,
    Pressure,
    GrpDate = dateadd(dd,-rn,RecordedDate),
    rn,
    rn1 = row_number() over (partition by MachineID, dateadd(dd,-rn,RecordedDate) order by dateadd(dd,-rn,RecordedDate))
from
    Base0Data
)
--select * from BaseData where Pressure = 0
--select
--    bd1.MachineID,
--    bd1.RecordedDate,
--    Pressure = case when bd1.Pressure = 0 then coalesce(bd2.Pressure, bd1.Pressure) else bd1.Pressure end
update up set
    Pressure = case when bd1.Pressure = 0 then coalesce(bd2.Pressure, bd1.Pressure) else bd1.Pressure end
from
    dbo.UpdateGaps up
    inner join BaseData bd1
        on up.MachineID = bd1.MachineID and up.RecordedDate = bd1.RecordedDate
    left outer join BaseData bd2
        on (bd1.MachineID = bd2.MachineID
            and dateadd(dd, -bd1.rn1, bd1.RecordedDate) = bd2.RecordedDate);
go

select * from dbo.UpdateGaps;
go

drop table dbo.UpdateGaps;
go