Update SQL table based on column value-CodePudding

I have a table with different connection times and I am able to pick the latest connection time. However I want to update a column based on the column that the latest time is picked from. I am using the following case statement but the column keeps getting NA. Any advice on how I can do that? I want the latest_city to be based on the column that the latest connected time is showing

id fe_connected           ce_connected        ln_connected           jp_connected        latest_connected        latest_city
 1 2022-10-06 12:59:21  2022-11-07 11:30:00 2021-10-01 15:29:00   2020-12-14 13:01:54    2022-11-07 11:30:00
 2 2021-10-04 10:25:00  2022-05-15 08:14:54 2022-07-11 12:23:30   2021-11-10 11:37:45    2022-07-11 12:23:30

 SELECT CASE 
        WHEN CAST(fe_connected  AS datetime) > latest_connected THEN  'FE'
        WHEN CAST(ce_connected  AS datetime) > latest_connected THEN  'CE'
        WHEN CAST(ln_connected AS datetime) > latest_connected THEN  'LN'
        WHEN CAST(jp_connected AS datetime) > latest_connected THEN  'JP'
        ELSE 'NA' END 
from [dbo].[city_register]

CodePudding user response：

Had to change it to use '=' instead of using '>'

SELECT CASE 
        WHEN CAST(fe_connected  AS datetime) = latest_connected THEN  'FE'
        WHEN CAST(ce_connected  AS datetime) = latest_connected THEN  'CE'
        WHEN CAST(ln_connected AS datetime) =  latest_connected THEN  'LN'
        WHEN CAST(jp_connected AS datetime) = latest_connected THEN  'JP'
        ELSE 'NA' END 
from [dbo].[city_register]

CodePudding user response：

Is it possible to change the table? If you have the city init as a column instead like this

ID, Connected, City
1, 2022-10-06 12:59:21, FE
2, 2021-10-04 10:25:00, FE
3, 2022-11-07 11:30:00, CE
4, 2022-05-15 08:14:54, CE

and so on. Then add a unique index (or clusted index) like Connected (descending order), city and id. Then you can just use

Select top 1 City from city_register order by connected desc, city

I have not tested the SQL statement. It is just written here as a general idea.

This would still give you the historic list of when a city has reported the connection. But if you only need the last time the city reported back you could make the table as

ID, City, Connected
1, FE, 2022-10-06 12:59:21
2, CE, 2022-11-07 11:30:00

and so on having one record for each city. Depending on the number of cities you could skip creating the index creation also.

CodePudding user response：

You don't need an unwieldy case expression, you can unpivot the columns which allows you to sort them and select the most recent for each row:

select * 
from dbo.city_register
cross apply(
    select top(1) latest_city
    from (values
        ('FE',fe_connected),
        ('CE',ce_connected),
        ('LN',ln_connected),
        ('JP',jp_connected)
    )v(latest_city,d)
    order by d desc
)c;

Rather than maintaing the latest city you'd be better off utilising this in a view to compute the required value based on the current values of the date columns.

However you can use the above in a CTE (or derived table) to update rows. Alias the column as new_city then

with t as (
 <your query here>
)
update t set latest_city = new_city;