0

I'm looking to calculate the total time from a column similar to this forum:

https://stackoverflow.com/questions/3054943/calculate-sum-time-with-mysql

Here is my code that is not working:

SEC_TO_TIME(SUM(TIME_TO_SEC(CASE WHEN (SUBSTR(Total_Time,1,2) - 
SUBSTR(ActivityTime,1,2)) < 0 
THEN '0' || DATEDIFF(hh,Total_Time,ActivityTime)-1 || ':' || DATEDIFF(mi,Total_Time,ActivityTime)
WHEN (SUBSTR(Total_Time,1,2) - SUBSTR(ActivityTime,1,2)) >= 0
THEN '0' || DATEDIFF(hh,Total_Time,ActivityTime) || ':' ||
DATEDIFF(mi,Total_Time,ActivityTime)
END))) AS TotalVariance,

Any help would be great! thanks!

Community
  • 1
  • 1
eatonphil
  • 13,115
  • 27
  • 76
  • 133
  • Are you getting ERROR #5540: SQLCODE: -359 Message: User defined SQL Function 'SQLUSER.SEC_TO_TIME' does not exist? Because SEC_TO_TIME isn't built in to Cache. How about including your whole query and what you are trying to do? – psr Sep 05 '12 at 22:12

1 Answers1

1

I'm not sure if this helps you or someone. There are Calculated/SqlComputed properties in ObjectScript. Basically, you can define a property like:

Property TotalTime As %Integer [ Calculated, SqlComputeCode =
        {s {TotalTime}=##class(SomeClass).SomeClassMethod({Id})}, SqlComputed ]

Now you just have to write SomeClassMethod. It can continue any ObjectScript code including %Open, just don't make it cyclic. If you only need some of the other fields to make it, you can go this way:

Property TotalTime As %Integer [ Calculated, SqlComputeCode =
        {s {TotalTime}=##class(Some).SomeCM({PropA}, {PropB})}, SqlComputed ]

There, you will get the properties as input for your class method, instead of getting ID and calling ..%Open(Id).

Either way, you can use the resulting calculated property even for indices.

Vlasec
  • 5,500
  • 3
  • 27
  • 30