Need to Impute Missing Data from Sparsely Populated Table

Question

I am trying to populate a #temp reporting table from an existing sparse table with 2 key elements-- datekeys and prices based on a date range and prices as they change. Here is the data that exists in the price change table:

The date range for a report 2 days ago on a rolling 7 day cycle would include 6/12 - 6/19. The 2 rows in the table have an old price and old price variance from 6/7 out of range for that report, however, the price and variance is needed in order to dub it in under the CashPrice column for all datekeys 20190612 through 20190618. On 6/19 there is a new price change/variance. On that datekey, the new price/variance values should change.

The set-up data required for reporting would like this:

Here is the code to build the sampling data:

-- T-SQL script to build the sampling tables

-- use for date-related cross-join later
if object_id( N'tempdb..#Numbers', N'U' ) is not null 
drop table #Numbers;

create table #Numbers(
   n int
 );

insert #Numbers( n ) values( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), 
( 7 ), ( 8 ), ( 9 ), ( 10 ) 


-- select * from #Numbers;

-- creating existing sparse price data
if object_id( N'tempdb..#dt', N'U' ) is not null 
 drop table #dt;

create table #dt(
  StoreNumber int
  , City char( 3 )
  , State char( 2 )
  , Type char( 1 )
  , ProductKey int
  , DateKey int
  , CashPrice money
  , DateLastPriceChange datetime
  , CashPriceVar money
 )

 insert #dt values
    ( 1, 'OKC', 'OK', 'D', 144, 20190607, 2.799, '2019-06-07 11:37', -0.1 )
  , ( 1, 'OKC', 'OK', 'D', 144, 20190619, 2.699, '2019-06-19 10:40', -0.1 )

-- select * from #dt;

-- creaing temporary working table for reporting
if object_id( N'tempdb..#tt', N'U' ) is not null 
  drop table #tt;

create table #tt(
  StoreNumber int
, City char( 3 )
, State char( 2 )
, Type char( 1 )
, ProductKey int
, DateKey int
-- a couple of extra columns here
, DateKeyDate date
, DataDateKey int
, CashPrice money
, DateLastPriceChange datetime
, CashPriceVar money
)

-- dub in the start date for the report
declare @StartDateKey date = '2019-06-12'

-- populate the temporary working table
insert #tt( StoreNumber, ProductKey, DateKeyDate )
 select distinct
   dt.StoreNumber
 , dt.ProductKey
 , dateadd( day, n.n, @StartDateKey )
 from #dt dt
  cross join #Numbers n
 where
  n.n <= 7

-- select * from #tt

-- change the added datekeydate to datekey format
update #tt
set DateKey = year( DateKeyDate ) * 10000 + month( DateKeyDate ) * 100 + 
    day( DateKeyDate )

Here is the code that I have been working on that limps along. It's not ideal and in many cases against the full suite of data, it filters out the dates I cross-joined, so that I am left without the imputed data-- showing only the original known price changes. Please advise.

select 
dd.StoreNumber
, dto.City
, dto.State
, dto.Type
, dd.ProductKey
, dd.DateKey
, dto.CashPrice
, dto.DateLastPriceChange
, dto.CashPriceVar
from (
 select 
    tt.StoreNumber
    , tt.ProductKey
    , tt.DateKey
    , max( dt.DateKey ) MaxDateKey
 from #tt tt
  inner join #dt dt
    on dt.StoreNumber = tt.StoreNumber
    and dt.ProductKey = tt.ProductKey
    and dt.DateKey <= tt.DateKey
 group by
    tt.StoreNumber
    , tt.ProductKey
    , tt.DateKey
) dd
inner join #dt dto
    on dto.StoreNumber = dd.StoreNumber
    and dto.ProductKey = dd.ProductKey
    and dto.DateKey = dd.MaxDateKey;

EDIT: Can anyone advise as to whether or not the SQL Server LEAD function might be a better choice--perhaps comparing the value of the current row with the value of the following row?

Need to Impute Missing Data from Sparsely Populated Table

0 Answers