Search code examples
intersystems-cacheintersystems

CacheSQL calculate total time

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!

Solution

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