Time Addition Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Time Addition Problem

Author

Topic

yehpal
Starting Member

3 Posts

Posted - 2009-01-15 : 02:46:41

I am using the following code to Add up the difference of two time fields but It's not showing the correct record when the hours part increases above 24 means It shows the correct addition only if It's between 24 hours. For example

WaitingTimeTotal (hh:mm:ss)
----------------
03:18:46
03:25:06
________
06:43:52 which is correct but

WaitingTimeTotal (hh:mm:ss)
----------------
03:18:46
03:25:06
16:23:46
07:24:20
________
wrong answer!!!

The code i am using is

convert(varchar,convert(DateTime, SUM(convert(real, ConfTime - EnterTime))),108)WaitingTimeTotal

Can you please guide me. Thanks in advance !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 02:56:42

See DECLARE" rel="nofollow">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT '03:18:46' UNION ALL
SELECT '03:25:06' UNION ALL
SELECT '16:23:46' UNION ALL
SELECT '07:24:20'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, s, 0), 8), 1, 2, CAST(s / 3600 AS VARCHAR(12)))
FROM (
SELECT SUM(DATEDIFF(SECOND, 0, dt)) AS s
FROM @Sample
) AS d

E 12°55'05.63"
N 56°04'39.26"

yehpal
Starting Member

3 Posts

Posted - 2009-01-15 : 03:23:15

quote:
Originally posted by Peso

See DECLARE" rel="nofollow">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT '03:18:46' UNION ALL
SELECT '03:25:06' UNION ALL
SELECT '16:23:46' UNION ALL
SELECT '07:24:20'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, s, 0), 8), 1, 2, CAST(s / 3600 AS VARCHAR(12)))
FROM (
SELECT SUM(DATEDIFF(SECOND, 0, dt)) AS s
FROM @Sample
) AS d

E 12°55'05.63"
N 56°04'39.26"

Thanks a bunch bro :)

yehpal
Starting Member

3 Posts

Posted - 2009-01-15 : 05:28:49

A bit of help needed how can i insert this query in the query which i am using that's fetching other records from the table this is what i am using:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[patientServerPerDay]

@date datetime= null,
@date1 datetime= null,
@triagecategory varchar(1000)=null,
@Disposition varchar(15)=null,
@AgeYear int=null,
@AgeMonth int=null,
@AgeDay int=null

as

select convert(varchar,TmStamp,110)dt,
convert(varchar,DATENAME(dw,convert(varchar,TmStamp,110)),110)Dy,
count(EndTime)PatientServed,

--How can i insert the above query here...

convert(varchar, convert(datetime, avg(convert(real, ConfTime - EnterTime))),108)WaitingTimeAverage,
convert(varchar, convert(datetime, sum(convert(real, EndTime - StartTime))),108)ServiceTimeTotal,
convert(varchar, convert(datetime, avg(convert(real, EndTime - StartTime))),108)ServiceTimeAverage

from visit

where

((@date IS NULL) OR (convert(varchar,TmStamp,110) >= @date)) AND
((@date1 IS NULL) OR (convert(varchar,TmStamp,110) <= @date1)) AND
((@triagecategory IS NULL) OR (NoteText=@triagecategory))AND
((@Disposition IS NULL) OR (Disposition=@Disposition))AND
((@AgeYear IS NULL) OR (AgeYear=@AgeYear))AND
((@AgeMonth IS NULL)OR (AgeMonth=@AgeMonth))AND
((@AgeDay IS NULL)OR (AgeDay= @AgeDay))

group by convert(varchar, TmStamp,110)
order by convert(varchar, TmStamp,110)

Thanks you so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 05:39:52

First execute this code to create the function

CREATE FUNCTION dbo.fnSeconds2Time
(
	@Seconds INT
)
RETURNS VARCHAR(13)
AS
BEGIN
	RETURN	STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(@Seconds), '19000101'), 8), 1, 2, CAST(@Seconds / 3600 AS VARCHAR(12)))
END

And the your rewritten procedure as this

ALTER PROCEDURE dbo.patientServerPerDay
(
	@date DATETIME = NULL,
	@date1 DATETIME = NULL,
	@triagecategory VARCHAR(1000) = NULL,
	@Disposition VARCHAR(15) = NULL,
	@AgeYear INT = NULL,
	@AgeMonth INT = NULL,
	@AgeDay INT = NULL
)
AS

SET NOCOUNT ON

SELECT		CONVERT(CHAR(10), TmStamp, 110) AS dt,
		DATENAME(dw, TmStamp) AS dy,
		COUNT(EndTime) AS PatientServed,
		dbo.fnSeconds2Time(SUM(DATEDIFF(SECOND, EnterTime, ConfTime))) AS WaitingTimeAverage,
		dbo.fnSeconds2Time(SUM(DATEDIFF(SECOND, StartTime, EndTime))) AS ServiceTimeTotal,
		dbo.fnSeconds2Time(AVG(DATEDIFF(SECOND, StartTime, EndTime))) AS ServiceTimeAverage 
FROM		Visit
WHERE		(@date IS NULL OR TmStamp >= @date)
		AND (@date1 IS NULL OR TmStamp < DATEADD(DAY, 1, @date1)
		AND (@triagecategory IS NULL OR NoteText = @triagecategory)
		AND (@Disposition IS NULL OR Disposition = @Disposition)
		AND (@AgeYear IS NULL OR AgeYear = @AgeYear)
		AND (@AgeMonth IS NULL OR AgeMonth = @AgeMonth)
		AND (@AgeDay IS NULL OR AgeDay = @AgeDay) 
GROUP BY	CONVERT(CHAR(10), TmStamp, 110)
ORDER BY	TmStamp

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources