How do we populate missing values from last available data?

  • 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.
  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply