Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Generating history of record change

    Hi,
    I recently got some help with DB2 SQL to perform a query, in this thread.

    It uses analytical functions to perform calculation of subtracting the value of a column in the current row from the previous row. When I run this in MSSQL 2008 I get the error " Incorrect syntax near 'ROWS'., SQL State: S0001, Error Code: 102", which I assume indicates that the ROWS syntax is not supported (I have removed the RTRIM etc.). Even cutting it down to a single (meaningless) select doesnt work, as the following gives the same error:

    Code:
    SELECT MIN(reservePkey) OVER(PARTITION BY reservePkey 
                            ORDER BY ReserveDate
                            ROWS BETWEEN 1 PRECEDING 
                            AND 1 PRECEDING) change
    FROM TOLReserve;
    Doe anyone know if there is an alternative approach or how to convert this query to work in MSSQL?

    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you describe what ROWS is supposed to do?
    What's the difference between
    Code:
    SELECT MIN(reservePkey) OVER(PARTITION BY reservePkey 
                            ORDER BY ReserveDate
                            ROWS BETWEEN 1 PRECEDING 
                            AND 1 PRECEDING) change
    FROM TOLReserve;
    and
    Code:
    SELECT MIN(reservePkey) OVER(PARTITION BY reservePkey 
                            ORDER BY ReserveDate) change
    FROM TOLReserve;
    ?

    I'm guessing your solution may require a derived table, but will no doubt be reproduceable.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2009
    Posts
    37
    Hi,
    Thanks for the reply. The ROWS keyword allows you to limit the number of rows that you are looking at within a partition. My ROWS criteria says to only look at the row immediately preceding the current row i.e. the previous one. To answer your question I will slightly modify the SQL to be the difference between

    Code:
    SELECT reservePkey, 
    MIN(reservePkey) OVER(PARTITION BY month(ReserveDate)
    ORDER BY ReserveDate
    ROWS BETWEEN 1 PRECEDING 
    AND 1 PRECEDING) min_val
    FROM TOLReserve;
    and

    Code:
    SELECT reservePkey, 
    MIN(reservePkey) OVER(PARTITION BY month(ReserveDate) 
    ORDER BY ReserveDate) min_val
    FROM TOLRESERVEHISTORY;
    The first will return the reservePkey of the current row and the min reservePkey within the partition, after the partition has been limited to just the previous row (i.e. the reservePkey of the previous row). This can be thought of as a form of "Sliding Window" over the data.

    The second query will return the reservePkey of the current row and the min reservePkey within the partition, with no limitation on the partition. Hence we get a repeated value for the min(reservePkey) as its always the same min value in a partition.

    For example:
    Code:
    reservePkey 		ReserveDate
    1			2009-11-09 
    131			2009-11-18 
    205			2009-11-26 
    209			2009-11-27 
    225			2009-12-07 
    226			2009-12-07 
    301			2009-12-14
    My result set would be
    Code:
    reservePkey	 min_val
    1		<null>
    131		1
    205		131
    209		205
    225		<null>
    226		225
    301		226
    My query partitions on the month, producing two partitions (as there are two distinct months). When generating the min(reservePkey) of each row, it also says to limit the partition size to just the previous row and take that reservePkey as the min_value. Hence the first record has a NULL value for min_val as it has no previous record in the partition. The min_val of the 2nd record is the reservePkey of the previous (i.e. 1st) record in the partition, while the min_val of the 3rd record is the reservePkey of the previous (i.e. 2nd) record in the partition etc. When we start a new partition, again the first has no previous so its min_val is NULL and the same pattern emerges as before.

    Meanwhile, your result set would be

    Code:
    reservePkey	 min_val
    1		1
    131		1
    205		1
    209		1
    225		225
    226		225
    301		225
    As you can see, your query partitions on the month also. Hence there are again two partitions (as there are two distinct months) and the min(reservePkey) of each is 1 and 255 respectively, as there is no ROWS keyword to "window" it. Hence, these values are repeated for each record within the partition, as compared to mine which is different for each record

    Does this make any sense?
    Last edited by FLANDERS; 01-04-10 at 13:47.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @your_table table (
       reservePkey int
     , ReserveDate datetime
    )
    
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (  1, '2009-11-09')
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (131, '2009-11-18')
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (205, '2009-11-26')
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (209, '2009-11-27')
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (225, '2009-12-07')
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (226, '2009-12-07')
    INSERT INTO @your_table (reservePkey, ReserveDate) VALUES (301, '2009-12-14')
    
    ; WITH cte AS (
      SELECT reservePkey
           , Dense_Rank() OVER (ORDER BY Year(ReserveDate), Month(ReserveDate)) As dr
           , Row_Number() OVER (ORDER BY ReserveDate) As row_num
      FROM   @your_table
    )
    SELECT a.reservePkey
         , b.reservePkey
    FROM   cte As a
     LEFT
      JOIN cte As b
        ON a.dr = b.dr
       AND b.row_num + 1 = a.row_num
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can swap out Dense_Rank() with Rank() (it may perform slightly better).
    Alternatively you can swap it out for Month() and Year() on the join *shrug*
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •