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.
0
Best Answer
-
Jim_Knicely
- Select Field - Administrator
Here's one way:
dbadmin=> SELECT * FROM employee ORDER BY 1; EmpID | StartDate | EndDate -------+------------+------------ 1001 | 2019-03-13 | 2019-03-18 1002 | 2019-03-29 | 2019-04-02 (2 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 '2019-01-31 00:00:00'::TIMESTAMP as tm UNION ALL SELECT '2100-12-31 00:00:00'::TIMESTAMP) 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 (11 rows)
5
Answers
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;
Maybe modify the query to pull the min start date and max end date? That'll give you the full range of dates.
Example: