|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 5:29:14 PM
Posts: 1,
Visits: 14
|
|
Hi,
I am having a column where the time is sorted as ddd hh:min:ss. The col has many 3 digit days & when database sums them all...the total is more than "4 digit days".
If I try for interval: day(5) to second(0)....its not working.
Is there a way to accomodate sum of days more than 4 digits?
953 21:34:15
846 19:54:20
748 03:24:65
702 11:34:94
675 23:42:58
644 20:13:40
562 23:08:55
534 16:12:39
468 12:53:58
394 15:25:14
343 08:12:28
298 07:30:98
250 05:39:74
Let me know, how it can be resolved. Thanks
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
The code below converts each interval to days (with decimal part-days), adds them up and then converts it back to a character interval looking string with up to 5 days.
You can only have a maximum of Days(4) to Second interval. (This can easily be upded to larger total of days if things start to take even longer in your busiiness!)
-------------------------------------------
Set up Test Data:
Create Volatile Table T1
(Pk Smallint Not Null
, IntvlCol Interval Day(3) TO SECOND(0)
)
Unique Primary Index (PK)
On Commit preserve Rows
;
Insert Into T1 Values (1,'953 21:34:15');
Insert Into T1 Values (2,'846 19:54:20');
Insert Into T1 Values (3,'748 03:24:65');
Insert Into T1 Values (4,'702 11:34:94');
Insert Into T1 Values (5,'675 23:42:58');
Insert Into T1 Values (6,'644 20:13:40');
Insert Into T1 Values (7,'562 23:08:55');
Insert Into T1 Values (8,'534 16:12:39');
Insert Into T1 Values (9,'468 12:53:58');
Insert Into T1 Values (10,'394 15:25:14');
Insert Into T1 Values (11,'343 08:12:28');
Insert Into T1 Values (12,'298 07:30:98');
Insert Into T1 Values (13,'250 05:39:74');
----------------------------------------------
Add them up:
Select
Sum(
Extract(Day From IntvlCol) +
((Extract(Hour From IntvlCol) * 60 * 60)
+ (Extract(Minute From IntvlCol) *60)
+ Extract(Second From IntvlCol)) / (24.0000 * 60 * 60) ) (Named AllInts)
, Trim(Cast((Allints (Integer)) As Char(6)))||' '
||Cast((Allints Mod 1.0000 *24 (ByteInt)) AS Char(2))||':'
||Cast(((AllInts Mod 1.0000 * 24 * 60 Mod 60) (ByteInt)) As Char(2))||':'
||Cast(((AllInts Mod 1.0000 * 24 * 60 * 60 ) Mod 60 (ByteInt)) As Char(2)) As BigIntvl
From T1
Order By 1
;
HTH
|
|
|
|