GitLab

create view Bewegungsdaten as
With Joins as (Select 
*
from 
BUZEITEN, 
BSCHLUESSEL, 
PERSONAL P1, 
KOSTENSTELLEN 
where 
BZFINR = PEFINR and 
BZPENR = PENR and 
BZBS = BSKN and 
PEKST = KSTLNR and
BZLKZ = 0 and
P1.PEGUELTIG_AB = 
	(Select max(P2.PEGUELTIG_AB)
	 from PERSONAL P2
	 where 
	 P2.PEFINR = P1.PEFINR and
	 P2.PENR = P1.PENR and
	 P2.PEGUELTIG_AB <= BZTAG)
)

Select BZTAG as Datum, BZFINR as Mandantennummer, KSTNR as Kostenstellennummer, KSTBEZ as Kostenstellenbezeichnung, BSKN as Kennzahl, SUM(dbo.getFloatOfTime(BZIST)) as Wert from Joins where not (BSEIN = 'F' and BSART = 'I') Group by BZTAG,BZFINR , KSTNR, KSTBEZ, BSKN
union 
Select BZTAG as Datum, BZFINR as Mandantennummer, KSTNR as Kostenstellennummer, KSTBEZ as Kostenstellenbezeichnung, 'Anwesenheitsstunden' as Kennzahl, SUM(dbo.getFloatOfTime(BZIST)) - SUM(dbo.getFloatOfTime(BZPAUSE)) as Wert from Joins,BUCHUNGEN where BUFINR = PEFINR and BUPENR = PENR and BZTAG = BUTAG and (BSEIN = 'F' and BSART = 'I') Group by BZTAG, BZFINR, KSTNR, KSTBEZ, BSKN
union
Select BUTAG as Datum, BUFINR as Mandantennummer, KSTNR as Kostenstellennummer, KSTBEZ as Kostenstellenbezeichnung,'Sollarbeitszeit' as Kennzahl,SUM(dbo.getFloatOfTime(BUSOLL)) from 
BUCHUNGEN, 
Mitarbeiter, 
KOSTENSTELLEN 
where
BUFINR = PEFINR and
BUPENR = PENR and 
PEKST = KSTLNR
Group by BUTAG,BUFINR, KSTNR, KSTBEZ