Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Program freezing

Status
Not open for further replies.
chedjou1971

chedjou1971

Programmer
May 21, 2008
3
CA
I'm trying to run this query using SQL server reporting service but the program always say program not responding.
Can someone help me to know why I have it?
Thanks.

Claude

here is the program:

[SELECT ISNULL(T1.SalesTrans,0) AS SalesTrans,ISNULL(T1.SalesConfirmed,0) AS Confirmed,ISNULL(T1.PhoneTime,0) AS Hours,
ISNULL(T2.SalesTrans,0) AS TSalesTrans,ISNULL(T2.SalesConfirmed,0) AS TConfirmed,ISNULL(T2.PhoneTime,0) AS THours,
ISNULL(T3.AlreadyE,0) AS AlreadyEnrolled, ISNULL(T4.AlreadyE,0) AS TAlreadyEnrolled,
ISNULL(T5.AlreadyHas,0) AS AlreadyHas,ISNULL(T6.AlreadyHas,0) AS TAlreadyHas,
ISNULL(T7.CancelledAcc,0) AS CancelledAccount,ISNULL(T8.CancelledAcc,0) AS TCancelledAccount,
ISNULL(T9.CancelledMC,0) AS CancelledCard,ISNULL(T10.CancelledMC,0) AS TCancelledCard,
ISNULL(T11.NotByPhone,0) AS NotByPhone,ISNULL(T12.NotByPhone,0) AS TNotByPhone,
ISNULL(T13.DNC,0) AS DNC,ISNULL(T14.DNC,0) AS TDNC,
ISNULL(T15.DNS,0) AS DNS,ISNULL(T16.DNS,0) AS TDNS,
ISNULL(T17.NoNeed,0) AS NoNeed,ISNULL(T18.NoNeed,0) AS TNoNeed,
ISNULL(T19.HasSimilar,0) AS HasSimilar,ISNULL(T20.HasSimilar,0) AS THasSimilar,
ISNULL(T21.Ineligible,0) AS Ineligible,ISNULL(T22.Ineligible,0) AS TIneligible,
ISNULL(T23.InvalidAge,0) AS InvalidAge,ISNULL(T24.InvalidAge,0) AS TInvalidAge,
ISNULL(T25.NoReason,0) AS NoReason,ISNULL(T26.NoReason,0) AS TNoReason,
ISNULL(T27.NotCompet,0) AS NotCompetitive,ISNULL(T28.NotCompet,0) AS TNotCompetitive,
ISNULL(T29.NotInterested,0) AS NotInterested,ISNULL(T30.NotInterested,0) AS TNotInterested,
ISNULL(T31.OtherReason,0) AS OtherReason, ISNULL(T32.OtherReason,0) AS TOtherReason,
ISNULL(T33.Referred,0) AS Referred,ISNULL(T34.Referred,0) AS TReferred,
ISNULL(T35.TooExp,0) AS TooExpensive,ISNULL(T36.TooExp,0) AS TTooExpensive,
ISNULL(T37.Culture,0) AS Culture,ISNULL(T38.Culture,0) AS TCulture,
ISNULL(T39.DeadLine,0) AS DeadLine,ISNULL(T40.DeadLine,0) AS TDeadLine,
ISNULL(T41.Deceased,0) AS Deceased,ISNULL(T42.Deceased,0) AS TDeceased,
ISNULL(T43.NotListen,0) AS NotListen,ISNULL(T44.NotListen,0) AS TNotListen,
ISNULL(T45.HangUp,0) AS HangUp,ISNULL(T46.HangUp,0) AS THangUp,
ISNULL(T47.Prolonged,0) AS Prolonged,ISNULL(T48.Prolonged,0) AS TProlonged,
ISNULL(T49.Refused,0) AS Refused,ISNULL(T50.Refused,0) AS TRefused,
ISNULL(T51.Unavailable,0) AS Unavailable,ISNULL(T52.Unavailable,0) AS TUnavailable,
ISNULL(T53.Unreachable,0) AS Unreachable,ISNULL(T54.Unreachable,0) AS TUnreachable,
ISNULL(T55.WrongNumber,0) AS WrongNumber,ISNULL(T56.WrongNumber,0) AS TWrongNumber,
ISNULL(T57.Follup1,0) AS Follup1,ISNULL(T58.Follup1,0) AS TFollup1,
ISNULL(T59.Follup2,0) AS Follup2,ISNULL(T60.Follup2,0) AS TFollup2,
ISNULL(T61.CB1,0) AS CB1,ISNULL(T62.CB1,0) AS TCB1,
ISNULL(T63.CB2,0) AS CB2,ISNULL(T64.CB2,0) AS TCB2,
ISNULL(T65.Fax,0) AS Fax,ISNULL(T66.Fax,0) AS TFax




FROM
(SELECT SUM(SalesTran) AS SalesTrans, SUM(SalesConfirmed) AS SalesConfirmed, SUM(PhoneTime) AS PhoneTime
FROM SQLPROD01.HIP.dbo.TCCRReport
WHERE (DayDate BETWEEN @Enter_DayDate AND DATEADD(d, 1, @Enter_DayDate))
) AS T1 CROSS JOIN

(SELECT SUM(SalesTran) AS SalesTrans, SUM(SalesConfirmed) AS SalesConfirmed, SUM(PhoneTime) AS PhoneTime
FROM SQLPROD01.HIP.dbo.TCCRReport
WHERE (DayDate < DATEADD(d, 1, @Enter_DayDate))
) AS T2 CROSS JOIN

(SELECT 'ALREADY ENROLLED' AS OutCode, COUNT(t2.i3_RowId) AS AlreadyE
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='ALREADY ENROLLED'
) AS T3 CROSS JOIN

(SELECT 'ALREADY ENROLLED' AS OutCode, COUNT(t2.i3_RowId) AS AlreadyE
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='ALREADY ENROLLED'
) AS T4 CROSS JOIN

(SELECT 'ALREADY HAS SUFFICIENT INSURANCE' AS OutCode, COUNT(t2.i3_RowId) AS AlreadyHas
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='ALREADY HAS SUFFICIENT INSURANCE'
) AS T5 CROSS JOIN

(SELECT 'ALREADY HAS SUFFICIENT INSURANCE' AS OutCode, COUNT(t2.i3_RowId) AS AlreadyHas
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='ALREADY HAS SUFFICIENT INSURANCE'
) AS T6 CROSS JOIN

(SELECT 'CANCELLED NBLIC ACCOUNT' AS OutCode, COUNT(t2.i3_RowId) AS CancelledAcc
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CANCELLED NBLIC ACCOUNT'
) AS T7 CROSS JOIN

(SELECT 'CANCELLED NBLIC ACCOUNT' AS OutCode, COUNT(t2.i3_RowId) AS CancelledAcc
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CANCELLED NBLIC ACCOUNT'
) AS T8 CROSS JOIN

(SELECT 'CANCELLED NBLIC MASTERCARD' AS OutCode, COUNT(t2.i3_RowId) AS CancelledMC
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CANCELLED NBLIC MASTERCARD'
) AS T9 CROSS JOIN

(SELECT 'CANCELLED NBLIC MASTERCARD' AS OutCode, COUNT(t2.i3_RowId) AS CancelledMC
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CANCELLED NBLIC MASTERCARD'
) AS T10 CROSS JOIN

(SELECT 'CLIENT DID NOT WANT TO ENROLL OVER PHONE' AS OutCode, COUNT(t2.i3_RowId) AS NotByPhone
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CLIENT DID NOT WANT TO ENROLL OVER PHONE'
) AS T11 CROSS JOIN

(SELECT 'CLIENT DID NOT WANT TO ENROLL OVER PHONE' AS OutCode, COUNT(t2.i3_RowId) AS NotByPhone
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CLIENT DID NOT WANT TO ENROLL OVER PHONE'
) AS T12 CROSS JOIN

(SELECT 'DO NOT CALL' AS OutCode, COUNT(t2.i3_RowId) AS DNC
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DO NOT CALL'
) AS T13 CROSS JOIN

(SELECT 'DO NOT CALL' AS OutCode, COUNT(t2.i3_RowId) AS DNC
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DO NOT CALL'
) AS T14 CROSS JOIN

(SELECT 'DO NOT SOLICIT' AS OutCode, COUNT(t2.i3_RowId) AS DNS
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DO NOT SOLICIT'
) AS T15 CROSS JOIN

(SELECT 'DO NOT SOLICIT' AS OutCode, COUNT(t2.i3_RowId) AS DNS
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DO NOT SOLICIT'
) AS T16 CROSS JOIN

(SELECT 'DOES NOT SEE THE NEED' AS OutCode, COUNT(t2.i3_RowId) AS NoNeed
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DOES NOT SEE THE NEED'
) AS T17 CROSS JOIN

(SELECT 'DOES NOT SEE THE NEED' AS OutCode, COUNT(t2.i3_RowId) AS NoNeed
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DOES NOT SEE THE NEED'
) AS T18 CROSS JOIN

(SELECT 'HAS SIMILAR PRODUCT WITH COMPETITOR' AS OutCode, COUNT(t2.i3_RowId) AS HasSimilar
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='HAS SIMILAR PRODUCT WITH COMPETITOR'
) AS T19 CROSS JOIN

(SELECT 'HAS SIMILAR PRODUCT WITH COMPETITOR' AS OutCode, COUNT(t2.i3_RowId) AS HasSimilar
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='HAS SIMILAR PRODUCT WITH COMPETITOR'
) AS T20 CROSS JOIN

(SELECT 'INELIGIBLE' AS OutCode, COUNT(t2.i3_RowId) AS Ineligible
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='INELIGIBLE'
) AS T21 CROSS JOIN

(SELECT 'INELIGIBLE' AS OutCode, COUNT(t2.i3_RowId) AS Ineligible
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='INELIGIBLE'
) AS T22 CROSS JOIN

(SELECT 'INVALID AGE' AS OutCode, COUNT(t2.i3_RowId) AS InvalidAge
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='INVALID AGE'
) AS T23 CROSS JOIN

(SELECT 'INVALID AGE' AS OutCode, COUNT(t2.i3_RowId) AS InvalidAge
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='INVALID AGE'
) AS T24 CROSS JOIN

(SELECT 'NO REASON GIVEN' AS OutCode, COUNT(t2.i3_RowId) AS NoReason
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='NO REASON GIVEN'
) AS T25 CROSS JOIN

(SELECT 'NO REASON GIVEN' AS OutCode, COUNT(t2.i3_RowId) AS NoReason
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='NO REASON GIVEN'
) AS T26 CROSS JOIN

(SELECT 'NOT COMPETITIVE WITH PREVIOUS OFFERS' AS OutCode, COUNT(t2.i3_RowId) AS NotCompet
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='NOT COMPETITIVE WITH PREVIOUS OFFERS'
) AS T27 CROSS JOIN

(SELECT 'NOT COMPETITIVE WITH PREVIOUS OFFERS' AS OutCode, COUNT(t2.i3_RowId) AS NotCompet
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='NOT COMPETITIVE WITH PREVIOUS OFFERS'
) AS T28 CROSS JOIN

(SELECT 'NOT INTERESTED' AS OutCode, COUNT(t2.i3_RowId) AS NotInterested
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='NOT INTERESTED'
) AS T29 CROSS JOIN

(SELECT 'NOT INTERESTED' AS OutCode, COUNT(t2.i3_RowId) AS NotInterested
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='NOT INTERESTED'
) AS T30 CROSS JOIN

(SELECT 'OTHER REASON' AS OutCode, COUNT(t2.i3_RowId) AS OtherReason
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='OTHER REASON'
) AS T31 CROSS JOIN

(SELECT 'OTHER REASON' AS OutCode, COUNT(t2.i3_RowId) AS OtherReason
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='OTHER REASON'
) AS T32 CROSS JOIN

(SELECT 'REFERRED TO NBLIC CUSTOMER SERVICE 1-800 NUMBER' AS OutCode, COUNT(t2.i3_RowId) AS Referred
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='REFERRED TO NBLIC CUSTOMER SERVICE 1-800 NUMBER'
) AS T33 CROSS JOIN

(SELECT 'REFERRED TO NBLIC CUSTOMER SERVICE 1-800 NUMBER' AS OutCode, COUNT(t2.i3_RowId) AS Referred
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='REFERRED TO NBLIC CUSTOMER SERVICE 1-800 NUMBER'
) AS T34 CROSS JOIN

(SELECT 'TOO EXPENSIVE' AS OutCode, COUNT(t2.i3_RowId) AS TooExp
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='TOO EXPENSIVE'
) AS T35 CROSS JOIN

(SELECT 'TOO EXPENSIVE' AS OutCode, COUNT(t2.i3_RowId) AS TooExp
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 1 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='TOO EXPENSIVE'
) AS T36 CROSS JOIN

(SELECT 'CULTURE BARRIER' AS OutCode, COUNT(t2.i3_RowId) AS Culture
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CULTURE BARRIER'
) AS T37 CROSS JOIN

(SELECT 'CULTURE BARRIER' AS OutCode, COUNT(t2.i3_RowId) AS Culture
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CULTURE BARRIER'
) AS T38 CROSS JOIN

(SELECT 'DEAD LINE DISCONNECTED' AS OutCode, COUNT(t2.i3_RowId) AS DeadLine
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DEAD LINE DISCONNECTED'
) AS T39 CROSS JOIN

(SELECT 'DEAD LINE DISCONNECTED' AS OutCode, COUNT(t2.i3_RowId) AS DeadLine
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DEAD LINE DISCONNECTED'
) AS T40 CROSS JOIN

(SELECT 'DECEASED' AS OutCode, COUNT(t2.i3_RowId) AS Deceased
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DECEASED'
) AS T41 CROSS JOIN

(SELECT 'DECEASED' AS OutCode, COUNT(t2.i3_RowId) AS Deceased
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DECEASED'
) AS T42 CROSS JOIN

(SELECT 'DID NOT LISTEN TO PITCH' AS OutCode, COUNT(t2.i3_RowId) AS NotListen
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DID NOT LISTEN TO PITCH'
) AS T43 CROSS JOIN

(SELECT 'DID NOT LISTEN TO PITCH' AS OutCode, COUNT(t2.i3_RowId) AS NotListen
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='DID NOT LISTEN TO PITCH'
) AS T44 CROSS JOIN

(SELECT 'HANG UP' AS OutCode, COUNT(t2.i3_RowId) AS HangUp
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='HANG UP'
) AS T45 CROSS JOIN

(SELECT 'HANG UP' AS OutCode, COUNT(t2.i3_RowId) AS HangUp
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='HANG UP'
) AS T46 CROSS JOIN

(SELECT 'PROLONGED ABSENCE' AS OutCode, COUNT(t2.i3_RowId) AS Prolonged
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='PROLONGED ABSENCE'
) AS T47 CROSS JOIN

(SELECT 'PROLONGED ABSENCE' AS OutCode, COUNT(t2.i3_RowId) AS Prolonged
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='PROLONGED ABSENCE'
) AS T48 CROSS JOIN

(SELECT 'REFUSED RECORDING' AS OutCode, COUNT(t2.i3_RowId) AS Refused
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='REFUSED RECORDING'
) AS T49 CROSS JOIN

(SELECT 'REFUSED RECORDING' AS OutCode, COUNT(t2.i3_RowId) AS Refused
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='REFUSED RECORDING'
) AS T50 CROSS JOIN

(SELECT 'UNAVAILABLE DURING CALLING WINDOW' AS OutCode, COUNT(t2.i3_RowId) AS Unavailable
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='UNAVAILABLE DURING CALLING WINDOW'
) AS T51 CROSS JOIN

(SELECT 'UNAVAILABLE DURING CALLING WINDOW' AS OutCode, COUNT(t2.i3_RowId) AS Unavailable
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='UNAVAILABLE DURING CALLING WINDOW'
) AS T52 CROSS JOIN

(SELECT 'UNREACHABLE ON 3 FOLLOW UPS' AS OutCode, COUNT(t2.i3_RowId) AS Unreachable
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='UNREACHABLE ON 3 FOLLOW UPS'
) AS T53 CROSS JOIN

(SELECT 'UNREACHABLE ON 3 FOLLOW UPS' AS OutCode, COUNT(t2.i3_RowId) AS Unreachable
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='UNREACHABLE ON 3 FOLLOW UPS'
) AS T54 CROSS JOIN

(SELECT 'WRONG NUMBER' AS OutCode, COUNT(t2.i3_RowId) AS WrongNumber
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='WRONG NUMBER'
) AS T55 CROSS JOIN

(SELECT 'WRONG NUMBER' AS OutCode, COUNT(t2.i3_RowId) AS WrongNumber
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 2 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='WRONG NUMBER'
) AS T56 CROSS JOIN

(SELECT 'FOLLUP 1 REQUESTED' AS OutCode, COUNT(t2.i3_RowId) AS Follup1
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t2.FinishCode)IS NULL
) AS T57 CROSS JOIN

(SELECT 'FOLLUP 1 REQUESTED' AS OutCode, COUNT(t2.i3_RowId) AS Follup1
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t2.FinishCode)IS NULL
) AS T58 CROSS JOIN

(SELECT 'FOLLUP 2 REQUESTED' AS OutCode, COUNT(t2.i3_RowId) AS Follup2
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='FOLLUP 2 REQUESTED'
) AS T59 CROSS JOIN

(SELECT 'FOLLUP 2 REQUESTED' AS OutCode, COUNT(t2.i3_RowId) AS Follup2
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='FOLLUP 2 REQUESTED'
) AS T60 CROSS JOIN

(SELECT 'CALL BACK 1' AS OutCode, COUNT(t2.i3_RowId) AS CB1
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CALL BACK 1'
) AS T61 CROSS JOIN

(SELECT 'CALL BACK 1' AS OutCode, COUNT(t2.i3_RowId) AS CB1
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CALL BACK 1'
) AS T62 CROSS JOIN

(SELECT 'CALL BACK 2' AS OutCode, COUNT(t2.i3_RowId) AS CB2
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CALL BACK 2'
) AS T63 CROSS JOIN

(SELECT 'CALL BACK 2' AS OutCode, COUNT(t2.i3_RowId) AS CB2
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='CALL BACK 2'
) AS T64 CROSS JOIN

(SELECT 'ANSWERING/FAX MACHINE' AS OutCode, COUNT(t2.i3_RowId) AS Fax
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime Between @Enter_DayDate and DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='ANSWERING/FAX MACHINE'
) AS T65 CROSS JOIN

(SELECT 'ANSWERING/FAX MACHINE' AS OutCode, COUNT(t2.i3_RowId) AS Fax
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) AS CallPlacedTime
FROM SQLPROD02.I3_Dialer24.dbo.I3_HIPF05WORKFLOW_CH0
WHERE agentid <> ''
GROUP BY i3_rowid) t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.i3_rowid = t2.i3_rowid and t1.CallPlacedTime = t2.CallPlacedTime
LEFT OUTER JOIN
SQLPROD01.HIP.dbo.TFinishCodes t3 ON t2.FinishCode = t3.FinishCode
WHERE
CAT = 3 and
(t1.CallPlacedTime < DATEADD(d, 1, @Enter_DayDate) ) AND
UPPER(t3.DIMICode)='ANSWERING/FAX MACHINE'
) AS T66 ]
 
Sort by date Sort by votes
Does the SQL run in Query Analyser?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
I am not 100% sure that this would work because you have declared t1,t2,t3 and then keep on referencing t2 and t3 and redeclaring t1 and every statement.

I would also wrap the statement in a view or stored procedure because is a heck of a statement with more than 60 queries to pass in RS.

it would take a long time for the query to be validated by the server before is even executed and this will be done everytime you run the report.

another advice would be to start small, make it run with 5 and then add more

I am working on a diller as well at present and I hope they won't ask me to do any stuff like that.......

-Mo

If you don't stand for something, you'll fall for anything
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Auguy
  • Locked
  • Question
Reporting Services 2000 - &quot;This Program cannot display the webpage&quot;
Replies
0
Views
25
Auguy
Auguy
CharlesBen
  • Locked
  • Question
Report Automation Program?
Replies
1
Views
29
RTag
RTag
Cooperdam
  • Locked
  • Question
Programming for Dashboards
Replies
3
Views
35
RiverGuy
RiverGuy
aajay
  • Locked
  • Question
freezing column and header in a report
Replies
0
Views
25
aajay
aajay
brynomm
  • Locked
  • Question
MDX Programming. How to exclude sum function
Replies
0
Views
38
brynomm
brynomm

Part and Inventory Search

Sponsor

Back
Top