Result set to be modified

Question

Post reply

Result set to be modified

keshava.murthy

SSC Enthusiast

Points: 193
More actions
May 28, 2013 at 8:30 am

#97155

I have query as follows:
select right(convert(varchar, CCD.startDateTime , 106), 8) as startmonth,CCD.contactDisposition, count(*) as TotalCalls
from ContactCallDetail CCD,
ContactQueueDetail CQD
where
CCD.sessionID=CQD.sessionID
and CCD.sessionSeqNum=CQD.sessionSeqNum
and CCD.profileID=CQD.profileID
and CCD.nodeID=CQD.nodeID
and CCD.applicationName='FLVS'
and CCD.contactType=1
and contactDisposition in (1,2)
and CCD.startDateTime >='2011-01-01 00:00:00.000'
and CCD.startDateTime <='2011-12-31 00:59:59.000'
group by right(convert(varchar, startDateTime , 106), 8),contactDisposition
order by 1
The result is as follows when the above query is executed:
Start month Contactdisposition Total calls
Jan-2011 1 500
Jan -2011 2 4000
Feb-2011 1 400
Feb-2011 2 5000
I would like to have the result set only as follows:
Start Month 1 2
Jan-2011 500 4000
Feb -2011 400 5000
Can I get help please to display the data as above.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

Without DDL, sample data and expected results this is just a guess....

select right(convert(varchar, CCD.startDateTime , 106), 8) as startmonth,

sum(case when CCD.contactDisposition=1 then 1 else 0 end) as TotalCalls1,

sum(case when CCD.contactDisposition=2 then 1 else 0 end) as TotalCalls2

from ContactCallDetail CCD,

ContactQueueDetail CQD

where

CCD.sessionID=CQD.sessionID

and CCD.sessionSeqNum=CQD.sessionSeqNum

and CCD.profileID=CQD.profileID

and CCD.nodeID=CQD.nodeID

and CCD.applicationName='FLVS'

and CCD.contactType=1

and contactDisposition in (1,2)

and CCD.startDateTime >='2011-01-01 00:00:00.000'

and CCD.startDateTime <='2011-12-31 00:59:59.000'

group by right(convert(varchar, startDateTime , 106), 8)

order by 1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

You can use CROSS TABS to achieve the results

SELECTRIGHT(CONVERT(VARCHAR, CCD.startDateTime , 106), 8) AS startmonth,

SUM( CASE WHEN CCD.contactDisposition = 1 THEN 1 ELSE 0 END ) AS 1

SUM( CASE WHEN CCD.contactDisposition = 2 THEN 1 ELSE 0 END ) AS 2

FROMContactCallDetail CCD,

ContactQueueDetail CQD

where

CCD.sessionID=CQD.sessionID

and CCD.sessionSeqNum=CQD.sessionSeqNum

and CCD.profileID=CQD.profileID

and CCD.nodeID=CQD.nodeID

and CCD.applicationName='FLVS'

and CCD.contactType=1

and contactDisposition in (1,2)

and CCD.startDateTime >='2011-01-01 00:00:00.000'

and CCD.startDateTime <='2011-12-31 00:59:59.000'

GROUP BY RIGHT(CONVERT(VARCHAR, startDateTime , 106), 8)

ORDER BY 1

The below mentioned articles will give you more idea on CROSS TABS and PIVOTS

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Mark's excellent query looks like it should return the data you are looking for.

However if I could make a couple of suggestions. You are using the old style joins. You should consider changing this to the ANSI-92 style joins. Also you should avoid using ordinal positions for ordering. It is more difficult to read and if your query changes you run the risk of messing up your order by. You can use the column alias in the order by. The last change I would recommend is in your date checks. You have the possibility of letting some rows from the last few milliseconds. I would instead check for less than the next day.

select right(convert(varchar, CCD.startDateTime , 106), 8) as startmonth,

sum(case when CCD.contactDisposition = 1 then 1 else 0 end) as TotalCalls1,

sum(case when CCD.contactDisposition = 2 then 1 else 0 end) as TotalCalls2

from ContactCallDetail CCD

inner join ContactQueueDetail CQD on CCD.sessionID = CQD.sessionID

and CCD.sessionSeqNum = CQD.sessionSeqNum

and CCD.profileID = CQD.profileID

and CCD.nodeID = CQD.nodeID

where

CCD.applicationName = 'FLVS'

and CCD.contactType = 1

and contactDisposition in (1, 2)

and CCD.startDateTime >= '2011-01-01 00:00:00.000'

and CCD.startDateTime < '2012-01-01 00:00:00.000'

group by right(convert(varchar, startDateTime , 106), 8)

order by startmonth

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

keshava.murthy SSC Enthusiast Points: 193 More actions · Answer 4

Thank you all very much for instant response. It works fine