Query to generate rows dynamically

Answers

• aniruddha20

  May 2019 edited May 2019

  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

  0
• aniruddha20

  May 2019

  Is there support available for Vertica ?

  0
• aniruddha20

  May 2019

  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 ?

  0
• aniruddha20

  May 2019

  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;

  0
• Jim_Knicely Administrator

  May 2019

  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)
  

  0