...perhaps a solution?
union all
select cast(a.t || b.str as timestamp) as ts, a.bez from tkal_main a,
sub_datum(:vorschau) as b where a.serie = 2
create procedure SUB_DATUM (
days integer)
returns (
str varchar(8))
as
declare variable c_date timestamp;
declare variable t_date timestamp;
declare variable temp_date timestamp;
begin
c_date = current_date;
t_date = current_date + days;
temp_date = c_date;
while (temp_date < t_date) do
begin
str = '.' || lpad(extract(month from temp_date),2,'0') || '.' ||
extract(year from temp_date);
suspend;
temp_date = temp_date + 30;
end
end
it works..
Von: firebird-support@yahoogroups.com
Gesendet: Donnerstag, 13. Juni 2019 16:36
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] CTE, tricky request
Hello,
I would like to generate a list with appointments.
In a table tkal_main I set the day (field "t") of the meeting, every month I
would like to get an entry. The description of the appointment is "bez".
I helps me with a separate table (tmonate, id 1 = January and so on).
With cte.(
Select other termins
Union all
select cast(a.t || '.' || b.id || '.' || extract(year from current_date) as
timestamp) as ts, a.bez from tkal_main a, tmonate b)
select ts, bez from kal where ts >= current_date and ts < current_date +
:vorschau order by ts into :datum, bez do
Vorschau is an integer with days in the future I would show, from tomorrow
to tomorrow + x (vorschau) days
Now I get a List of appointments, but if I would get a month of the next
year, the "year from current_date" is the problem.
Example:
Day 10 of every Month, today is the 13.06.2019, Vorschau = 365 days I would
get:
10.07.2019
10.08.2019
...
10.06.2020
At the time I get only til this December. And I have non indexed reads for
table tmonate, because there is no relation. How can I make it better? With
a List instead of the table tmonate?
Thank you.
Best regards
Olaf
