May 28, 2013 at 9:08 am
In the query below:
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
The order by clause is doing its job alphabatical ordering. However, I would like to go in order of months such as Jan, Feb etc.
Can I please get help on this.
May 28, 2013 at 9:14 am
keshava.murthy (5/28/2013)
In the query below: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
The order by clause is doing its job alphabatical ordering. However, I would like to go in order of months such as Jan, Feb etc.
Can I please get help on this.
Just need to add an extra column in your group by so you can order it.
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), month(startDateTime)
order by month(startDateTime)
_______________________________________________________________
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/
May 28, 2013 at 9:19 am
If I use like above suggestion then I will not get the sum which is grouped by the month
May 28, 2013 at 9:36 am
keshava.murthy (5/28/2013)
If I use like above suggestion then I will not get the sum which is grouped by the month
Did you actually try it? It is grouped by MONTH(starttime). Not sure how that won't be grouped by the month.
_______________________________________________________________
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/
May 28, 2013 at 9:44 am
keshava.murthy (5/28/2013)
If I use like above suggestion then I will not get the sum which is grouped by the month
Actually, it will. Give it a try.
May 28, 2013 at 9:51 am
If I try order by month( startdatetime) then it sorts the data by month.
The problem here is when the data spans to multiple years then it will sort as follows:
Jan 2011 450 4000
Jan 2012 200 3651
Feb 2011 300 6354
Feb 2012 400 2654
I want result set instead as follows:
Jan 2011 450 4000
Feb 2011 300 6354
till Dec 2011 and then
Jan 2012 200 3651
Feb 2012 400 2654
and so on
May 28, 2013 at 9:53 am
keshava.murthy (5/28/2013)
If I try order by month( startdatetime) then it sorts the data by month.The problem here is when the data spans to multiple years then it will sort as follows:
Jan 2011 450 4000
Jan 2012 200 3651
Feb 2011 300 6354
Feb 2012 400 2654
I want result set instead as follows:
Jan 2011 450 4000
Feb 2011 300 6354
till Dec 2011 and then
Jan 2012 200 3651
Feb 2012 400 2654
and so on
Your query had a where clause that would prevent data from any year other than 2011.
_______________________________________________________________
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/
May 28, 2013 at 10:13 am
Perhaps the following will give you another idea:
create table dbo.TestSort(
tsid int identity(1,1),
datecol datetime
);
go
/*
Template: Dynamic Tally CTE - 2008
Author: Lynn A. Pettis
Date: 2013-03-12
Site: ISS, Inc -- Colorado Springs, CO
This template is the start of a dynamic Tally table for SQL Server 2008 and later
*/
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
eTally(n) as (select row_number() over (order by (select null)) from e2 a cross join e2 b) -- 10,000 rows
insert into dbo.TestSort(datecol)
select
dateadd(day, n-1, getdate())
from
eTally;
go
select
right(convert(varchar, datecol, 106), 8) as startmonth,
count(*)
from
dbo.TestSort
group by
right(convert(varchar, datecol, 106), 8), year(datecol), month(datecol)
order by
year(datecol), month(datecol);
go
drop table dbo.TestSort;
go
May 28, 2013 at 10:20 am
I will try this way and see.
May 28, 2013 at 11:53 am
Instead of MONTH( startdatetime) use convert(char(6), startdatetime, 112)
May 28, 2013 at 1:26 pm
Don't do yet another character conversion for the sort. Also, you can simplify a lot... no need for separate calcs for year and month. You can sort by DATEDIFF(mm,0,datecol) and all should be well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2013 at 1:28 pm
keshava.murthy (5/28/2013)
If I try order by month( startdatetime) then it sorts the data by month.The problem here is when the data spans to multiple years then it will sort as follows:
Jan 2011 450 4000
Jan 2012 200 3651
Feb 2011 300 6354
Feb 2012 400 2654
I want result set instead as follows:
Jan 2011 450 4000
Feb 2011 300 6354
till Dec 2011 and then
Jan 2012 200 3651
Feb 2012 400 2654
and so on
That type of confusion can easily be avoided in the future by including readily consumable test data. Please see the first link in my signature line below for the proper way to post such problems to avoid such ambiguity.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2013 at 1:36 pm
This works as long as you are with in one year.
If it is more than one year then the data will output as:
Jan-2011,
Jan 2012,
Feb-2011.
Feb-2012,
In this case I want the data to appear as :
Jan-2011,
Feb-2011
---Dec2011.
Jan 2012,
Feb-2012,
----
May 28, 2013 at 1:39 pm
keshava.murthy (5/28/2013)
This works as long as you are with in one year.If it is more than one year then the data will output as:
Jan-2011,
Jan 2012,
Feb-2011.
Feb-2012,
In this case I want the data to appear as :
Jan-2011,
Feb-2011
---Dec2011.
Jan 2012,
Feb-2012,
----
What isn't working here? Would help to see the code that you say doesn't work.
May 28, 2013 at 1:41 pm
Here is the code:
SELECTRIGHT(CONVERT(VARCHAR, CCD.startDateTime , 105), 7) AS startmonth,
SUM( CASE WHEN CCD.contactDisposition = 1 THEN 1 ELSE 0 END ) AS Abandoned,
SUM( CASE WHEN CCD.contactDisposition = 2 THEN 1 ELSE 0 END ) AS Handled
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 <='2012-12-31 00:59:59.000'
GROUP BY RIGHT(CONVERT(VARCHAR, startDateTime , 105), 7),convert(char(6), startdatetime, 112)
ORDER BY RIGHT(CONVERT(VARCHAR, CCD.startDateTime , 105), 7),convert(char(6), startdatetime, 112)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply