Subversion Repositories public

-- Datum von: ???, Bis: ???

-- Mitarbeiter: Theofilu Andreas (1)

select ta_name, sum(al_hours), ta_num, ta_start, ta_duration, ta_hash

from task right join plan on pl_num = ta_plnum and pl_status in (2,3,4,5) and

     pl_prnum = 90001

     left join allocation on al_task = ta_num and al_ressource = 1

   where ta_num in (

	select ta_num from task where

	ta_meeting = 0 and ta_level > 1 and

	(ta_start between 1145491200 and 1146009600 or

	ta_start+ta_duration*86400 between 1138752000 and 1146355200 or

	1145491200 between ta_start and ta_start+ta_duration*86400 or

	1146009600 between ta_start and ta_start+ta_duration*86400

	or ta_num in (

		select distinct wd_task from wdone where

		wd_minum = 1 and wd_prnum = 90001 and

		wd_datum between 1145491200 and 1146009600

		order by wd_task desc)

	) order by pl_num desc)

group by ta_num, ta_hash, ta_start, ta_duration, ta_name order by ta_num desc;

-- Alle Tasks die bebuchbar sind bzw. auf denen IST-Buchungen vorhanden sind

select distinct on(ta_num) ta_name, ta_num, ta_start, ta_duration, ta_hash

from task, allocation

	where al_task = ta_num and al_ressource = 1 and

		ta_meeting = 0 and ta_level > 1 and

		(ta_plnum = 66 or ta_plnum in (

		select distinct pl_num from wdone, plan, task where 

		ta_num = wd_task and pl_num = ta_plnum and

		wd_prnum = 90001 and wd_minum = 1 and

		wd_datum between 1145491200 and 1146009600 and

		pl_status in (2,3,4,5) order by pl_num desc)) and

	(ta_start between 1145491200 and 1146009600 or

	ta_start+ta_duration*86400 between 1145491200 and 1146009600 or

	1145491200 between ta_start and ta_start+ta_duration*86400 or

	1146009600 between ta_start and ta_start+ta_duration*86400)

order by ta_num desc;

select * from temptask order by tmp_num;

drop table temptask;