Combining column

[chedjou1971]

Hi,

I'm trying to display all the results in differents columns but I'm having an error when running the query.

Here is the code:

DECLARE @report_date DATETIME

set @report_date = '2008-06-06'

SELECT DATEPART(hh, callplacedtime) AS hr,

(SELECT COUNT(i3_rowid) AS test1,
COUNT(t2.CustomerId) AS SalesConf,COUNT(t1.i3_RowId)as Effective, COUNT(t1.i3_RowId) as Ineffective, COUNT(DISTINCT agentid) as TSR
FROM SQLPROD02.I3_Dialer.dbo.I3_HIPF05WORKFLOW_CH0 AS t1
INNER JOIN SQLPROD01.HIP.dbo.TinboundCalls AS t2 ON t1.callid = t2.CallId
WHERE (finishcode LIKE '%Succès%') AND (calldate BETWEEN @report_date AND DATEADD(d, 1, @report_date))
GROUP BY LTRIM(STR(DATEPART(hh, callplacedtime))) + ':00-' + LTRIM(STR(DATEPART(hh, callplacedtime))) + ':59') AS SalesTran,

(SELECT COUNT(t2.CustomerId) AS test2
FROM SQLPROD02.i3_Dialer.dbo.I3_HIPF05WORKFLOW_CL0 AS t1 INNER JOIN
SQLPROD01.HIP.dbo.TinboundCalls AS t2 ON t1.tm_id = t2.CustomerId
WHERE (t2.CallDate BETWEEN @report_date AND DATEADD(d, 1, @report_date)) AND (t2.WrapUpCode = '01') AND (t2.Verified = '01')
GROUP BY LTRIM(STR(DATEPART(hh, callDATE))) + ':00-' + LTRIM(STR(DATEPART(hh, calldate))) + ':59') AS SalesConf,

(SELECT COUNT(t2.i3_RowId)as test3
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) as CallPlacedTime
FROM 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 @report_date AND DATEADD(d, 1, @report_date))
AND DATEPART(hh, t2.callplacedtime)=DATEPART(hh, t1.callplacedtime)
GROUP BY LTRIM(STR(DATEPART(hh, t1.callplacedtime))) + ':00-' + LTRIM(STR(DATEPART(hh, t1.callplacedtime))) + ':59') AS Effective,

(SELECT COUNT(t2.i3_RowId) as test4
FROM
(SELECT i3_rowid,MAX(CallPlacedTime) as CallPlacedTime
FROM I3_HIPF05WORKFLOW_CH0 WHERE agentid <> '' GROUP BY i3_rowid) AS 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 @report_date AND DATEADD(d, 1, @report_date))
GROUP BY LTRIM(STR(DATEPART(hh, t2.callplacedtime))) + ':00-' + LTRIM(STR(DATEPART(hh, t2.callplacedtime))) + ':59') AS Ineffective,

(SELECT COUNT(DISTINCT agentid) as test5
FROM SQLPROD02.I3_Dialer.dbo.I3_HIPF05WORKFLOW_AS0 t2
WHERE (agentTime Between @report_date AND DATEADD(d, 1, @report_date) ) and agentid <> ''
GROUP BY LTRIM(STR(DATEPART(hh, agenttime))) + ':00-' + LTRIM(STR(DATEPART(hh, agenttime))) + ':59') AS TSR,

(SELECT dbo.ConvertToHours(SUM(CAST(PropertyValue as INT))) as test6
FROM dbo.i3_HIPF05WORKFLOW_as0 t1
INNER JOIN
I3_HIPF05WORKFLOW_CH0 t2 ON t1.callid = t2.callid
WHERE propertyname NOT IN ('Login', 'Logout', 'BreakStart', 'BreakStop')
and (agenttime between @report_date AND DATEADD(d, 1, @report_date)) and t1.agentid <> ''
AND DATEPART(hh, callplacedtime)=DATEPART(hh, agenttime)
GROUP BY LTRIM(STR(DATEPART(hh, agenttime))) + ':00-' + LTRIM(STR(DATEPART(hh, agenttime))) + ':59') AS PhoneHrs,

(SELECT dbo.ConvertToHours(SUM(CAST(PropertyValue as INT))) as test7
FROM dbo.i3_HIPF05WORKFLOW_as0
WHERE propertyname IN ('WrapUpTime', 'StageTime')
and (agenttime between @report_date AND DATEADD(d, 1, @report_date)) and agentid <> ''
GROUP BY LTRIM(STR(DATEPART(hh, agenttime))) + ':00-' + LTRIM(STR(DATEPART(hh, agenttime))) + ':59') AS TalkHrs

FROM SQLPROD02.I3_Dialer.dbo.I3_HIPF05WORKFLOW_CH0
GROUP BY DATEPART(hh, callplacedtime)
ORDER BY hr

this is the error when trying to run it:
Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can someone could help to tell me where I'm doing the mistake.

Thanks.

Claude

 

[RiverGuy]

The first subquery

(SELECT   COUNT(i3_rowid) AS test1,
    COUNT(t2.CustomerId) AS SalesConf,COUNT(t1.i3_RowId)as Effective, COUNT(t1.i3_RowId) as Ineffective, COUNT(DISTINCT agentid) as TSR
    FROM    SQLPROD02.I3_Dialer.dbo.I3_HIPF05WORKFLOW_CH0 AS t1
            INNER JOIN SQLPROD01.HIP.dbo.TinboundCalls AS t2 ON t1.callid = t2.CallId
    WHERE   (finishcode LIKE '%Succès%') AND (calldate BETWEEN @report_date AND DATEADD(d, 1, @report_date))
    GROUP BY LTRIM(STR(DATEPART(hh, callplacedtime))) + ':00-' + LTRIM(STR(DATEPART(hh, callplacedtime))) + ':59') AS SalesTran,

is selecting more than one value, but you're using in a way intended to produce a single value. So you need to figure out which of those columns you are selecting needs to represent SalesTran and select only that one column. But I would also advise you to redesign the query and use more joins.

 

[SQLSister]

I would suggest that you need to learn about dervied tables. Subqueries are almost always a bad idea from a performance perspective. Like RIverGuy I think this needs more joins and less subqueries.

"NOTHING is more important in a database than integrity." ESquared