[Solved] How to calculate too plan number

[RikardSvenningsen]

Hi
I have some trouble with time calculation so i did it this way:
( HOUR( "SlutTid" ) * 60 ) + ( MINUTE( "SlutTid" ) ) - ( HOUR( "StartTid" ) * 60 ) + ( MINUTE( "StartTid" ) )
I have a table with StartTid, SlutTid and it's only time value.
This gives a plan number i minutes and need som futher calculating.
If I did it this way:
Select ( HOUR( "SlutTid" ) * 60 ) + ( MINUTE( "SlutTid" ) ) AS "TS1", ( HOUR( "StartTid" ) * 60 ) + ( MINUTE( "StartTid" ) ) AS "TS2", "TS1" - "TS2" AS "TS3" from table (table name)
The "TS1" - "TS2" AS "TS3", dos'nt work that could be if the TS1 and TS2 is string, then i need to cconvert it to numbers but how val("ts1") and Convert(some syntax) dosn't work.
To move forward on my conversion I also need to use if... then... else. or when... do... else.. end.
Any syntax on that?
Best regards

Rikard Svenningsen

[DrewJensen]

Hello Rikard,

Well, you are correct the database engine used in the embedded ODB file does not allow you to use column aliases in other parts of the same query.

You can get around this - but it is one extra step.

Create two Base query definitions or create a single query using a sub select.

First example. Using the query designer create query1

Code: Select all

Select ( HOUR( "SlutTid" ) * 60 ) + ( MINUTE( "SlutTid" ) ) AS "TS1", ( HOUR( "StartTid" ) * 60 ) + ( MINUTE( "StartTid" ) ) AS "TS2" FROM tableName

Using the query designer create query2 - this time select query1 in the Add Table window, so the query will end up being

Code: Select all

SELECT *,  "TS1" - "TS2" AS "TS3" from "query1"

Functionally this is same as if you had written it as a sub-select

Code: Select all

SELECT q.*,  q."TS1" -  q."TS2" AS "TS3" from 
   (  Select ( HOUR( "SlutTid" ) * 60 ) + ( MINUTE( "SlutTid" ) ) AS "TS1", ( HOUR( "StartTid" ) * 60 ) + ( MINUTE( "StartTid" ) ) AS "TS2" from table (table name) ) AS q

[RikardSvenningsen]

Thanks Drew, this is great. It works!
Problem solved