1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
| CREATE view [dbo].[vRESUsersGlob]
as
-- Serveur local
select cast(u.id_user as varchar(8)) collate sql_latin1_general_cp1_ci_ai as id_user,
u.nom + ' ' + u.prenom collate sql_latin1_general_cp1_ci_ai as fullname,
u.statut collate sql_latin1_general_cp1_ci_ai as statut,
u.password collate sql_latin1_general_cp1_ci_ai as password,
u.dt_print_doc,
u.dt_retour_doc,
u.code_chauffeur collate sql_latin1_general_cp1_ci_ai as code_chauffeur,
u.badge collate sql_latin1_general_cp1_ci_ai as id_badge
from acc_user u
where u.badge is not null
union
select v.id_user collate sql_latin1_general_cp1_ci_ai,
v.fullname collate sql_latin1_general_cp1_ci_ai,
v.statut collate sql_latin1_general_cp1_ci_ai,
v.password collate sql_latin1_general_cp1_ci_ai,
h.dt_print_doc,
h.dt_retour_doc,
v.code_chauffeur collate sql_latin1_general_cp1_ci_ai,
h.id_badge collate sql_latin1_general_cp1_ci_ai
from vrespropriobadge v inner join res_histobadge h
on v.code = case when v.type_code = 'I' then h.int_code
when v.type_code = 'E' then h.ext_code
when v.type_code = 'T' then h.tmp_code
end and (h.date_fin is null or h.date_fin > getdate())
union
-- Serveurs distants
select v.id_user collate sql_latin1_general_cp1_ci_ai,
v.fullname collate sql_latin1_general_cp1_ci_ai,
v.statut collate sql_latin1_general_cp1_ci_ai,
v.password collate sql_latin1_general_cp1_ci_ai,
h.dt_print_doc,
h.dt_retour_doc,
v.code_chauffeur collate sql_latin1_general_cp1_ci_ai,
h.id_badge collate sql_latin1_general_cp1_ci_ai
from [10.21.1.35].DataSQL.dbo.vrespropriobadge v inner join [10.21.1.35].DataSQL.dbo.res_histobadge h
on v.code = case when v.type_code = 'I' then h.int_code
when v.type_code = 'E' then h.ext_code
when v.type_code = 'T' then h.tmp_code
end and (h.date_fin is null or h.date_fin > getdate())
union
select v.id_user collate sql_latin1_general_cp1_ci_ai,
v.fullname collate sql_latin1_general_cp1_ci_ai,
v.statut collate sql_latin1_general_cp1_ci_ai,
v.password collate sql_latin1_general_cp1_ci_ai,
h.dt_print_doc,
h.dt_retour_doc,
v.code_chauffeur collate sql_latin1_general_cp1_ci_ai,
h.id_badge collate sql_latin1_general_cp1_ci_ai
from [10.2.1.32].DataSQL.dbo.vrespropriobadge v inner join [10.2.1.32].DataSQL.dbo.res_histobadge h
on v.code = case when v.type_code = 'I' then h.int_code
when v.type_code = 'E' then h.ext_code
when v.type_code = 'T' then h.tmp_code
end and (h.date_fin is null or h.date_fin > getdate())
GO |
Partager