Sql For Dates With No Closeprice For All Companies

I have a fact table where I have ClosePrice for Company Tickers and TradingDates. I also have a dimCalendar table where all dates are there and a flag is maintained for Trading Ho

Solution 1:

The key idea is to generate the rows using a cross join and then fill in the values. In your case, you probably want to take into account that stocks may not exist at all points in the past, so you only want this for the minimum observed date.

To fill in the date,you can use lag(ignore nulls) in standard SQL:

select d.dateas tdate, d.datekey, t.ticker, 
       coalesce(fsdc.ClosePrice,
                lag(fsdc.ClosePrice ignore nulls) over (partition by t.ticker orderby d.date) as ClosePrice
from dimdates d join
     (select ticker, min(datekey) as min_date
      from factStockDividendCommodity fsdc
      groupby ticker
     ) t
     on d.datekey >= t.min_datekey left join
     factStockDividendCommodity fsdc
     on fsdc.ticker = t.ticker and
        fsdc.datekey = d.datekey
where d.Datekey between 20180101and20181231orderby d.Date;

Alas, many databases -- even those that support lag() -- do not support the ignore nulls option. The best approach then depends on the database. A correlated subquery is the most general method, but perhaps not the best from a performance perspective.

EDIT:

SQL Server does not support the IGNORE NULLS option. This is probably most easily handled using OUTER APPLY:

select d.dateas tdate, d.datekey, t.ticker, 
       fsdc.ClosePrice as ClosePrice
from dimdates d join
     (select ticker, min(datekey) as min_date
      from factStockDividendCommodity fsdc
      groupby ticker
     ) t
     on d.datekey >= t.min_datekey outer apply
     (select top (1) fsdc.*
      from factStockDividendCommodity fsdc
      where fsdc.ticker = t.ticker and
            fsdc.datekey <= d.datekey
      orderby fsdc.datekey desc
     ) fsdc
where d.Datekey between 20180101and20181231orderby d.Date;

However, because there are probably never more than 3 or 4 days without values in a row, a series of lag()s might be more efficient:

select d.dateas tdate, d.datekey, t.ticker, 
       coalesce(fsdc.ClosePrice,
                lag(fsdc.ClosePrice, 1) over (partition by t.ticker orderby d.date),
                lag(fsdc.ClosePrice, 2) over (partition by t.ticker orderby d.date),
                lag(fsdc.ClosePrice, 3) over (partition by t.ticker orderby d.date)
               ) as ClosePrice
from dimdates d join
     (select ticker, min(datekey) as min_date
      from factStockDividendCommodity fsdc
      groupby ticker
     ) t
     on d.datekey >= t.min_datekey left join
     factStockDividendCommodity fsdc
     on fsdc.ticker = t.ticker and
        fsdc.datekey = d.datekey
where d.Datekey between 20180101and20181231orderby d.Date;