How can I create a subquery or function
On Vertica, how Can I create a user defined function for find a total holiday days between StartDate and EndDate.
I have two tables 1.table "calendar_day" to keep a calendar days
| date | holiday |
| 1 Jan 20 | 1 |
| 2 Jan 20 | 0 |
| 3 Jan 20 | 0 |
*1 = Holiday 0 <> Holiday
table "ITEM" to keep a data detail include Start and end date for each item.
| ITEM | StartDate | EndDate |
| 1 | 1 Jan 20 | 3 Jan |
| 2 | 1 Jan 20 | 5 Jan |
. . .
So, I would like to find how many holiday days between StartDate and EndDate for each item record.
select sum(holiday) from calendar_day where date between StartDate and EndDate
how can I create a subquery or function to get a result.
Thank you very much.