Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

SQL query question/help

Hey all,

Looking for some help to group this by month instead of day...Not sure if anyone out there can help but if I post this on a sql forum I just would be even more lost...I know enough to be dangerous but this is a little out of my league. Any help would be appreciated. If not, I'll find someone that knows SQL but resources are limited on my end. I'm just looking for total number of calls for these specific dialed numbers.

Here's what I'm doing:

declare @bd DateTime
declare @ed DateTime

set @bd = '2012/3/31'    -- Set the start date for the result set here
set @ed = '2012/5/10'    -- Set the end date for the result set here

select convert(varchar(10), DateTime, 101) Date, count(*) ATTEMPTS, DialedNumberString from t_Route_Call_Detail where DateTime >= @bd and DateTime < @ed
and DialedNumberString in ('1811122','6218')
and ScriptID <> 22513
group by (convert(varchar(10), DateTime, 101)), DialedNumberString
order by (convert(varchar(10), DateTime, 101)

   

I'm looking to group it by month instead of a day output. I looked at a few sql sites and they said to use this              

SELECT

        month(dateadd(month, datediff(month, 0, SomeDate),0)) as [month],

        year(dateadd(month, datediff(month, 0, SomeDate),0)) as [year],

GROUP BY dateadd(month, datediff(month, 0, SomeDate),0)

and to sort

ORDER BY dateadd(month, datediff(month, 0, SomeDate),0)

So is there an easiser way to do this or what does SomeDate stand for? Is it just a random placeholder....thanks

Everyone's tags (2)
7 REPLIES

Re: SQL query question/help

Hi,

first of all, don't use them t_* tables. Really. Bad things can happen, for instance, Cisco can switch tables underneath the views and then you'll end up scratching your head and crying over empty result sets. So in this particular case, the correct table name is Route_Call_Detail, and not t_Route_Call_Detail.

Second, you need to change the convert(varchar(10), DateTime101) to

DATEPART(yyyy,DateTime) AS [Year], DATEPART(mm,DateTime) AS [Month] both after the SELECT and the GROUP BY clause.

And the ORDER BY clause: ORDER BY 1,2

G.

New Member

Re: SQL query question/help

Getting and error “incorrect syntax near the keyword ‘AS’…..what am I missing? Also, this is an archive on a sql 2008 machine, so I have to use the t_ for some reason. I’m still used to 2005.

SQL query question/help

Post the whole query so I can take a look at it.

New Member

Re: SQL query question/help

Thanks for the help. Here's what I have and I basically pieced my original one together from a few other queries...not even close to a sql expert but I usually can figure it out by trial and error. Not this one though...

declare @bd DateTime
declare @ed DateTime

set @bd = '2012/1/1'    -- Set the start date for the result set here
set @ed = '2012/5/10'    -- Set the end date for the result set here

select DATEPART(yyyy,DateTime) AS [Year], DATEPART(mm,DateTime) AS [Month], count(*) ATTEMPTS
from Route_Call_Detail where DateTime >= @bd and DateTime < @ed
and DialedNumberString in ('1811111', '6208')
and ScriptID <> 22513
group by DATEPART(yyyy,DateTime) AS [Year], DATEPART(mm,DateTime)AS [Month]
order by DATEPART(yyyy,DateTime) AS [Year], DATEPART(mm,DateTime)AS [Month]

Re: SQL query question/help

Try this:

declare @bd DateTime
declare @ed DateTime

set @bd = '2012/1/1'    -- Set the start date for the result set here
set @ed = '2012/5/10'    -- Set the end date for the result set here

select

DATEPART(yyyy,DateTime) AS [Year],

DATEPART(mm,DateTime) AS [Month],

count(*) AS [ATTEMPTS]
from Route_Call_Detail

where DateTime >= @bd and DateTime < @ed
and DialedNumberString in ('1811111', '6208')
and ScriptID <> 22513
group by DATEPART(yyyy,DateTime), DATEPART(mm,DateTime)
order by 1,2

G.

New Member

Re: SQL query question/help

Worked! Awesome, thanks. I think I tried every combination except for removing the Year/Month from the group by statement. Thanks again.

Green

SQL query question/help

Also thanks. I've never been able to understand "GROUP BY". Your example is nice and I just tried it on my lab system.

Regards,

Geoff

606
Views
4
Helpful
7
Replies