How can I generate the previous table of values ​​by combining fields from an SQL table?

Question

Say I have some data in a SQL Server database.

Location   PayID    Year
------------------------
Loc1        100     2010
Loc1        100     2011
Loc1        101     2012
Loc2        200     2010
Loc2        201     2011
Loc2        202     2012

And I'm trying to write a query in SQL Server that will give me a two-column table that I can find to find out what was previous PayIDfor a specific location. Thus, the output will be.

PayID   PrevID
-----------------
101     100
202     201
201     200

He only needs a record when the identifier of the previous year differs from the current year, and I will request it recursively if I do not have a correct match when the user returns for more than one year, so he will pull out each previous identifier based on the one that was just pulled until she finds the PayID and Year corresponding to the first table.

. , .

Answer 1

CTE:

with cte as (
  select Location, PayID, PayID as PrevID, Year from payhistory
  union all
  select p.Location, p.PayID, cte.PayID as PrevID, p.Year
  from payhistory p
  join cte on cte.Location = p.Location and cte.Year + 1 = p.Year
)
select distinct Location, PayID, PrevID
from cte
where PayID <> PrevID;

, :

| LOCATION | PAYID | PREVID |
|----------|-------|--------|
|     Loc1 |   101 |    100 |
|     Loc2 |   201 |    200 |
|     Loc2 |   202 |    201 |

: http://www.sqlfiddle.com/#!3/e0ac0/4

Answer 2

, LAG 2012 . , . LAG (PayID, 1, NULL) LAG (PayID, 1, PayID), .

DECLARE @tbl TABLE (Location VARCHAR(4),   PayID INT,    Year INT)
INSERT INTO @tbl VALUES
 ('Loc1',100,2010)
,('Loc1',100,2011)
,('Loc1',101,2012)
,('Loc2',200,2010)
,('Loc2',201,2011)
,('Loc2',202,2012)

SELECT Location 
      ,PayID
      ,LAG(PayID,1,NULL) OVER (PARTITION BY Location ORDER BY Year ASC) PrevID
  FROM @tbl

http://www.sqlfiddle.com/#!6/e0ac0/2

Answer 3

- , PayIds .

select distinct l1.payId as PayId, 
l2.payId as PrevId
from locs as l1
inner join locs as l2
on l1.location = l2.location
and l1.payid = (l2.payid + 1)

Answer 4

The following solution is non-recursive and can provide better performance:

DECLARE @Payment TABLE (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    Location    VARCHAR(50) NOT NULL,
    PayID       INT NOT NULL,
    [Year]      SMALLINT NOT NULL
);
INSERT @Payment
SELECT 'Loc1', 100, 2010
UNION ALL SELECT 'Loc1', 100, 2011
UNION ALL SELECT 'Loc1', 101, 2012
UNION ALL SELECT 'Loc2', 200, 2010
UNION ALL SELECT 'Loc2', 201, 2011
UNION ALL SELECT 'Loc2', 202, 2012


SELECT  z.Location, z.GroupID,
        MAX(CASE WHEN z.RowType = 1 THEN z.[Year] END)  AS CurrentYear,
        MAX(CASE WHEN z.RowType = 0 THEN z.[Year] END)  AS PreviousYear,
        MAX(CASE WHEN z.RowType = 1 THEN z.[PayID] END) AS CurrentPayID,
        MAX(CASE WHEN z.RowType = 0 THEN z.[PayID] END) AS PreviousPayID
FROM
(
    SELECT  y.PayID, y.[Location], y.[Year],
            -- It "groups" rows two by two: current row and previous row will have the same GroupID
            (ROW_NUMBER() OVER(PARTITION BY y.Location ORDER BY y.RowNum + n.Num ASC) + 1) / 2 AS GroupID,
            -- RowType: 1=Current row, 0=Previous row
            ROW_NUMBER() OVER(PARTITION BY y.Location ORDER BY y.RowNum + n.Num ASC) % 2 AS RowType 
    FROM 
    (
        SELECT  x.Location, x.[Year], x.PayID, ROW_NUMBER() OVER(PARTITION BY x.Location ORDER BY x.[Year] DESC) RowNum
        FROM    @Payment x
    ) y
    -- For every location, it duplicates every row except the last one
    INNER JOIN (VALUES (1), (2)) n(Num) ON y.RowNum = 1 AND n.Num = 1 OR y.RowNum > 1 
) z
GROUP BY z.Location, z.GroupID
HAVING  MAX(CASE WHEN z.RowType = 1 THEN z.[Year] END) = MAX(CASE WHEN z.RowType = 0 THEN z.[Year] END) + 1
AND     MAX(CASE WHEN z.RowType = 1 THEN z.[PayID] END) <> MAX(CASE WHEN z.RowType = 0 THEN z.[PayID] END)
ORDER BY z.Location;

Output:

Location  GroupID CurrentYear PreviousYear CurrentPayID PreviousPayID
--------- ------- ----------- ------------ ------------ -------------
Loc1      1       2012        2011         101          100
Loc2      1       2012        2011         202          201
Loc2      2       2011        2010         201          200