/*
Row-wise and direct access to relation
Cursors pp. 307-308
Temporary tables

*/



USE Northwind

SELECT * FROM Orders WHERE YEAR(OrderDate)=1998 AND
    MONTH(OrderDate) = 2

/*
The sales department ran a "sweetheart" promotion during
the month of February 1988. The customer who placed the 
14th order that month would receive a large discount on 
their next order.

List the OrderID, CustomerID, EmployeeID, and OrderDate
for the 14th order February 1988.

I think the correct row is 

10876	BONAP	7	1998-02-09 00:00:00.000

Can this be done with SQL? Is this a set-based operation?

At least two data-tier solutions:
A cursor and a temporary table

*/

SELECT OrderID, CustomerID, EmployeeID, OrderDate
     FROM Orders
     WHERE YEAR(OrderDate) = 1998 AND
           MONTH(OrderDate) = 2
     ORDER BY OrderDate

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM Orders
WHERE OrderID = 10876


EXEC GetOrderByOffSet 2, 1998, 14
--DROP PROC GetOrderByOffset
CREATE PROC GetOrderByOffset
   @Month AS INT,
   @Year AS INT,
   @Offset AS INT
AS
/*
Written by Dr. Pardue for ISC 561 Oct 5, 2006
Psuedo code:

Declare local variables
Declare, define, and open cursor
Priming fetch
Initialize accumulator variable
While not end of cursor
   If accumulator equals offset
      print (SELECT) row
   Fetch next row
   Increment accumulator variable      
end while

*/

BEGIN

  --Declare an accumulator variable
  DECLARE @RowCount INT

  --Declare local variables for cursor
  DECLARE @OrderID INT, @CustomerID NVARCHAR(10), @EmployeeID INT, @OrderDate DATETIME


  --Declare cursor
  DECLARE curOrder CURSOR

  --Define cursor
  FOR
     SELECT OrderID, CustomerID, EmployeeID, OrderDate
     FROM Orders
     WHERE YEAR(OrderDate) = @Year AND
           MONTH(OrderDate) = @Month
     ORDER BY OrderDate
  OPEN curOrder

  --Read first row (priming read...)
  FETCH curOrder
     INTO @OrderID, @CustomerID, @EmployeeID, @OrderDate

  SET @RowCount = 1

  WHILE @@FETCH_STATUS = 0 -- while not eof
  BEGIN

     IF (@RowCount = @OffSet)
        SELECT OrderID, CustomerID, EmployeeID, OrderDate
        FROM Orders
        WHERE OrderID = @OrderID

     FETCH curOrder --Read next row in cursor
        INTO @OrderID, @CustomerID, @EmployeeID, @OrderDate

     SET @RowCount = @RowCount + 1

  END

  --Garbage collection
  CLOSE curOrder
  DEALLOCATE curOrder
 
END

/*
Solution using temporary tables

My read of the literature is that cursors are much slower
Each FETCH is more-or-less equivalent to an individual SELECT.
 
BTW, SQL Server supports a TABLE data type that can be 
use instead of a temporary table.
In SQL Server 2005 and later, the newest version of this is called 
Common Table Expressions (CTEs).

*/

EXEC GetOrderByOffsetTemp 2, 1998, 14

--DROP PROC GetOrderByOffsetTemp

CREATE PROC GetOrderByOffsetTemp
   @Month AS INT,
   @Year AS INT,
   @Offset AS INT
AS
/*
Written by Dr. Pardue for ISC 561 Oct 5, 2006
Psuedo code:

Create temporary table
Load table
Select row by offset
Drop table

*/

BEGIN

  SET NOCOUNT ON

  CREATE TABLE #OrdersByOffset
  (
   RowOffset INT IDENTITY(1,1) PRIMARY KEY,
   OrderID INT NULL,
   CustomerID NVARCHAR(10) NULL,
   EmployeeID INT NULL,
   OrderDate DATETIME NULL
  )

  INSERT INTO #OrdersByOffset
     SELECT OrderID, CustomerID, EmployeeID, OrderDate
     FROM Orders
     WHERE YEAR(OrderDate) = @Year AND
           MONTH(OrderDate) = @Month
     ORDER BY OrderDate

  SELECT OrderID, CustomerID, EmployeeID, OrderDate 
  FROM #OrdersByOffset 
  WHERE RowOffSet = @OffSet

  --SELECT * FROM #OrdersByOffset

  DROP TABLE #OrdersByOffset

  SET NOCOUNT OFF

END

--Performance?
/*
Displays the number of milliseconds required to parse, compile, and execute 
each Transact-SQL statement after statements execute.
*/
SET STATISTICS TIME ON
EXEC GetOrderByOffSet 2, 1998, 14
EXEC GetOrderByOffsetTemp 2, 1998, 14
SET STATISTICS TIME OFF

/*
Another common row-wise operation at the data tier
is paging.

Here is at least one way to do it using a cursor.

*/

EXEC GetOrderByPage 2, 15

--DROP PROC GetOrderByPage
CREATE PROC GetOrderByPage
  @Page INT, --The page "number" to returned
  @RowsPerPage INT
AS
/*
Written by Dr. Pardue for ISC 561 Oct 5, 2006
Psuedo code:

This technique bascially searches for the 
endpoints to use in a SELECT statement

Declare local variables
Declare, define, and open cursor
Priming fetch
Initialize accumulator variable
While not end of cursor
   If accumulator equals FirstRow
      Store the first OrderID of page
   If accumulator equals LastRow
      Store the last OrderID of page
   Fetch next row
   Increment accumulator variable      
end while
Print (SELECT) page

*/

BEGIN

  DECLARE @FirstRow INT, @LastRow INT
  DECLARE @FirstOrderID INT, @LastOrderID INT
  SET @FirstRow = (@Page - 1) * @RowsPerPage
  SET @LastRow = (@Page * @RowsPerPage + 1)

  --Declare an accumulator variable
  DECLARE @RowCount INT

  --Declare local variables for cursor
  DECLARE @OrderID INT, @CustomerID NVARCHAR(10), @EmployeeID INT, @OrderDate DATETIME


  --Declare cursor
  DECLARE curOrder CURSOR

  --Define cursor
  FOR
     SELECT OrderID, CustomerID, EmployeeID, OrderDate
     FROM Orders
     ORDER BY OrderDate
  OPEN curOrder

  --Read first row (priming read...)
  FETCH curOrder
     INTO @OrderID, @CustomerID, @EmployeeID, @OrderDate

  SET @RowCount = 1

  WHILE @@FETCH_STATUS = 0 -- while not eof
  BEGIN

     IF (@RowCount - 1 = @FirstRow)
        SET @FirstOrderID = @OrderID

     IF (@RowCount = @LastRow)
        SET @LastOrderID = @OrderID

     FETCH curOrder --Read next row in cursor
        INTO @OrderID, @CustomerID, @EmployeeID, @OrderDate

     SET @RowCount = @RowCount + 1

  END

  --Garbage collection
  CLOSE curOrder
  DEALLOCATE curOrder

  SELECT OrderID, CustomerID, EmployeeID, OrderDate 
  FROM Orders
  WHERE OrderID >= @FirstOrderID AND OrderID < @LastOrderID
 
END

/*
Solution using temporary table.
*/

EXEC GetOrderByPageTemp 3, 10

--DROP PROC GetOrderByPageTemp

CREATE PROC GetOrderByPageTemp
  @Page INT, --The page "number" to returned
  @RowsPerPage INT
AS
/*
Written by Dr. Pardue for ISC 561 Oct 5, 2006
Psuedo code:

Create temporary table
Load temporarty table
Compute endpoints of page (exclusive)
Print (SELECT) page
*/

BEGIN
   SET NOCOUNT ON

  --Create a temporary table
  CREATE TABLE #OrdersByPage
  (
   RowOffset INT IDENTITY(1,1) PRIMARY KEY,
   OrderID INT NULL,
   CustomerID NVARCHAR(10) NULL,
   EmployeeID INT NULL,
   OrderDate DATETIME NULL
  )

  INSERT INTO #OrdersByPage
     SELECT OrderID, CustomerID, EmployeeID, OrderDate
     FROM Orders
     ORDER BY OrderDate

   DECLARE @FirstRow INT, @LastRow INT
   SET @FirstRow = (@Page - 1) * @RowsPerPage
   SET @LastRow = (@Page * @RowsPerPage + 1)

   SELECT OrderID, CustomerID, EmployeeID, OrderDate 
   FROM #OrdersByPage
   WHERE RowOffset > @FirstRow AND RowOffSet < @LastRow

   SET NOCOUNT OFF

   DROP TABLE #OrdersByPage

END


SET STATISTICS TIME ON
EXEC GetOrderByPage 2, 15
EXEC GetOrderByPageTemp 3, 10
SET STATISTICS TIME OFF

--Again, hands down, the cursor was slower...