## [Solved] How to calculate too plan number

Creating tables and queries

### [Solved] How to calculate too plan number

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
Last edited by RikardSvenningsen on Wed Jan 16, 2008 4:12 pm, edited 1 time in total.
Best regards
Rikard Svenningsen
Denmark
RikardSvenningsen

Posts: 16
Joined: Mon Jan 14, 2008 3:17 pm

### Re: How to calculate to plan number

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   Expand viewCollapse view
`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   Expand viewCollapse view
`SELECT *,  "TS1" - "TS2" AS "TS3" from "query1"`

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

Code: Select all   Expand viewCollapse view
`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`
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04

DrewJensen
Volunteer

Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

### Re: How to calculate to plan number

Thanks Drew, this is great. It works!
Problem solved
Best regards
Rikard Svenningsen
Denmark
RikardSvenningsen

Posts: 16
Joined: Mon Jan 14, 2008 3:17 pm