sql - Logically merging 4 columns of the same information -

sql - Logically merging 4 columns of the same information -

i'm querying 3 different databases (4 total fields) "username" field given particular machine name in our environment: sccm, mcafee epo, , activedirectory.

the 4 columns sccm_top, sccm_last, epo, ad

some of tuples like:

joe, joe, administrator, joe or joe, sarah, joe, joe or null, null, joe, joe or null, null, joe, sarah

the lastly illustration of hard code against.

i'm writing case statement help merge info in additive way give 1 final column of "best guess". @ moment, i'm weighing valid username based on column, "age of record" each database.

case when epo_age <= ct_age , not epo_un null epo_un when not (sccm_age) null , not (sccm_last_un) null sccm_last_un when not (sccm_age) null , not (sccm_top_un) null sccm_top_un when not (ad_un) null ad_un else null end bestname,

but there has improve way combine these records one. next step weigh "average age" , pick username there, discarding "administrator".

any thoughts or tricks?

you benefit little coalesce function first non-null value , like:

coalesce(case when epo_age<=ct_age epo_un end, case when sccm_age not null coalesce(sccm_last_un, sccm_top_un) end, ad_un) bestname

sql tsql