The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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.

for example:

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.

like a

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.

Tagged:

Answers

  • You can't create a function including a SELECT.
    UDx-s, written in C++, Python, Java or R, don't allow cursors as of yet.
    And you can't CREATE OR REPLACE FUNCTION .... BEGIN ... SELECT .. RETURN .. END; - You can't put a SELECT into the body of a SQL based function.
    Do you need help to create a fullselect returning COUNT(*) of the days in a calendar that are holidays? That would be the workaround. Store that somewhere central, and copy-paste it into all queries needing it - as a Common Table Expression.

  • Hi,
    You can try with Analytic queries and cross join to get the results.
    create local temp table calendar_date ("date" date, holiday int) on commit preserve rows;
    create local temp table item(id int,start_date date, end_date date) on commit preserve rows;

    INSERT INTO calendar_date values('2019-10-01',0);
    INSERT INTO calendar_date values('2019-10-02',0);
    INSERT INTO calendar_date values('2019-10-03',0);
    INSERT INTO calendar_date values('2019-10-04',0);
    INSERT INTO calendar_date values('2019-10-05',0);
    INSERT INTO calendar_date values('2019-10-06',0);
    INSERT INTO calendar_date values('2019-10-07',0);
    INSERT INTO calendar_date values('2019-10-08',0);
    INSERT INTO calendar_date values('2019-10-09',1);
    INSERT INTO calendar_date values('2019-10-10',0);
    INSERT INTO calendar_date values('2019-10-11',0);
    INSERT INTO calendar_date values('2019-10-12',0);
    INSERT INTO calendar_date values('2019-10-13',0);
    INSERT INTO calendar_date values('2019-10-14',0);
    INSERT INTO calendar_date values('2019-10-15',0);
    INSERT INTO calendar_date values('2019-10-16',0);
    INSERT INTO calendar_date values('2019-10-17',0);
    INSERT INTO calendar_date values('2019-10-18',1);
    INSERT INTO calendar_date values('2019-10-19',0);
    INSERT INTO calendar_date values('2019-10-20',0);
    INSERT INTO calendar_date values('2019-10-21',0);
    INSERT INTO calendar_date values('2019-10-22',0);
    INSERT INTO calendar_date values('2019-10-23',0);
    INSERT INTO calendar_date values('2019-10-24',0);
    INSERT INTO calendar_date values('2019-10-25',0);
    INSERT INTO calendar_date values('2019-10-26',0);
    INSERT INTO calendar_date values('2019-10-27',1);
    INSERT INTO calendar_date values('2019-10-28',0);
    INSERT INTO calendar_date values('2019-10-29',0);
    INSERT INTO calendar_date values('2019-10-30',0);
    INSERT INTO calendar_date values('2019-10-31',0);
    INSERT INTO calendar_date values('2019-11-01',0);
    INSERT INTO calendar_date values('2019-11-02',0);
    INSERT INTO calendar_date values('2019-11-03',0);
    INSERT INTO calendar_date values('2019-11-04',0);
    INSERT INTO calendar_date values('2019-11-05',0);
    INSERT INTO calendar_date values('2019-11-06',0);
    INSERT INTO calendar_date values('2019-11-07',0);
    INSERT INTO calendar_date values('2019-11-08',0);
    INSERT INTO calendar_date values('2019-11-09',1);
    INSERT INTO calendar_date values('2019-11-10',0);
    INSERT INTO calendar_date values('2019-11-11',0);
    INSERT INTO calendar_date values('2019-11-12',0);
    INSERT INTO calendar_date values('2019-11-13',0);
    INSERT INTO calendar_date values('2019-11-14',0);
    INSERT INTO calendar_date values('2019-11-15',0);
    INSERT INTO calendar_date values('2019-11-16',0);
    INSERT INTO calendar_date values('2019-11-17',0);
    INSERT INTO calendar_date values('2019-11-18',1);
    INSERT INTO calendar_date values('2019-11-19',0);
    INSERT INTO calendar_date values('2019-11-20',0);
    INSERT INTO calendar_date values('2019-11-21',0);
    INSERT INTO calendar_date values('2019-11-22',0);
    INSERT INTO calendar_date values('2019-11-23',0);
    INSERT INTO calendar_date values('2019-11-24',0);
    INSERT INTO calendar_date values('2019-11-25',0);
    INSERT INTO calendar_date values('2019-11-26',0);
    INSERT INTO calendar_date values('2019-11-27',1);
    INSERT INTO calendar_date values('2019-11-28',0);
    INSERT INTO calendar_date values('2019-11-29',0);
    INSERT INTO calendar_date values('2019-11-30',0);

    INSERT INTO item
    values (1,'2019-10-01','2019-10-20');

    INSERT INTO item
    values (2,'2019-10-01','2019-10-31');

    INSERT INTO item
    values (3,'2019-10-01','2019-10-03');

    INSERT INTO item
    values (4,'2019-10-01','2019-11-30');

    INSERT INTO item
    values (5,'2019-10-01','2019-11-10');

    select distinct id,start_date,end_date,holiday from
    (
    select id,start_date,end_date,sum(holiday) over(partition by id) as holiday from item cross join calendar_date
    where end_date >= date
    order by 1,2
    )A;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.