Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Result set to be modified Expand / Collapse
Author
Message
Posted Tuesday, May 28, 2013 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 4:52 PM
Points: 9, Visits: 15
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.
Post #1457343
Posted Tuesday, May 28, 2013 8:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1457353
Posted Tuesday, May 28, 2013 8:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
You can use CROSS TABS to achieve the results

SELECT	RIGHT(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
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

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/
Post #1457355
Posted Tuesday, May 28, 2013 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1457364
Posted Tuesday, May 28, 2013 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 4:52 PM
Points: 9, Visits: 15
Thank you all very much for instant response. It works fine
Post #1457372
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse