[Solved] How to calculate too plan number

Creating tables and queries

[Solved] How to calculate too plan number

Postby RikardSvenningsen » Mon Jan 14, 2008 3:36 pm

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

Postby DrewJensen » Mon Jan 14, 2008 6:55 pm

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
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: How to calculate to plan number

Postby RikardSvenningsen » Tue Jan 15, 2008 2:11 pm

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests