Time Calculation

Question

Time Calculation

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

Will you ever end up with totals in excess of 24 hours?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

Tomm Carr (7/7/2008)
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.

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

InFrA007 SSC Enthusiast Points: 198 More actions · Answer 5

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...

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

There's nothing stopping you from that... think about it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

InFrA007 SSC Enthusiast Points: 198 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

InFrA007 SSC Enthusiast Points: 198 More actions · Answer 10

InFrA007

SSC Enthusiast

Points: 198

July 8, 2008 at 6:51 am

#839448

Ahh alright, I see now.

Thanks... OP 😛

karthik M One Orange Chip Points: 29139 More actions · Answer 11

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