CODEWITHSUNDEEP
recursive-querypervasiveactian
David
David

Reputation: 59

How to get a list of dates in Pervasive SQL

Our time & attendance database is a Pervasive/Actian Zen database. What I'm trying to do is create a query that just lists the next 14 days from today. I'll then cross apply this list of dates with employee records so that in effect I have a list of people/dates for the next 14 days.

I've done it with a recursive CTE on SQL server quite easily. I could also do it with a loop in SQL Server too but I can't figure it out with Pervasive SQL. Loops can only exist within Stored Procedures and triggers.

Looking around I thought that this code that I found and adapted might work, but it doesn't (and further research suggests that there isn't a recursive option within Pervasive at all.

WITH RECURSIVE cte_numbers(n, xDate) 
AS (
     SELECT 
           0, CURDATE() + 1
     UNION ALL
    SELECT 
     n+1, 
     dateAdd(day,n,xDate) 
    FROM 
     cte_numbers 
    WHERE n < 14
)
SELECT 
     xDate 
FROM 
cte_numbers;

I just wondered whether anyone could help me write an SQL query that gives me this list of dates, outside of a stored procedure.

Upvotes: 0

Views: 390

Answers (1)

Luuk
Luuk

Reputation: 14920

When you create a table like this:

CREATE TABLE dates(d DATE PRIMARY KEY, x INTEGER);

And create a first record like this:

INSERT INTO dates VALUES ('2021-01-01',0);

Then you can use this statement which doubles the number of records in the table dates, every time it is executed. (so you need to run it a couple of times

When you run it 10 times the table dates will have 21 oktober 2023 as last date. When you run it 12 times the last date will be 19 march 2032.

INSERT INTO dates 
   SELECT 
      DATEADD(DAY,m.m+1,d),
      x+m.m+1 
   from dates
   cross join (select max(x) m from dates) m
   order by d;

Of course the column x can be deleted (optionally) with next statement, but you cannot add more records using the previous statement:

ALTER TABLE dates DROP COLUMN x;

Finally, to return the next 14 day from today:

SELECT d 
FROM DATES 
WHERE d BETWEEN CURDATE( ) AND DATEADD(DAY,13,CURDATE());

Upvotes: 0

Related Questions