SQLTXT.txt

SELECT r.WYDZ & r.DAY AS ID, r.MOnth, r.WEEk, r.DAY, r.WYDZ, r.REFRDAT, max(TERMINOWOή.IL_PLAN) AS IL_PLAN, min(ter.MADE_ON_TIME) AS MADE_ON_TIME, max(ter.MADE_TOO_LATE) AS MADE_TOO_LATE, round( min(ter.MADE_ON_TIME)/max(TERMINOWOή.IL_PLAN)*100,2) AS TERMIN
FROM (Select  q.MOnth,q.WEEk,wrk.DAY,wrk.WYDZ,max(MinimumOfREFR_DATE) as REFRDAT from 
(select * from  (SELECT TERMINOWOŒÆ.DAY, Left([REFR],8) AS Dzieñ, TERMINOWOŒÆ.WYDZ, Min(TERMINOWOŒÆ.REFR_DATE) AS MinimumOfREFR_DATE FROM TERMINOWOŒÆ GROUP BY TERMINOWOŒÆ.DAY, Left([REFR],8), TERMINOWOŒÆ.WYDZ, TERMINOWOŒÆ.TYP_RAP HAVING ((TERMINOWOŒÆ.TYP_RAP='POPRZEDNI DZIEÑ' and TERMINOWOŒÆ.WYDZ<>'MAG_WE') ) 
ORDER BY TERMINOWOŒÆ.DAY, Left([REFR],8), TERMINOWOŒÆ.WYDZ) union all (SELECT TERMINOWOŒÆ.DAY, Left([REFR],8) AS Dzieñ, TERMINOWOŒÆ.WYDZ, max(TERMINOWOŒÆ.REFR_DATE) AS MinimumOfREFR_DATE FROM TERMINOWOŒÆ GROUP BY TERMINOWOŒÆ.DAY, Left([REFR],8), TERMINOWOŒÆ.WYDZ, TERMINOWOŒÆ.TYP_RAP HAVING ((TERMINOWOŒÆ.TYP_RAP='BIE¯¥CY DZIEÑ' and TERMINOWOŒÆ.WYDZ='MAG_WE') )ORDER BY TERMINOWOŒÆ.DAY, Left([REFR],8), TERMINOWOŒÆ.WYDZ) ) as wrk,(select MOnth,WEEk,valid_date,department from wydaj group by MOnth,WEEk,valid_date,department 
union all 
select MOnth,WEEk,valid_date,'MAG_WE' as department from wydaj where department='400ST' group by MOnth,WEEk,valid_date ) as q where wrk.DAY=q.valid_date and wrk.WYDZ=q.department group by q.MOnth,q.WEEk,wrk.DAY,wrk.WYDZ)  AS r, TERMINOWOŒÆ, (select wydz,day,refr_date,MADE_ON_TIME,MADE_TOO_LATE from TERMINOWOŒÆ where (WYDZ<>'MAG_WE' and TYP_RAP in( 'POPRZEDNI DZIEÑ','BIE¯¥CY DZIEÑ')) or  (WYDZ='MAG_WE' and TYP_RAP='BIE¯¥CY DZIEÑ') )  AS ter
WHERE r.REFRDAT=TERMINOWOή.REFR_DATE and ter.day=r.day and r.wydz=TERMINOWOή.wydz and ter.REFR_DATE>=r.REFRDAT and ter.wydz= r.wydz
GROUP BY r.MOnth, r.WEEk, r.DAY, r.WYDZ, r.REFRDAT;