July 7, 2008 at 7:11 am
Will you ever end up with totals in excess of 24 hours?
--Jeff Moden
July 7, 2008 at 7:13 am
I'm sorry to be asking so many questions... I'm just looking for clarification because I believe I have a nasty fast method to do what you want...
--Jeff Moden
July 7, 2008 at 3:29 pm
This works wonders for me:
alter table dbo.CallCost
add Seconds as DateDiff( ss, 0, '1900-01-01 ' + Duration );
Now you have a computed column which very quickly, and behind the scenes, converts the varchar string into seconds. You may also group by or order by on this column. The only problem would occur if you have any duration strings with '24' or higher in the hours position or '60' or higher in the minutes or seconds position. Otoh, this would be a good verification as it would not allow you to enter a value into Duration that was not in the proper format -- overcoming one of the disadvantages of using varchar for data of this type.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 7, 2008 at 5:11 pm
Tomm Carr (7/7/2008)
This works wonders for me:
alter table dbo.CallCostadd Seconds as DateDiff( ss, 0, '1900-01-01 ' + Duration );
Now you have a computed column which very quickly, and behind the scenes, converts the varchar string into seconds. You may also group by or order by on this column. The only problem would occur if you have any duration strings with '24' or higher in the hours position or '60' or higher in the minutes or seconds position. Otoh, this would be a good verification as it would not allow you to enter a value into Duration that was not in the proper format -- overcoming one of the disadvantages of using varchar for data of this type.
No need for the date part, either... the following works just fine...
alter table dbo.CallCost
add Seconds as DateDiff( ss, 0, Duration );
--Jeff Moden
July 8, 2008 at 1:41 am
Hi guys,
Thanks, your method is really fast, but it does not give me the results I am looking for.
Basically I am looking for a total time. so if I have data such as:
Phoneno Duration Cost
------------------------------------
0413451234 00:40:20 5.20
0413451234 00:30:15 20.00
Then when I group by Phoneno, Duration and Cost,
I will have the following result:
0413451234 01:10:35 25.20
That way I can see what the total time is that was spent by an employee in a month, year etc... calling one number and what the cost is to the business, there are other columns involved which give me a count of the number of calls etc.
On another note... I highly doubt we will ever get a value higher than 60 hours, that would mean more than 1 working week on the phone...
July 8, 2008 at 4:32 am
There's nothing stopping you from that... think about it...
--Jeff Moden
July 8, 2008 at 5:35 am
Alright... I was hoping the OP would come up with something himself...
Here's a million rows test table... as usual, read the comments for what each column represents...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDur" has a range of 00:00:00.000 to 23:59:59.999 non-unique date/times which are
-- then turned into VARCHAR Times to represent durations.
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
PhoneNumber = CAST(ABS(CHECKSUM(NEWID()))%50000+1 AS VARCHAR(10)),
Cost = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
Duration = CONVERT(CHAR(8),CAST(RAND(CHECKSUM(NEWID())) AS DATETIME),108)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
SELECT TOP 10 * FROM JBMTest
... and the following code returns the Total Cost and Total Duration as hhhh:mm:ss (yeah... 4 digit hours so can do about 5 years worth of duration)...
SELECT --===== Format the aggregations for display...
tt.PhoneNumber,
CONVERT(CHAR(13),tt.TotalCost,1) AS TotalCost,
STR(DATEDIFF(hh,0,tt.TotalDuration),4)+RIGHT(CONVERT(CHAR(8),tt.TotalDuration,108),6) AS TotalDuration
FROM (--==== Aggregate the cost and duration
SELECT PhoneNumber,
SUM(Cost) AS TotalCost,
DATEADD(ss,SUM(DATEDIFF(ss,0,Duration)),0) AS TotalDuration
FROM dbo.JBMtest
GROUP BY PhoneNumber)tt
The key is... if you have a GUI, the formatting should be done by the GUI... not by SQL because if you go international, the format may have to change based on local settings... if you send formatted data to the GUI, that'll be difficult to make happen.
--Jeff Moden
July 8, 2008 at 6:13 am
Im afraid to ask... OP?
I see what you are doing now and it does work thanks.
The thing is that the table I am retrieving the data from is a production/live database and cannot be modified, the formats and datatypes have been cast in stone and the table is updated via another application.
This includes the hh:mm:ss format.
Thanks for the tip about the GUI side of things, thats exactly what the application is doing at the moment, it performs formatting on the column for the duration to make sure it is in the hh:mm:ss format and it solved the anomoly I had with one of the previous methods.
July 8, 2008 at 6:17 am
Sorry Robert... "OP" is forum shorthand for "Original Poster".
The thing is that the table I am retrieving the data from is a production/live database and cannot be modified, the formats and datatypes have been cast in stone and the table is updated via another application.
This includes the hh:mm:ss format.
That's why I made the test table look like your production table... no changes in format on the table... 😉
--Jeff Moden
July 8, 2008 at 6:51 am
Ahh alright, I see now.
Thanks... OP 😛
July 15, 2008 at 3:55 am
How about this ?
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
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
union all
select '9841645768','00:00:59',20
select Phoneno, Cost = sum(Cost), Hour = sum(convert(int,substring(Duration,1,2))), Mins = sum(convert(int,substring(Duration,4,2))), Secs = sum(convert(int,substring(Duration,7,2)))
into #Sum
from #CallCost
group by Phoneno
select Phoneno, Cost,
Secs = Case
when Secs > 60 then Secs - (60 * (Secs / 60))
else Secs
end,
Mins = Case
when Secs > 60 then Mins + (Secs / 60)
when Mins > 60 then Mins - (60 * (Mins / 60))
else Mins
end ,
Hour = Case
when Mins > 60 then Hour + Mins / 60
else Hour
end
into #Sum1
from #Sum
select Phoneno, Cost,
Secs = Case
when Secs > 60 then Secs - (60 * (Secs / 60))
else Secs
end,
Mins = Case
when Secs > 60 then Mins + (Secs / 60)
when Mins > 60 then Mins - (60 * (Mins / 60))
else Mins
end ,
Hour = Case
when Mins > 60 then Hour + Mins / 60
else Hour
end
from #Sum1
karthik
July 15, 2008 at 12:22 pm
create table CallCost
(
Phoneno varchar(10),
Duration varchar(15),
Cost numeric(8,2)
)
insert into CallCost
select '9841645768','01:20:15',20
union all
select '9841645768','10:00:15',1
union all
select '9940272484','06:55:01',60
union all
select '9940272484','05:01:15',2
union all
select '9940272484','03:10:15',10
union all
select '9841645768','12:00:55',1
Select Phoneno,Sum(Datepart(hh,Duration))+Sum(Datepart(mi,Duration))/60+Sum(Datepart(ss,Duration))/360 as Hours,
Sum(Datepart(mi,Duration))%60+Sum(Datepart(ss,Duration))/60 as Minutes,
Sum(Datepart(ss,Duration))%60 As Seconds,Sum(cost) as TotalCost
From CallCost
Group By Phoneno
July 15, 2008 at 12:32 pm
Correction:
Select Phoneno,Sum(Datepart(hh,Duration))+Sum(Datepart(mi,Duration))/60+Sum(Datepart(ss,Duration))/360 as Hours,
(Sum(Datepart(mi,Duration))%60+Sum(Datepart(ss,Duration))/60)%60 as Minutes,
Sum(Datepart(ss,Duration))%60 As Seconds,Sum(cost) as TotalCost
From CallCost
Group By Phoneno
July 15, 2008 at 12:35 pm
Another correction:
Select Phoneno,Sum(Datepart(hh,Duration))+Sum(Datepart(mi,Duration))/60+Sum(Datepart(ss,Duration))/3600 as Hours,
(Sum(Datepart(mi,Duration))%60+Sum(Datepart(ss,Duration))/60)%60 as Minutes,
Sum(Datepart(ss,Duration))%60 As Seconds,Sum(cost) as TotalCost
From CallCost
Group By Phoneno
July 16, 2008 at 9:21 am
perhaps this is a slightly simpler example
create table #temp(tfield varchar(50))
insert into #temp select '00:00:50'
insert into #temp select '00:01:20'
insert into #temp select '01:00:50'
insert into #temp select '00:00:50'
insert into #temp select '00:00:50'
select sum(datediff(s,0,tfield)) as durationseconds,
sum(datediff(s,0,tfield))/3600 as hours, (sum(datediff(s,0,tfield))%3600)/60 as minutes,(sum(datediff(s,0,tfield))%60) as seconds
from #temp
drop table #temp
MVDBA
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply