I have a Vertica table named Start_End like:
| name | Started | Ended |
------------------------------------
| Albert | 1970-01-16 | 1970-06-01 |
| Barry | 1992-05-01 | 1992-07-14 |
| Carol | 2001-03-16 | 2001-06-03 |
It has about 100,000 rows.
How can I use months_between to subtract Started from Ended?
The resulting table would look like:
| name | Started | Ended | Month_diff |
-------------------------------------------------
| Albert | 1970-01-16 | 1970-06-01 | 4|
| Barry | 1992-05-01 | 1992-07-14 | 2|
| Carol | 2001-03-16 | 2001-06-03 | 2|
The following will add a blank column:
ALTER TABLE Start_End
ADD COLUMN Month_diff INTEGER
How can I use months_between?
to find out if the behaviour of MONTHS_BETWEEN() (when to return INTEGER, when to return FLOAT, last-day-of-month behaviour, etc) is the one you need. Otherwise, you might want to DATE_TRUNC() the two operands first, for example, as @Gordon Linoff suggests. Or use TIMESTAMPDIFF(month, ...) instead: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/TIMESTAMPDIFF.htm?zoom_highlight=timestampdiff
That said, try this for size:
ALTER TABLE start_end
ADD month_diff INTEGER DEFAULT MONTHS_BETWEEN(ended,started)
Good luck ...