The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Query to generate rows dynamically

Hi

I have one table named "Employee" like first snap. I want to write a query so it can give me the output like snap2. In snap2, I am showing up 1 for day other than Friday and Saturday. If day is Friday or Saturday then 0.

https://imgur.com/UP6lreH

Best Answer

Answers

  • edited May 1

    Hi

    Actually being a Microsoft experience, I tried using WITH clause to generate but I am getting following error

    _SQL Error [4566] [42V01]: [Vertica]VJDBC ERROR: Relation "cteCalendar" does not exist
    _

    My query is

    with cteCalendar
    AS (
    SELECT employee_number,
    date_start ,
    date_end,
    case when DATE_PART ( 'ISODOW', date_start ) in (5,6) then 0 else 1 end dayval
    FROM PA_ODS.ods_pro_fct_emp_leave
    where date_start < date_end

    union all

    select employee_number,
    TIMESTAMPADD (DD, 1, date_start) as date_start,
    date_end,
    case when DATE_PART ( 'ISODOW', TIMESTAMPADD (DD, 1, date_start)) in (5,6) then 0 else 1 end dayval
    from cteCalendar
    WHERE date_start < date_end and cteCalendar.employee_number=employee_number

    )
    SELECT employee_number, date_start as startdate,date_start as enddate,dayval
    FROM cteCalendar ORDER BY employee_number

  • Is there support available for Vertica ?

  • Thanks for your response !!

    In your query, you have supplied hard coded date '2019-01-31 00:00:00' but I do not want to set that as hard coded, instead I want that to pick Start date, how to make change for that ?

  • I modified my query like this and it worked now. Kindly let me know if this is correct.

    SELECT EmpID,
    StartDate,
    EndDate,
    the_date DayBetween,
    TO_CHAR(the_date, 'Day') DayOfTheWeek,
    (CASE WHEN DAYOFWEEK(the_date) IN (1, 7) THEN 1 ELSE 0 END) DayVal
    FROM employee
    CROSS JOIN (SELECT ts::date the_date
    FROM (SELECT StartDate::TIMESTAMP as tm from employee UNION ALL SELECT '2100-12-31 00:00:00'::TIMESTAMP) as t
    TIMESERIES ts as '1 DAY' OVER (ORDER BY t.tm)) foo
    WHERE the_date BETWEEN StartDate AND EndDate
    ORDER BY EmpID, the_date;

  • Jim_KnicelyJim_Knicely Administrator

    Maybe modify the query to pull the min start date and max end date? That'll give you the full range of dates.

    SELECT EmpID,
    StartDate,
    EndDate,
    the_date DayBetween,
    TO_CHAR(the_date, 'Day') DayOfTheWeek,
    (CASE WHEN DAYOFWEEK(the_date) IN (1, 7) THEN 1 ELSE 0 END) DayVal
    FROM employee
    CROSS JOIN (SELECT ts::date the_date
    FROM (SELECT MIN(StartDate::TIMESTAMP) AS tm FROM employee UNION ALL SELECT MAX(EndDate::TIMESTAMP) AS tm FROM employee) as t
    TIMESERIES ts as '1 DAY' OVER (ORDER BY t.tm)) foo
    WHERE the_date BETWEEN StartDate AND EndDate
    ORDER BY EmpID, the_date;
    

    Example:

    dbadmin=> SELECT * FROM employee ORDER BY EmpID;
     EmpID | StartDate  |  EndDate
    -------+------------+------------
      1001 | 2019-03-13 | 2019-03-18
      1002 | 2019-03-29 | 2019-04-02
      1003 | 1918-09-05 | 1918-09-08
    (3 rows)
    
    dbadmin=> SELECT EmpID,
    dbadmin-> StartDate,
    dbadmin-> EndDate,
    dbadmin-> the_date DayBetween,
    dbadmin-> TO_CHAR(the_date, 'Day') DayOfTheWeek,
    dbadmin-> (CASE WHEN DAYOFWEEK(the_date) IN (1, 7) THEN 1 ELSE 0 END) DayVal
    dbadmin-> FROM employee
    dbadmin-> CROSS JOIN (SELECT ts::date the_date
    dbadmin(> FROM (SELECT MIN(StartDate::TIMESTAMP) AS tm FROM employee UNION ALL SELECT MAX(EndDate::TIMESTAMP) AS tm FROM employee) as t
    dbadmin(> TIMESERIES ts as '1 DAY' OVER (ORDER BY t.tm)) foo
    dbadmin-> WHERE the_date BETWEEN StartDate AND EndDate
    dbadmin-> ORDER BY EmpID, the_date;
     EmpID | StartDate  |  EndDate   | DayBetween | DayOfTheWeek | DayVal
    -------+------------+------------+------------+--------------+--------
      1001 | 2019-03-13 | 2019-03-18 | 2019-03-13 | Wednesday    |      0
      1001 | 2019-03-13 | 2019-03-18 | 2019-03-14 | Thursday     |      0
      1001 | 2019-03-13 | 2019-03-18 | 2019-03-15 | Friday       |      0
      1001 | 2019-03-13 | 2019-03-18 | 2019-03-16 | Saturday     |      1
      1001 | 2019-03-13 | 2019-03-18 | 2019-03-17 | Sunday       |      1
      1001 | 2019-03-13 | 2019-03-18 | 2019-03-18 | Monday       |      0
      1002 | 2019-03-29 | 2019-04-02 | 2019-03-29 | Friday       |      0
      1002 | 2019-03-29 | 2019-04-02 | 2019-03-30 | Saturday     |      1
      1002 | 2019-03-29 | 2019-04-02 | 2019-03-31 | Sunday       |      1
      1002 | 2019-03-29 | 2019-04-02 | 2019-04-01 | Monday       |      0
      1002 | 2019-03-29 | 2019-04-02 | 2019-04-02 | Tuesday      |      0
      1003 | 1918-09-05 | 1918-09-08 | 1918-09-05 | Thursday     |      0
      1003 | 1918-09-05 | 1918-09-08 | 1918-09-06 | Friday       |      0
      1003 | 1918-09-05 | 1918-09-08 | 1918-09-07 | Saturday     |      1
      1003 | 1918-09-05 | 1918-09-08 | 1918-09-08 | Sunday       |      1
    (15 rows)
    

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.