:ich möchte alle Kunden zählen die in einer Endkampagne sind und einzeln auggeführt nach Responsen der letzten History (wenn sie in der Endkampagne liegen ist es ja wohl sowieso die letzte HIS).
Aber ich kann die TS$_HIS und die TS$_CAM nicht verknüpfen, dieses führt dann aber zum falschen Ergebnis.
- Code: Alles auswählen
select distinct --'Welle 1' as "Welle"
TS$_CUSTOMER.CUST_GROUP as "Welle"
,sum (case when (TS$_HISTORY.RES_ID = 1408) then 1 else 0 end) as "S1 WIVO"
,sum (case when (TS$_HISTORY.RES_ID = 1409) then 1 else 0 end) as "S1 WIVO Mitbewohner"
,sum (case when (TS$_HISTORY.RES_ID = 1410) then 1 else 0 end) as "S1 ZP A n u"
,sum (case when (TS$_HISTORY.RES_ID = 1411) then 1 else 0 end) as "S1 ZP z Z n e"
,sum (case when (TS$_HISTORY.RES_ID = 1414) then 1 else 0 end) as "S1 Rückruf v"
,sum (case when (TS$_HISTORY.RES_ID = 1415) then 1 else 0 end) as "S1 kein Intresse"
,sum (case when (TS$_HISTORY.RES_ID = 1416) then 1 else 0 end) as "S1 Ausfall"
,sum (case when (TS$_HISTORY.RES_ID = 1412) then 1 else 0 end) as "S1 kein Rückruf"
,sum (case when (TS$_HISTORY.RES_ID = 1548) then 1 else 0 end) as "S1 Aktionssperre"
,sum (case when (TS$_HISTORY.RES_ID = 1549) then 1 else 0 end) as "S1 Sperre w Ah"
,sum (case when (TS$_HISTORY.RES_ID = 1417) then 1 else 0 end) as "S1 Bestellung"
,sum (case when (TS$_HISTORY.RES_ID = 1420) then 1 else 0 end) as "S2 WIVO"
,sum (case when (TS$_HISTORY.RES_ID = 1421) then 1 else 0 end) as "S2 WIVO Mitbewohner"
,sum (case when (TS$_HISTORY.RES_ID = 1422) then 1 else 0 end) as "S2 Bestellung n z H"
,sum (case when (TS$_HISTORY.RES_ID = 1423) then 1 else 0 end) as "S2 Rückruf"
,sum (case when (TS$_HISTORY.RES_ID = 1424) then 1 else 0 end) as "S2 kein Intresse"
,sum (case when (TS$_HISTORY.RES_ID = 1425) then 1 else 0 end) as "S2 KC Besteller"
,sum (case when (TS$_HISTORY.RES_ID = 1426) then 1 else 0 end) as "S2 Ausfall"
,sum (case when (TS$_HISTORY.RES_ID = 1428) then 1 else 0 end) as "S2 kein Rückruf"
,sum (case when (TS$_HISTORY.RES_ID = 1550) then 1 else 0 end) as "S2 Aktionssperre"
,sum (case when (TS$_HISTORY.RES_ID = 1551) then 1 else 0 end) as "S2 Sperre w Ah"
,sum (case when (TS$_HISTORY.RES_ID = 1427) then 1 else 0 end) as "S2 Bestellung"
from TS$_HISTORY
, TS$_CUSTOMER
, (select
max(TS$_HISTORY.HIS_ID) as MAX_HIS_ID
,MAN_ID as MAN_ID
,CUST_ID as CUST_ID
,'1' as aktuell
from TS$_HISTORY
,TS$_CAMPAIN
where 1=1
and TS$_HISTORY.MAN_ID = 545
and TS$_CAMPAIN.CAM_ID = 1320
group by
TS$_HISTORY.MAN_ID
,TS$_HISTORY.CUST_ID
)
MAX_HIS
, TS$_CAMPAIN
where TS$_HISTORY.MAN_ID = 545
and TS$_CAMPAIN.CAM_ID = 1320
and TS$_CUSTOMER.CUST_GROUP = '75149_1'
and TS$_HISTORY.MAN_ID = TS$_CUSTOMER.MAN_ID (+)
and TS$_HISTORY.CUST_ID = TS$_CUSTOMER.CUST_ID (+)
and TS$_HISTORY.MAN_ID = MAX_HIS.MAN_ID (+)
and TS$_HISTORY.CUST_ID = MAX_HIS.CUST_ID (+)
and trunc(TS$_HISTORY.HIS_START) between '[F1]' and '[F2]'
group by TS$_CUSTOMER.CUST_GROUP
ihr habt doch bestimmt was auf Lager, wie man das rausbekommt
Viele Grüße Stefan
