IF THEN function

Getting your data onto paper - or the web - Discussing the reports features of Base

IF THEN function

Postby pjwalkerpj » Mon Jan 19, 2009 10:46 am

I have a function in a query that takes a start time and a finish time and gives the difference in minutes.
(DATEDIFF('Minute', "StartTime", "FinishTime)).
Now I wish to determine the appropriate cost for different length of time sessions. In the past there was a constant cost per minute which allowed me to do a simple arithmetic calculation, however the cost structure has now been changed (quite beyond my control) and is non linear. For a 60 minute session the charge is now $50 for a 45 minute session it is $40 and for a 30 minute session it is $30. There will always be only three length of time sessions.
What I now need is an IF THEN function that will deliver $40 for a 45 minute session, $50 for a session greater than 45 and $30 for a session less than 45.
Is there such a thing in Base 3 and if so what is it and how do I apply it.
I would greatly appreciate any help with this. Needless to say 'she who must be obeyed' would be ecstatic
Regards
Pete
OOo 3.0.X on Ms Windows XP
pjwalkerpj
 
Posts: 35
Joined: Fri Oct 24, 2008 8:33 am

Re: IF THEN function

Postby Villeroy » Mon Jan 19, 2009 1:02 pm

Which software are you talking about? Sun Report Builder? Does DATEDIFF('Minute', "StartTime", "FinishTime) really work in the report builder?

 Edit: Sorry, I was unaware of the fact that DATEDIFF works with 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour' like it does with 'dd'='day', 'mm'='month' and 'yy' = 'year'. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27873
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IF THEN function

Postby Villeroy » Mon Jan 19, 2009 2:03 pm

Do you need is something like a spreadsheet's LOOKUP function? LOOKUP(2.5 ; {1;2;3}) returns 2 which is the last value in the sorted array being <= 2.5.
My own approach lookuing up a price by product and date stored in a separate table: [Solved] MAX(X) where X<=:param Does this help a little?

For the most simple distinctions there is a function CASEWHEN(bool, case_true, case_false)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27873
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IF THEN function

Postby pjwalkerpj » Tue Jan 20, 2009 1:37 am

OK let me put it another way
The DATEDIFF("MINUTE", "StartTime", "FinishTime") expression needs to deliver a cost of $50 or $40 or $30 depending on whether the calculation is 60minutes or 45minutes or 30minutes.
I expect the syntax to look something like

(DATEDIFF("MINUTE", "StartTime", "FinishTime") ), (RESULT FROM A LOOK UP TABLE)

This is an expression in the field of a query table.

Maybe I am in the wrong forum, sorry

Pete
OOo 3.0.X on Ms Windows XP
pjwalkerpj
 
Posts: 35
Joined: Fri Oct 24, 2008 8:33 am

Re: IF THEN function

Postby Villeroy » Tue Jan 20, 2009 2:19 pm

I created a lookup table to lookup prices by hours. It's easy to change the time units. I prefer small figures for testing.
Code: Select all   Expand viewCollapse view
Units   Price
0   13.00
6   12.00
12   11.00
18   10.00

The price to pay for one hour is 13.00 for up to 6 hours, 12.00 for >6 to 12 hours,... Primary key is "Units".

My table of random times "TIMES" looks like:
Code: Select all   Expand viewCollapse view
ID   START   END
0   14:11:19   08:27:50
1   19:33:07   01:40:16
2   01:08:34   14:43:42
3   21:38:43   12:53:51
4   08:28:41   12:41:40
...   ...   ...

Primary key is "ID"

I have a view VHOURS to calculate the amount of hours (cutting off all minutes). Don't care about views. You can use the same thing as a query.
Code: Select all   Expand viewCollapse view
SELECT "ID", DATEDIFF( 'HOUR', "START", "END" ) + CASEWHEN( "END" < "START", 24, 0 ) AS "HOURS" FROM "TIMES"


Hours with times look like:
Code: Select all   Expand viewCollapse view
ID   START   END   HOURS
0   14:11:19   08:27:50   18
1   19:33:07   01:40:16   6
2   01:08:34   14:43:42   13
3   21:38:43   12:53:51   15
4   08:28:41   12:41:40   4
5   22:42:30   16:31:09   18

Query "LookupHours" looks up the maximum unit being <="HOURS" for each "ID"
Code: Select all   Expand viewCollapse view
SELECT "V"."ID", MAX( "L"."Units" ) AS "MAX"
FROM "Lookup" AS "L", "VHOURS" AS "V"
WHERE "L"."Units" <= "V"."HOURS" GROUP BY "V"."ID"

Result:
Code: Select all   Expand viewCollapse view
ID   MAX
0   18
1   6
2   12
3   12
4   0
5   18

Linking everything together:
Code: Select all   Expand viewCollapse view
SELECT "TIMES"."ID", "TIMES"."START", "TIMES"."END",
"VHOURS"."HOURS",
"LookupHours"."MAX",
"Lookup"."Price" AS "Price per Hour"
FROM "LookupHours", "TIMES", "Lookup", "VHOURS"
WHERE "LookupHours"."ID" = "TIMES"."ID"
AND "Lookup"."Units" = "LookupHours"."MAX"
AND "TIMES"."ID" = "VHOURS"."ID"
ORDER BY "ID"


Result:
Code: Select all   Expand viewCollapse view
ID   START   END   HOURS   MAX   Price per Hour
0   14:11:19   08:27:50   18   18   10
1   19:33:07   01:40:16   6   6   12
2   01:08:34   14:43:42   13   12   11
3   21:38:43   12:53:51   15   12   11
4   08:28:41   12:41:40   4   0   13
5   22:42:30   16:31:09   18   18   10
...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27873
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IF THEN function

Postby pjwalkerpj » Thu Jan 22, 2009 4:34 am

Thanks Villeroy for your extended explanation.
I have recorded it for much needed in-depth study, but it seems to deliver what I am trying to achieve
Regards
Pete
OOo 3.0.X on Ms Windows XP
pjwalkerpj
 
Posts: 35
Joined: Fri Oct 24, 2008 8:33 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest