Look up in same table with different value reference sql server [closed]

I’d like some help with a code. I have TableA where Btime is sometimes 0.

  TableA

       ID    Dest   Orig    Bcode   Btime
       FB8E  GD     TR      KL      3600
       AC2A  BO     VR      KS      2700
       65B3  GD     AE      LH      7800
       AF85  NY     BU      NWG     3300
       B32A  BO     VR      KS      0
       B42L  NY     BU      NWG     0
       F66D  BO     VR      KS      2700
       N20S  NY     BU      NWG     3300

I need to get from the same table the value of Btime where Dest, Orig and Bcode are the same as in the row where Btime is 0 and convert Btime to HH:MM:SS

The result would be:

      ID    Dest    Orig    Bcode   Btime
      FB8E  GD      TR      KL      01:00:00
      AC2A  BO      VR      KS      00:45:00
      65B3  GD      AE      LH      02:10:00
      AF85  NY      BU      NWG     00:55:00
      B32A  BO      VR      KS      00:45:00
      B42L  NY      BU      NWG     00:55:00

How can I get this for my entire table if there are multiple values that can meet this condition?

Version is Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)

Answer

You can convert the values to a time for the format and use window functions to fill in the value:

select t.*,
       dateadd(second, new_btime, convert(time, 0)) as new_btime_formatted
from (select t.*,
             (case when btime = 0
                   then max(btime) over (partition by dest, orig, bcode)
                   else btime
              end) as new_btime
      from t
     ) t;