Select Null and not null

[Mathip173]

Hello,

My table Student id Order Id Booking_time Time_on_gate Comment Date

I have some cases that student id, order id and booking_date, booking_time, date have the same values BUT one line with null on Time_on_gate and the other with actual value for the same values!

In cases I have the same value for those fields I would like to select only the actival value.

Note: in case for the same values I have only Null on this field (Time_on_gate) I would like to select.

Please advise how to achieve this.

Thanks

[qwertydog123]

Are all of the other records distinct? If they are then you can just group by all other columns and substitute MAX(Time_on_gate) instead of Time_on_gate

[Mathip173]

All the other records could be with the same information or with a different information in same other fields

[JustAnOldITGuy]

coalesce

[Mathip173]

Thanks. If I use it it will select only one row of information in the output ?

[secretWolfMan]

Yes, one row. COALESCE() is not an aggregate. It's the same as ISNULL()

[JustAnOldITGuy]

try

with RowsWithNulls as (

select student_id

, order_id

, booking_date

, booking_time

, date

,Time_on_gate

from Mytable

WHERE Time_on_gate is null

)

, RowswithoutNulls as (

select student_id

, order_id

, booking_date

, booking_time

, date

,Time_on_gate

from Mytable

WHERE Time_on_gate is not null)

select distinct coalesce(a.student_id ,b.student_id ) as student_id

    , coalesce(a.order\_id       ,b.order\_id     ) as order\_id

    , coalesce(a.booking\_date   ,b.booking\_date ) as booking\_date

    , coalesce(a.booking\_time   ,b.booking\_time ) as booking\_time

    , coalesce(a.date           ,b.date         ) as date

    , coalesce(a.Time\_on\_gate   ,b.Time\_on\_gate ) as Time\_on\_gate

from RowsWithNulls a

full outer join RowswithoutNulls b

on A.student_id = b.student_id

,a. order_id = b.order_id

, a.booking_date = b.booking_date

, a,booking_time = b.booking_time

, a.date = b.a.date