--------------------------------------------------------------------
--Performance Measurement Version
--------------------------------------------------------------------
--Description : New Non Expanded Complex Query
--              Replaces the previous query, by a more efficient
--              Coalescing Operation
--Author      : Sameer Verkhedkar, Xianjin Yang
--Date        : Mar 7, 1997
--Update      : Dec. 4, 1997
--------------------------------------------------------------------
USE tempo
GO

DROP TABLE Emp10RES
GO

CREATE TABLE Emp10RES (EID       Integer,
                       StartDate datetime,
                       EndDate   datetime)
GO


  INSERT INTO Emp10RES
  SELECT E1.EID, E1.StartDate, E1.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate <= E1.StartDate AND
         E1.EndDate <= E2.EndDate
  UNION
  SELECT E1.EID, E1.StartDate, E2.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E1.StartDate > E2.StartDate AND
         E2.EndDate < E1.EndDate AND
         E1.StartDate < E2.EndDate
  UNION
  SELECT E1.EID, E2.StartDate, E1.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate > E1.StartDate AND
         E1.EndDate < E2.EndDate AND
         E2.StartDate < E1.EndDate
  UNION
  SELECT E1.EID, E2.StartDate, E2.EndDate
  FROM   Emp10 E1, Emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate >= E1.StartDate AND
         E2.EndDate <= E1.EndDate AND
         NOT (E2.StartDate = E1.StartDate AND
              E2.EndDate = E1.EndDate)

GO

DROP TABLE Emp10COA
GO

CREATE TABLE Emp10COA (EID       Integer,
                        StartDate datetime,
                        EndDate   datetime)
GO


DECLARE Emp10RES_cur CURSOR FOR
    SELECT EID, StartDate, EndDate
    FROM Emp10RES
GO

DECLARE @StartRec_EID Integer, @StartRec_StartDate datetime, @StartRec_EndDate datetime
DECLARE @PrevRec_EID Integer, @PrevRec_StartDate datetime, @PrevRec_EndDate datetime
DECLARE @CurrRec_EID Integer, @CurrRec_StartDate datetime, @CurrRec_EndDate datetime
DECLARE @resultCount Integer, @msecs Integer, @prnvar char(20) 
DECLARE @first_time datetime, @last_time datetime
SELECT @first_time = getdate()

  -- ASSUMES THAT THE TUPLES ARE SORTED IN THE TABLE,
  -- Which is accomplished by the UNION operator
  -- instead of UNION ALL

  -- Enable printing Put_Line Messages
  print "Start"


  SELECT @resultCount = (SELECT COUNT(*) FROM Emp10RES)
  SELECT @prnvar = convert(char(20),@resultCount)
  print "# of Records to Operate on : %1!", @prnvar




OPEN Emp10RES_cur

FETCH Emp10RES_cur INTO @PrevRec_EID, @PrevRec_StartDate, @PrevRec_EndDate
SELECT @StartRec_EID = @PrevRec_EID
SELECT @StartRec_StartDate = @PrevRec_StartDate
SELECT @StartRec_EndDate = @PrevRec_EndDate

FETCH Emp10RES_cur INTO @CurrRec_EID, @CurrRec_StartDate, @CurrRec_EndDate

WHILE (@@sqlstatus = 0)
BEGIN

        IF ((@StartRec_EID !=  @CurrRec_EID) OR (dateadd(day,1,@PrevRec_EndDate) < @CurrRec_StartDate))
                BEGIN
                INSERT INTO Emp10COA
                        VALUES (@StartRec_EID,@StartRec_StartDate,@PrevRec_EndDate)
                SELECT @StartRec_EID = @CurrRec_EID
                SELECT @StartRec_StartDate = @CurrRec_StartDate
                SELECT @StartRec_EndDate = @CurrRec_EndDate

                SELECT @PrevRec_EID = @StartRec_EID
                SELECT @PrevRec_StartDate = @StartRec_StartDate
                SELECT @PrevRec_EndDate = @StartRec_EndDate
                END


        IF (@CurrRec_EndDate > @PrevRec_EndDate)
                BEGIN
                SELECT @PrevRec_EID = @CurrRec_EID
                SELECT @PrevRec_StartDate = @CurrRec_StartDate
                SELECT @PrevRec_EndDate = @CurrRec_EndDate
                END

        FETCH Emp10RES_cur INTO @CurrRec_EID, @CurrRec_StartDate, @CurrRec_EndDate
END

INSERT INTO Emp10COA
        VALUES (@StartRec_EID, @StartRec_StartDate, @StartRec_EndDate)

CLOSE Emp10RES_cur


SELECT @last_time = getdate()
SELECT @msecs = datediff(millisecond, @first_time,  @last_time)
SELECT @prnvar = convert(char(20), @msecs)
print "[Emp10 COALESCE CURSORS] Elapsed Time %1! milliseconds", @prnvar


SELECT @resultCount = (SELECT COUNT(*) FROM Emp10RES)
SELECT @prnvar = convert(char(20),@resultCount)
print "Resulting Records : %1!", @prnvar



GO

