Subject AW: [firebird-support] CTE, tricky request
Author Check_Mail
Post date 2019-06-13T14:59:14Z

..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 <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