Update latest record with data from an older record

Question

I have a table with newspaper subscribers:

Subscribers:
==============
ID INT,
Status,
Address,
IndexAddress,
StartDate,
EndDate,
SubscriberID,
PaperID

IndexAddress is a reference to my internal Address table where I keep "correct" addresses (you woulnd't believe how many people don't know where they live). Address is the address supplied by the customer.

Each time a subscriber ends his subscription I save the data and when he renews his subscription I want to re-fetch the old IndexAddress from the old subscrption line in my table.

The data in the database can look like this:

1   1   MyLocalAddress  13455   20160101    20160501    100     5
8   1   MyLocalAddress  13455   20160820    20161201    100     5
14  1   MyLocalAddress  13455   20161228    20170107    100     5
18  0   MyLocalAddress  NULL    20170109    NULL        100     5

So ID 1, has status 1, a local address, pointing to address 13455 in my internal system, started 160101 and ended 160501 with customer number 100 and paper number 5.

The last row, ID 18 has just arrived in the database, I want to make sure I automatically find the IndexAddress number so I don't have to match it by hand, but I also want to make absolutlely sure that I fetch the information from the row with ID 14 since the older information in the database MIGHT be wrong (in this case it isn't but it might).

Here is my SQL to fix this:

UPDATE s SET
    Status = s2.Status,
    IndexAddress = s2.IndexAddress
FROM dbo.Subscribers s
JOIN dbo.Subscribers s2 ON s2.SubscriberID = s.SubscriberID
WHERE 1 = 1
    AND s.Status <> s2.Status
    AND s2.Status = 1
    AND s2.ID IN
    (
        SELECT
            MAX(s3.ID)
        FROM dbo.Subscribers s3
        WHERE 1 = 1
            AND s3.SubscriberID = s.SubscriberID
            AND s3.PaperID = s.PaperID
            AND s3.Status = 1
            AND s3.ID <> s.ID
    )
    -- Make sure it's the same customer. Customer number is checked in
    -- join above.
    AND s.PaperID = s2.PaperID
    AND s.Address = s2.Address

This works, but I wanted to know if the subquery approach was the best solution or is there a better approach?

I would like to deepen my understand of MS SQL and thus my questions.

Answer 1

This is not the answer you're looking for but it's not really suitable for a comment. I don't really agree with the design of the tables as you have redundant data. You shouldn't have to repeat data for address and indexaddress in Subscribers or do updates like you are doing.

I would suggest a design something like the below that would avoid you having to do updates like the one you are doing. The below code is re-runnable, so you can run and modify if required to test it.

-- user level information with 1 row per user - address should be linked here
CREATE TABLE #user
    (
      id INT ,
      name NVARCHAR(20) ,
      indexAddress INT
    )

-- all subscriptions - with calculated status compared to current date
CREATE TABLE #subscription
    (
      id INT ,
      startDate DATETIME ,
      endDate DATETIME ,
      staus AS CASE WHEN endDate < GETDATE() THEN 1
                    ELSE 0
               END
    )

-- table to link users with their subscriptions
CREATE TABLE #userSubscription
    (
      userId INT ,
      subscriptionId INT
    )


INSERT  INTO #user
        ( id, name, indexAddress )
VALUES  ( 1, N'bob', 13455 ),
        ( 2, 'dave', 55332 )

INSERT  INTO #subscription
        ( id, startDate, endDate )
VALUES  ( 1, '20160101', '20160201' ),
        ( 8, '20160820', '20161201' ),
        ( 14, '20161228', '20170107' ),
        ( 18, '20170109', NULL ),
        ( 55, '20170101', NULL );   

INSERT  INTO #userSubscription
        ( userId, subscriptionId )
VALUES  ( 1, 1 ) ,
        ( 1, 8 ) ,
        ( 1, 14 ) ,
        ( 1, 18 ) ,
        ( 2, 55 );

-- show active users
SELECT u.name ,
       u.indexAddress ,
       us.userId ,
       us.subscriptionId ,
       s.startDate ,
       s.endDate ,
       s.staus 
FROM  #user u
INNER JOIN #userSubscription us ON u.id = us.userId
INNER JOIN #subscription s ON s.id = us.subscriptionId
WHERE s.staus = 0 -- active

-- show inactive users
SELECT u.name ,
       u.indexAddress ,
       us.userId ,
       us.subscriptionId ,
       s.startDate ,
       s.endDate ,
       s.staus 
FROM  #user u
INNER JOIN #userSubscription us ON u.id = us.userId
INNER JOIN #subscription s ON s.id = us.subscriptionId
WHERE s.staus = 1 -- inactive

-- tidy up
DROP TABLE #subscription
DROP TABLE #user
DROP TABLE #userSubscription

Answer 2

I think your query is way over complicated:

with toupdate as (
      select s.*,
             lag(address) over (partition by subscriberid, paperid order by id) as prev_address,
             lag(status) over (partition by subscriberid, paperid order by id) as prev_status
      from dbo.Subscribers s
     ) 
update toupdate
    set address = prev_address,
        status = prev_status
    where address is null;