How do we populate missing values from last available data?

• T.Ashish

  SSCarpal Tunnel

  Points: 4248

  More actions

  May 19, 2022 at 6:46 am

  Go to Answer

  #4035413

  Hi There,

  I am working on a query where I need to convert some amounts based on date, as (amount*conversion_factor).

  If conversion factor is not available for any date than I should pick the factor from last available date.

  Eg. For dates 01-01-2022, 02-01-2022, 03-01-2022. If conversion factor is not available for 02 and 03 than factor of 01 should be used.

  I have attached the DDL/DML statements here for reference. In attached example, conversion factor for 2022-05-06 and 2022-05-07 are not available thus we are supposed to pick the last available factor from date 2022-05-05.

  I tried using Ranking functions, LAG function, <= in Left Join and few other things as well but no success as of now.

  Any help will be much appreciated.

  Thanks.

  Attachments:

  You must be logged in to view attached files.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  May 19, 2022 at 7:29 am

  Answer

  #4035423

  Not very elegant, but I think that this works:

  WITH c1
  AS (SELECT *
            ,Ctr = IIF(conversion_factor IS NULL, 0, 1)
      FROM dbo.time_sheet_conversion)
      ,c2
  AS (SELECT *
            ,GrpNo = SUM(c1.Ctr) OVER (ORDER BY c1.log_date)
      FROM c1)
  SELECT c2.*
        ,ConversionFactorFilled = FIRST_VALUE(c2.conversion_factor) OVER (PARTITION BY c2.GrpNo ORDER BY c2.log_date)
  FROM c2;

  ---