Look up in same table with different value reference sql server

Question

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)

What if there are multiple records with Btime that fits this condition? — Zohar Peled, Oct 29 '19 at 10:17
There is no SQL Server 15.0.18142; the latest version of SQL Server 15 (SQL Server 2019 Preview) is 15.0.1900.25. Tell us your version of SQL Server, not your SSMS build version. — Thom A, Oct 29 '19 at 10:17
Possible duplicate of [sql server convert seconds to dd hh mm ss](https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql). — Tim Biegeleisen, Oct 29 '19 at 10:22
Yes that is true, there are multiple records wiht Btime that fits this condition. — db_noob, Oct 29 '19 at 11:08

score 2 · Accepted Answer · answered Oct 29 '19 at 10:32

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;

Look up in same table with different value reference sql server

1 Answers1