I am using MS SQL 2016, and I have a table which looks something like that (simplified)

 Object      Date         Amount  Owner  TypeOwner
  O1        20180101        10      A     X      
  O2        20180101        50      B     Y
  O1        20180101        30      C     X
  O1        20180203        25      B     Y
  O2        20180203        20      B     Y
  O1        20180206        15      A     X
  O2        20180206        20      A     X

Simply put, owners declare how much of an object they have, at a date they choose. What I am looking for is reconstruct the history of ownership of the objects with respect to a column like TypeOwner, i.e. each time a new data point is available for a owner, recompute the current ownership. So the output table should look something like

Object      Date         Total  TypeOwner  
  O1        20180101        40      X   
  O2        20180101        50      Y 

  O1        20180203        25      Y
  O2        20180203        20      Y
  O1        20180103        40      X    

  O1        20180206        45      X
  O2        20180206        20      X
  O1        20180206        25      Y
  O2        20180206        20      Y

Basically each time there is a new entry in the table, it should look at all the most recent entries for any other owners and recompute a grouped sum per Object, TypeOwner.

How I obtain the result table:

  • As of 2018-01-01, we know that A owns 10 O1, B owns 50 O2 and C owns 30 O1. So group X (A and C) owns 40 O1 and group Y (B) owns 50 O2. That's the first two lines of the results.
  • On 2018-02-03, we have new data for B. This overrides the past entry for that owner. So as of this date A still owns 10 O1, C still owns 30 O1 and now B owns 25 O1 and 20 O2. So group X (A and C) owns 40 O1, group Y (B) owns 25 O1 and 20 O2.
  • On 2018-02-06, we have new data for A. This overrides the past entry for that owner. As of this date, A now owns 15 O1 and 20 O2, C still owns 30 O1 (2018-01-01), and B still owns 25 O1 and 20 O2 (2018-02-03). Once again group X (A and C) owns 45 O1 and 20 O2, group Y (B) owns 25 O1 and 20 O2

I tried using various techniques like inner join/cross apply to select last records up to some date, but I am not sure what would be te most efficient solution here. The real table is on thousands of objects and owners, hence any speed gain would be great.

Thank you

  • can you explain the logic a bit more? How did the row number increase?
    – S3S
    Commented Nov 9, 2018 at 22:26
  • I added some comments for the result table. Basically when a owner declares its ownership, it overrides past value for this owner. We use the most recent record for each other owners. Let me know if this clarifies.
    – Loupiol
    Commented Nov 9, 2018 at 22:40
  • To me, it is still not clear. In the first example groups X and Y owns the values, second B, and in the third, what did A refresh? For example, if A refreshes, the date of refreshing will be always added correct? Can you show the exact data before a refresh and after a refresh, as precise as possible, for example for one owner. Commented Nov 9, 2018 at 22:47
  • I added the operations performed at every step to get to the result table. At any given point, only one record is used for any owner (the last available, which is supposed to represent all the ownership of the owner, any older data is obsolete).
    – Loupiol
    Commented Nov 9, 2018 at 23:07

1 Answer 1


This will get you the desired result set. It works by 1) creating the missing [object, date, owner] row combinations and then 2) doing a "data smear" to carry the most recent non-NULL amount forward. And 3) filter out any rows that did not have a previous amount to pull from.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 

    Object CHAR(2) NOT NULL,
    [Date] DATE NOT NULL,
    Amount INT NOT NULL,
    Owner CHAR(1) NOT NULL,
    TypeOwner CHAR(1) NOT NULL 
INSERT #TestData (Object, Date, Amount, owner, TypeOwner) VALUES
  ('01', '20180101', 10, 'A', 'X'),      
  ('02', '20180101', 50, 'B', 'Y'),
  ('01', '20180101', 30, 'C', 'X'),
  ('01', '20180203', 25, 'B', 'Y'),
  ('02', '20180203', 20, 'B', 'Y'),
  ('01', '20180206', 15, 'A', 'X'),
  ('02', '20180206', 20, 'A', 'X');

--SELECT * FROM #TestData;

    cte_cross_values AS (   -- 
        FROM (
            VALUES ('01'),('02') ) o (object)
            CROSS APPLY ( VALUES ('2018-01-01'),('2018-02-03'),('2018-02-06') ) d (date)
            CROSS APPLY ( VALUES ('A','X'),('c','X'),('B','Y') ) ot (owner, typeowner)
    cte_rolling_amtount AS (
            RollingAmount = CONVERT(INT, SUBSTRING(MAX(bv.bin_val) OVER (PARTITION BY cv.owner, cv.typeowner, cv.object ORDER BY cv.date), 4, 4)),
            cte_cross_values cv
            LEFT JOIN #TestData td
                ON cv.date = td.Date
                AND cv.object = td.Object
                AND cv.owner = td.Owner
                AND cv.typeowner = td.TypeOwner
            CROSS APPLY ( VALUES (CONVERT(BINARY(3), td.Date) + CONVERT(BINARY(4), td.Amount)) ) bv (bin_val)

    amount = SUM(ra.RollingAmount),
    cte_rolling_amtount ra
    ra.RollingAmount IS NOT NULL 


object date             amount      typeowner
------ ----------       ----------- ---------
01     2018-01-01       40          X
02     2018-01-01       50          Y
01     2018-02-03       40          X
01     2018-02-03       25          Y
02     2018-02-03       20          Y
01     2018-02-06       45          X
02     2018-02-06       20          X
01     2018-02-06       25          Y
02     2018-02-06       20          Y
  • Thanks, interesting article about the last non null values. I ended up doing a similar thing, but without creating the additional lines (for each date, using cross apply to gather all last entries).
    – Loupiol
    Commented Nov 17, 2018 at 19:11
  • @Loupiol - That's great, I'm glad you were able to apply the concepts to derive a working solution. For the sake of future generations, please update your OP with your final working solution. Commented Nov 18, 2018 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.