select distinct treatment.offerId as OFFERIDMATCH,
tx.*,
dch.RTSelectionMethod
from UACI_XSessResponse tx
Left Outer Join UA_Treatment treatment ON tx.trackingCode=treatment.treatmentCode
Left Outer Join UA_DtlContactHist dch ON tx.CustomerID = dch.CustomerID
Left Outer Join UA_ContactHistory ch ON tx.CustomerID = ch.CustomerID
AND treatment.cellID = ch.cellID
AND treatment.packageID=ch.packageID
where tx.mark=1
and tx.trackingCodeType=1
select treatment.treatmentCode as TREATMENTCODEMATCH,
tx.*,
dch.RTSelectionMethod
from UACI_XSessResponse tx
Left Outer Join UA_DtlContactHist dch ON tx.CustomerID=dch.CustomerID
Left Outer Join UA_Treatment treatment ON tx.offerId = treatment.offerId
Left Outer Join
(
select max(dch.contactDateTime) as maxDate,
treatment.offerId,
dch.CustomerID
from UA_DtlContactHist dch, UA_Treatment treatment, UACI_XSessResponse tx
where tx.CustomerID=dch.CustomerID
and tx.offerID = treatment.offerId
and dch.treatmentInstId = treatment.treatmentInstId
group by dch.CustomerID, treatment.offerId
) dch_by_max_date ON tx.CustomerID=dch_by_max_date.CustomerID
and tx.offerId = dch_by_max_date.offerId
where tx.mark = 1
and dch.contactDateTime = dch_by_max_date.maxDate
and dch.treatmentInstId = treatment.treatmentInstId
and tx.trackingCodeType=2
union
select treatment.treatmentCode as TREATMENTCODEMATCH,
tx.*,
0
from UACI_XSessResponse tx
Left Outer Join UA_ContactHistory ch ON tx.CustomerID =ch.CustomerID
Left Outer Join UA_Treatment treatment ON tx.offerId = treatment.offerId
Left Outer Join
(
select max(ch.contactDateTime) as maxDate,
treatment.offerId, ch.CustomerID
from UA_ContactHistory ch, UA_Treatment treatment, UACI_XSessResponse tx
where tx.CustomerID =ch.CustomerID
and tx.offerID = treatment.offerId
and treatment.cellID = ch.cellID
and treatment.packageID=ch.packageID
group by ch.CustomerID, treatment.offerId
) ch_by_max_date ON tx.CustomerID =ch_by_max_date.CustomerID
and tx.offerId = ch_by_max_date.offerId
and treatment.cellID = ch.cellID
and treatment.packageID=ch.packageID
where tx.mark = 1
and ch.contactDateTime = ch_by_max_date.maxDate
and treatment.cellID = ch.cellID
and treatment.packageID=ch.packageID
and tx.offerID = treatment.offerId
and tx.trackingCodeType=2
Select m.TreatmentCode as TreatmentCodeMatch, m.OfferID as OfferIDMatch, tx.*
From MyLookup m, UACI_XSessResponse tx
Where m.customerId = tx.customerId
And m.alternateCode = tx.trackingCode
And tx.mark=1
And tx.trackingCodeType = <x>
|
Copyright IBM Corporation 2014. All Rights Reserved.
|