July 2, 2008 at 6:14 am
Hi guys,
This is my first post, I am not too experienced with SQL so please bare with me.
I have got a table which stores a time duration as a varchar eg. 00:01:06
indicating hours:minutes:seconds
It is used to store call durations so each row has got an extension, number, cost etc... together with it.
The problem is that I need to perform a Group By in order to find the total cost, number of calls to a particular number, and call duration for each number.
SUM(Cost) for the call costs works fine and COUNT(Number) also works for counting how many calls were made to each number but I cannot do the same for Duration because it is a varchar.
SUM(CAST Duration AS datetime) wont work either...
The idea is so that the results will show each number only once, with a total cost, number of calls and call duration.
Any ideas?
July 2, 2008 at 6:44 am
One thing you can do is to convert your varchar time to its lowest granularity -- seconds -- and then sum the seconds.
EDIT:
For example, perhaps something like:
declare @test-2 table(aGroup char(1), aTime varchar(8))
insert into @test-2
select 'A', '00:00:47' union all
select 'A', '00:08:09' union all
select 'A', '23:59:59' union all
select 'B', '01:01:01'
--select * from @test-2
select
aGroup,
sum( 3600*convert(int, left(aTime, 2)) +
60*convert(int, substring(aTime, 4, 2)) +
convert(int, right(aTime, 2))
) as totalSeconds
from @test-2
group by aGroup
/* -------- Sample Output: --------
aGroup totalSeconds
------ ------------
A 86935
B 3661
*/
With a question that relates to the formatting of the accumulate time -- days:hours:minutes:seconds?
July 2, 2008 at 7:07 am
"stores a time duration as a varchar eg. 00:01:06"
The problem is the physical datatype used and the that formating is stored. More appropriate would be a numeric datatype such as integer and store only seconds.
To convert the current format to seconds:
declare@timevarchar(8)
set@time = '06:12:03'
selectDATEDIFF(ss,cast( '1900-01-01T00:00:00' as datetime) , cast( '1900-01-01T' + @time as datetime) )
Now, the integer seconds column can be aggregated ( summed, averaged, standard deviation)
To convert seconds to a display format:
declare @secondsint
set@seconds= 22323
selectSUBSTRING( CONVERT( varchar(255) , DATEADD(ss,@seconds,cast( '1900-01-01T00:00:00' as datetime) ) , 126 ) , 12, 8)
SQL = Scarcely Qualifies as a Language
July 2, 2008 at 7:53 am
Try out this query and let me know.
create table CallCost
(
Phoneno varchar(10),
Duration varchar(15),
Cost numeric(8,2)
)
insert into CallCost
select '9841645768','00:20:15',20
union all
select '9841645768','00:00:15',1
union all
select '9940272484','00:55:01',60
union all
select '9940272484','00:01:15',2
union all
select '9940272484','00:10:15',10
union all
select '9841645768','00:00:55',1
union all
Query:
select Phoneno,
Sum(Cost),
Sum(convert(int,substring(Duration,1,2))) as Hours,
Sum(convert(int,substring(Duration,4,2))) as Minutes,
Sum(convert(int,substring(Duration,7,2))) as Seconds,
'CalculatedSeconds' = case when Sum(convert(int,substring(Duration,7,2))) /60 >= 1
then Sum(convert(int,substring(Duration,7,2))) - (60* (Sum(convert(int,substring(Duration,7,2)))/60))
when Sum(convert(int,substring(Duration,7,2))) /60 <= 1
then Sum(convert(int,substring(Duration,7,2)))
end,
'CalculatedMinutes' = case when Sum(convert(int,substring(Duration,7,2))) /60 >= 1
then Sum(convert(int,substring(Duration,4,2))) + Sum(convert(int,substring(Duration,7,2)))/60
when Sum(convert(int,substring(Duration,4,2))) /60 >= 1
then Sum(convert(int,substring(Duration,4,2))) - (60* (Sum(convert(int,substring(Duration,4,2)))/60))
end,
'CalculatedHours' = Case when Sum(convert(int,substring(Duration,4,2))) /60 >= 1
then Sum(convert(int,substring(Duration,1,2))) + Sum(convert(int,substring(Duration,4,2))) /60
end
from CallCost
group by Phoneno
karthik
July 2, 2008 at 8:00 am
I have tested for some more data.
insert into CallCost
select '9841145768','00:55:00',1
union all
select '9841145768','01:00:05',1
union all
select '9841145768','00:05:00',1
The above code works fine.
Phone Number Cost Hour Minutes Seconds CalSec CalMin CalHour
98411457683.001605502
98412457683.00016002NULL
984154576822.00020751521NULL
994037248472.00066313161
karthik
July 2, 2008 at 8:14 am
Robert,
Latest code is below.
select Phoneno,
'Cost' = Sum(Cost),
Sum(convert(int,substring(Duration,1,2))) as Hours,
Sum(convert(int,substring(Duration,4,2))) as Minutes,
Sum(convert(int,substring(Duration,7,2))) as Seconds,
'CalculatedSeconds' = case when Sum(convert(int,substring(Duration,7,2))) /60 >= 1
then Sum(convert(int,substring(Duration,7,2))) - (60* (Sum(convert(int,substring(Duration,7,2)))/60))
when Sum(convert(int,substring(Duration,7,2))) /60 <= 1
then Sum(convert(int,substring(Duration,7,2)))
end,
'CalculatedMinutes' = case when Sum(convert(int,substring(Duration,7,2))) /60 >= 1
then Sum(convert(int,substring(Duration,4,2))) + Sum(convert(int,substring(Duration,7,2)))/60
when Sum(convert(int,substring(Duration,4,2))) /60 >= 1
then Sum(convert(int,substring(Duration,4,2))) - (60* (Sum(convert(int,substring(Duration,4,2)))/60))
end,
'CalculatedHours' = Case when Sum(convert(int,substring(Duration,4,2))) /60 >= 1
then Sum(convert(int,substring(Duration,1,2))) + Sum(convert(int,substring(Duration,4,2))) /60
end
into #Temp
from CallCost
group by Phoneno
select Phoneno,Cost,
'Hour' = case when len(convert(varchar,isnull(CalculatedHours,00))) = 1 then '0' + convert(varchar,isnull(CalculatedHours,00)) else convert(varchar,isnull(CalculatedHours,00)) end,
'Minutes' = case when len(convert(varchar,isnull(CalculatedMinutes,00))) = 1 then '0' + convert(varchar,isnull(CalculatedMinutes,00)) else convert(varchar,isnull(CalculatedMinutes,00)) end,
'Seconds' = case when len(convert(varchar,isnull(CalculatedSeconds,00))) = 1 then '0' +convert(varchar,isnull(CalculatedSeconds,00)) else convert(varchar,isnull(CalculatedSeconds,00)) end
from #Temp
Output:
PhoneNo Cost Hr Min Secs
98411457683.00020005
98412457683.00000200
984154576822.00002115
994037248472.00010631
karthik
July 2, 2008 at 8:40 am
My head... it hurts...
Jokes, thanks for all of your suggestions so far.
I am busy trying them all out and I will get back to you once I have made sense of it all.
Its quite a bit to take in.
July 3, 2008 at 3:55 am
Hi guys,
Thanks again for all of your help and suggestions,
karthikeyan,
Your method works perfectly, after working through it carefully it is actually a lot simpler than it looks at first glance but it would of taken me days to figure out that logic on my own... thank you very much.
Regards,
Robert
July 3, 2008 at 5:18 am
Welcome !:)
karthik
July 4, 2008 at 1:13 am
Hi, karthikeyan
I need to borrow your mind again... for some reason with certain data, the minutes do not seem to increment over to hours properly, here is an example set of data below which I am having trouble with.
insert into #CallCost
select '2423731122','00:03:22',20
union all
select '2423731122','00:01:05',20
union all
select '2423731122','00:04:47',20
union all
select '2423731122','00:07:40',20
union all
select '2423731122','00:00:50',20
union all
select '2423731122','00:06:16',20
union all
select '2423731122','00:08:54',20
union all
select '2423731122','00:14:02',20
union all
select '2423731122','00:11:19',20
union all
select '2423731122','00:08:51',20
union all
select '2423731122','00:06:03',20
union all
select '2423731122','00:11:19',20
union all
select '2423731122','00:08:42',20
union all
select '2423731122','00:10:14',20
It returns 00 hours and 103 minutes and 24 seconds for some reason.
It is the strangest thing though, with the test data you provide, the incrementation to hours works perfectly but in the data I am pulling from the database, it doesnt want to tick over to hours for this bit of sample data, or any other data which comes from the database where it goes over into hours...
July 4, 2008 at 3:51 am
Ok dont worry.
I suppose I can give you some background.
The calculation you showed me, I added into the SELECT statement which is being used by an application I am working on.
The application retrieves information from a database and imports it into Excel, it then uses the information to create a PivotChart which is then used for reporting purposes.
All I did to solve the minutes problem was set the column number format to HH:MM:SS and Excel did the rest.
I know its not really solving the problem but its also redundant in a way I suppose.
July 6, 2008 at 12:31 am
Try that, for the previous #CallCost table
select aGroup, sum(datediff(ss, '00:00:00', aTime)) totalSecs,
sum(datediff(ss, '00:00:00', aTime)) / 3600 as hrs,
(sum(datediff(ss, '00:00:00', aTime)) / 60) % 60 as mins, -- % (Modulo)
sum(datediff(ss, '00:00:00', aTime)) % 60 as secs
from #CallCost
group by aGroup
July 6, 2008 at 9:49 am
You did not post the SQL you are running so it will not be possbile to review but here is some SQL with the results:
PhoneNo Duration Cost
2423731122 01:43:24 280.00
Here is the SQL to get the result:
SELECTPhoneno
,SUBSTRING( CONVERT( varchar(255)
, DATEADD(ss
, SUM(DATEDIFF(ss
, cast( '1900-01-01T00:00:00' as datetime )
, cast( '1900-01-01T' + Duration as datetime )
)
)
, cast ( '1900-01-01T00:00:00' as datetime) ) , 126 )
, 12, 8)
,SUM ( Cost ) as TotalCost
from #CallCost
group by Phoneno
SQL = Scarcely Qualifies as a Language
July 6, 2008 at 10:07 pm
robert (7/2/2008)
SUM(CAST Duration AS datetime) wont work either...
Since that's basically the correct way to do it, tell me... what do you mean it won't work?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2008 at 2:21 am
Nabila:
Thanks, your solution works well, and does not have any issues with that dataset which I posted either.
Carl Federl:
Thanks to you too, the result your SQL gives is actually exactly what I am looking for.
Jeff Moden:
When I use:
SUM(CAST Duration AS datetime)
I get the following error message:
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type as an argument.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply