I use something like this to generate dates:


Declare @minDate date = '2018-03-01'
       , @maxDate date = '2018-03-06';

   With dates
     As (
Select n.id
      , DateValue = dateadd(day, n.id, @minDate)
   From (Select row_number() over(Order By ac.object_id) - 1 As id
           From sys.all_columns ac) As n
        )
Select *
   From dates d
Where d.DateValue <= @maxDate;


Now - you just need to get the row that applies for that date - and to do that we can use a CROSS APPLY:


Declare @product Table (
   Product char(1)
  , Location tinyint
  , TransDate DATE
  , TransBal int
   );

Insert @product (
   Product
  , Location
  , TransDate
  , TransBal
   )
Values ('A', 1, '02/01/2018', 50)
  , ('A', 1, '02/15/2018', 75)
  , ('A', 2, '03/01/2018', 25)
  , ('A', 2, '03/06/2018', 50)
  , ('B', 1, '03/01/2018', 25);

Select * From @product;

Declare @minDate date = (Select min(p.TransDate) From @product p)
  , @maxDate date = (Select max(p.TransDate) From @product p);

 With dates
  As (
Select n.id
  , DateValue = dateadd(day, n.id, @minDate)
 From (Select row_number() over(Order By ac.object_id) - 1 As id
    From sys.all_columns ac) As n
   )
Select pd.Product
  , pd.Location
  , d.DateValue As TransDate
  , pd.TransBal
 From dates    d
Cross Apply (Select Top 1
        *
      From @product p
      Where p.TransDate <= d.DateValue
      Order By
        p.TransDate desc) As pd
Where d.DateValue <= @maxDate;


If you already have a number or tally table - you can replace the derived table with the row_number function and use your number or tally table directly.

Edit: I missed part of the relationship in the cross apply - if multiple products can have the same transdate then you need to include that.  To do that you would change the query to:


With dates
As (
Select n.id
, DateValue = dateadd(day, n.id, @minDate)
From (Select row_number() over(Order By ac.object_id) - 1 As id
  From sys.all_columns ac) As n
 )
Select Distinct pd.Product
, pd.Location
, d.DateValue As TransDate
, pd.TransBal
From dates  d
Cross Join @product p
Cross Apply (Select Top 1
   *
  From @product p1
  Where p1.Product = p.Product And p1.TransDate <= d.DateValue
  Order By
   p1.TransDate desc) As pd
Where d.DateValue <= @maxDate;


The CROSS JOIN creates a row for each date - the WHERE in the CROSS APPLY then limits the value to the preceding product value - and the DISTINCT eliminates any duplicates generated by the cross join.