locked
SQL GPS split with null value consideration RRS feed

  • Question

  • Good day,

    I have sample t-sql as in the below:

    SELECT 
    SUB_DATA.date,
    SUB_DATA.gps,
    SUB_DATA.latitude, 
    SUB_DATA.longitude
     FROM 
    (SELECT DISTINCT
    convert(date, UV.first_entry) date,
    UV.first_entry,
    (select max(uh.value) 
    FROM UPLOADS.RECORDS UR1
    left join uploads.headers uh on ur1.record_id = uh.record_id
    left join history.module_header_fields hmhf1 on ur1.module_id = hmhf1.module_id and ur1.module_version = hmhf1.date and uh.field_id = hmhf1.field_id
    where ur1.record_id = ur.record_id
    and HMHF1.text like '%Take GPS coordinate for the Outlet (outside not under a roof) %') gps,

    (select max(LEFT(uh.value,charindex(',',uh.value))) 
    FROM UPLOADS.RECORDS UR1
    left join uploads.headers uh on ur1.record_id = uh.record_id
    left join history.module_header_fields hmhf1 on ur1.module_id = hmhf1.module_id and ur1.module_version = hmhf1.date and uh.field_id = hmhf1.field_id
    where ur1.record_id = ur.record_id
    and HMHF1.text like '%Take GPS coordinate for the Outlet (outside not under a roof) %') latitude,
    (select max(RIGHT(uh.value,LEN(uh.value) - charindex(',',uh.value))) 
    FROM UPLOADS.RECORDS UR1
    left join uploads.headers uh on ur1.record_id = uh.record_id
    left join history.module_header_fields hmhf1 on ur1.module_id = hmhf1.module_id and ur1.module_version = hmhf1.date and uh.field_id = hmhf1.field_id
    where ur1.record_id = ur.record_id
    and HMHF1.text like '%Take GPS coordinate for the Outlet (outside not under a roof) %') longitude
    ,(SELECT MAX(SUV.id) FROM UPLOADS.VISITS SUV with (NoLock, ReadUncommitted) WHERE SUV.entity_id = UV.entity_id AND SUV.id < UV.id) last_visit_id
    FROM ENTITY.ENTITIES EE with (NoLock, ReadUncommitted)
    INNER JOIN GROUPS.LOCATIONS GL with (NoLock, ReadUncommitted) ON EE.location_id = GL.id

    INNER JOIN GROUPS.CLIENTS GC with (NoLock, ReadUncommitted) ON EE.client_id = GC.id
    INNER JOIN UPLOADS.VISITS UV with (NoLock, ReadUncommitted) ON EE.id = UV.entity_id
    INNER JOIN UPLOADS.RECORDS UR with (NoLock, ReadUncommitted) ON UV.ID = UR.visit_id

    INNER JOIN USERS.USERS UU with (NoLock, ReadUncommitted) ON UU.ID = UR.user_id

    ) SUB_DATA

    The result of the above is shown below:

    I want to have a clean format for the latitude and longitude, i.e no trailing comma on the latitude column, and no 'time' part for the longitude column. (example, for row 898 above, lat = -33.828541 and long = 25.394183

    I also want to cater for null values for the GPS column (I have tried the length substring, but get an error)

    Please kindly assist,

    Regards

    Wednesday, February 10, 2016 7:22 PM

Answers

  • Hi Zimiso,

    Please check below sample, you can apply the expression to either outerside or inside select of your query.

    DECLARE @T  TABLE(gps VARCHAR(100),latitude VARCHAR(100),longitude VARCHAR(100))
    
    INSERT INTO @T VALUES('-33.828541,25.394183,05:25:32 pm','-33.828541,','25.394183,05:25:32 pm')
    
    SELECT REPLACE(latitude,',','') latitude, LEFT(longitude,CHARINDEX(',',longitude)-1)FROM @T

    >I also want to cater for null values for the GPS column
    I don't quite get this point, please clarify.

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support

    Thursday, February 11, 2016 3:26 AM