SET STATISTICS IO ON;
SET STATISTICS TIME ON;
--Mohammad Salimabadi Solution
WITH C0 AS
(
	SELECT Ph.*, I.item,
           ROW_NUMBER() OVER (PARTITION BY Ph.ItemId 
                               ORDER BY Ph.PriceStartDate) AS rownum 
      FROM PriceHistory AS Ph
           JOIN Items AS I
             ON Ph.itemid = I.itemid
), 
C1 AS
(
	SELECT itemid, item, PriceStartDate, price,
	       rownum + k AS grp_fct,
	       k
	  FROM C0 
           CROSS JOIN
           (
              VALUES (-1), 
                     ( 0), 
                     ( 1)
            ) AS D(k)
)
SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item,
       MAX(CASE WHEN k = 1 THEN price END) AS OldPrice,
       MAX(CASE WHEN k = 0 THEN price END) AS RangePrice,
       MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate,
       MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate
  FROM C1
 GROUP BY itemid, grp_fct 
HAVING MIN(PriceStartDate) < MAX(PriceStartDate);

--A Common Solution
WITH PriceCompare AS 
(
	SELECT  i.Item, 
	        ph.ItemId, ph.PriceStartDate, ph.Price, 
            ROW_NUMBER() OVER (PARTITION BY ph.ItemId 
                               ORDER BY PriceStartDate) AS rownum 
     FROM Items i 
          JOIN PriceHistory ph 
            ON i.ItemId = ph.ItemId
)
SELECT  currow.Item, 
        prevrow.Price AS OldPrice, 
        currow.Price AS RangePrice, 
        currow.PriceStartDate AS StartDate, 
        nextrow.PriceStartDate AS EndDate 
  FROM  PriceCompare currow 
        LEFT JOIN PriceCompare nextrow 
               ON currow.rownum = nextrow.rownum - 1 
              AND currow.ItemId = nextrow.ItemId 
        LEFT JOIN PriceCompare prevrow 
               ON currow.rownum = prevrow.rownum + 1 
              AND currow.ItemId = prevrow.ItemId;
                        
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

/*
--Mohammad Salimabadi Solutio:
Table 'PriceHistory'. Scan count 3, logical reads 6
Table 'Items'. Scan count 1, logical reads 2

--A Common Solution
Table 'PriceHistory'. Scan count 5, logical reads 48
Table 'Items'. Scan count 1, logical reads 2
*/

/*Matching Adjacent Rows based on a consecutive value*/

CREATE TABLE Nums
(nbr INTEGER NOT NULL PRIMARY KEY,
 val INTEGER NOT NULL);

 
INSERT INTO Nums (nbr, val)
VALUES (1, 0), (5, 7), (9, 4);

--===========Mohammad Salimabadi Solution #2
SELECT T2.*, T1.*, T3.*
  FROM Nums AS T1
       LEFT JOIN Nums AS T2
         ON T2.nbr = (SELECT MAX(nbr)
                        FROM Nums
                       WHERE nbr < T1.nbr)
       LEFT JOIN Nums AS T3
         ON T3.nbr = (SELECT MIN(nbr)
                        FROM Nums
                       WHERE nbr > T1.nbr); 

--==========Mohammad Salimabadi Solution #3
SELECT pre_nbr, N1.val AS pre_val,
       C.nbr AS cur_nbr, C.val AS cur_val,
       nxt_nbr, N2.val AS nxt_val
  FROM (
          SELECT MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END) AS pre_nbr,
                 N1.nbr, N1.val,
                 MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END) AS nxt_nbr       
            FROM Nums AS N1,
                 Nums AS N2
           GROUP BY N1.nbr, N1.val
       ) AS C
    LEFT JOIN Nums AS N1
       ON C.pre_nbr = N1.nbr
    LEFT JOIN Nums AS N2
       ON C.nxt_nbr = N2.nbr;
       
--=========Mohammad Salimabadi Solution #4
SELECT CAST(SUBSTRING(concat_pre, 1, 4) AS integer) AS pre_nbr,
       CAST(SUBSTRING(concat_pre, 5, 8) AS integer) AS pre_val,
       nbr, val,
       CAST(SUBSTRING(concat_nxt, 1, 4) AS integer) AS pre_nbr,
       CAST(SUBSTRING(concat_nxt, 5, 8) AS integer) AS pre_val       
  FROM (
          SELECT (
                    SELECT MAX(CAST(nbr AS BINARY(4)) + CAST(val AS BINARY(4)))
                      FROM Nums
                     WHERE nbr < T.nbr
                  ) AS concat_pre,      
                  nbr, val,
                 (
                    SELECT MIN(CAST(nbr AS BINARY(4)) + CAST(val AS BINARY(4)))
                      FROM Nums
                     WHERE nbr > T.nbr
                  ) AS concat_nxt       
             FROM Nums AS T
        ) AS D

C3 AS
(
SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item,
       MAX(CASE WHEN k = 1 THEN price END) AS OldPrice,
       MAX(CASE WHEN k = 0 THEN price END) AS RangePrice,
       MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate,
       MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate
  FROM C1
 GROUP BY itemid, grp_fct 
HAVING MIN(PriceStartDate) < MAX(PriceStartDate)
)
SELECT * FROM C3 where Item='Item 512'