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
|
Select t1.code,t1.name,t1.capab,t1.usrname
,case when (convert(varchar(8),adddate,3) = convert(varchar(8),MODDATE,3)or adddate is not null and moddate is null)and deldate is null then adddate else null end as date_de_creation
,case when convert(varchar(8),adddate,3) <> convert(varchar(8),MODDATE,3) and deldate is null then moddate else null end as date_de_modification
,t1.deldate as date_de_suppression
,t1.passwdexpdate as Expi_MDP
,t2.soc01 AS "Soc 1"
,t2.soc02 AS "Soc 2"
,t2.soc03 AS "Soc 3"
,t2.soc04 AS "Soc 4"
,t2.soc05 AS "Soc 5"
,t2.soc06 AS "Soc 6"
,t2.soc07 AS "Soc 7"
,t2.soc08 AS "Soc 8"
from coda.com_usr t1
, (
select
usrname
, max(soc1) as soc01
, max(soc2) as soc02
, max(soc3) as soc03
, max(soc4) as soc04
, max(soc5) as soc05
, max(soc6) as soc06
, max(soc7) as soc07
, max(soc8) as soc08
from
(
select
usrname
,CASE LSTSEQNO WHEN 1 THEN CASE CMPCODE WHEN '*' THEN 'Toutes' ELSE cmpcode END END as soc1
,CASE lstseqno WHEN 2 THEN cmpcode ELSE ''END as soc2
,CASE lstseqno WHEN 3 THEN cmpcode ELSE '' END as soc3
,CASE lstseqno WHEN 4 THEN cmpcode ELSE '' END as soc4
,CASE lstseqno WHEN 5 THEN cmpcode ELSE '' END as soc5
,CASE lstseqno WHEN 6 THEN cmpcode ELSE '' END as soc6
,CASE lstseqno WHEN 7 THEN cmpcode ELSE '' END as soc7
,CASE lstseqno WHEN 8 THEN cmpcode ELSE '' END as soc8
from
coda.COM_USRLIST t2 )
group by usrname
) t2
where 1=1 |
