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.
When you create a table like this:
And create a first record like this:
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 timesWhen 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.Of course the column
x
can be deleted (optionally) with next statement, but you cannot add more records using the previous statement:Finally, to return the next 14 day from today: