Categories
get greatest of two dates in OTBI
Comments
-
Hi - assuming that the two dates are the same field, then you would do MAX("FIELD").
0 -
Hi - dates are in two different field
0 -
Are the fields dates or timestamps? Either way, you should be able to use TIMESTAMPDIFF and some CASE statement. Pseudocode below:
TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE),TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE))
...
CASE when (above formula >= 0) then date on right of method ELSE date on left of method END
Hope this helps.
0 -
OK, try this then:
CASE
WHEN "FIELD1" > "FIELD2" THEN "FIELD1"
WHEN "FIELD2" > "FIELD1" THEN "FIELD2"
END0 -
Wait where's the ELSE? What if they're the same? :-P
0 -
You do not always need the ELSE on there, in this statement it would just return a blank cell.
If you want it to return something for it being the same, you would do something like this:
CASE
WHEN "FIELD1" > "FIELD2" THEN "FIELD1"
WHEN "FIELD2" > "FIELD1" THEN "FIELD2"
ELSE 'SAME'
ENDWhen I tested this, it errored because FIELD1 and FIELD2 are dates ... so I had to use code like this:
CASE
WHEN "FIELD1" > "FIELD2" THEN 'Field 1 name'
WHEN "FIELD2" > "FIELD1" THEN 'Field 2 name'
ELSE 'SAME'
ENDI am not sure how to combine the text/date fields for it to work.
0