Access-o-Mania
Access-Forum => Tabelle/Abfrage => Thema gestartet von: eviblabla am Juli 04, 2018, 10:15:56
-
SELECT week, ROUND(VBZ_Woche.wert-VBZ_Basis.wert,3) AS korrektur, IIF(SUM(potential_ist) < (VBZ_Woche.wert-VBZ_Basis.wert)*-1, ROUND(SUM(potential_ist),3)*-1, ROUND(VBZ_Woche.wert-VBZ_Basis.wert,3)) AS p_ist_sum,
ROUND(SUM(potential_vsx),3)*-1 AS p_vsx_sum, ROUND(SUM(potential_vs),3) AS p_vs_sum FROM (((SELECT gueltig, hg, jahr, woche, k_ID, ende, potential as potential_ist, 0 as potential_vs, 0 as potential_vsx, IIf(IsNull(ende), Null, CWeek(ende)) As week
FROM Import WHERE hg = 4 Union
SELECT gueltig, hg, jahr, woche, k_ID, ende, 0 as potential_ist, potential as potential_vs, 0 as potential_vsx, IIf(Import.hg <= 3, IIf(IsNull(ziel), Null, CWeek(ziel)), IIf(IsNull(ende), Null, CWeek(ende))) As week
FROM Import Union
SELECT gueltig, hg, jahr, woche, k_ID, ende, 0 as potential_ist, 0 as potential_vs, potential as potential_vsx, IIf(Import.hg <= 3 And IIf(IsNull(ziel), Null, CWeek(ziel)) > 23, IIf(IsNull(ziel), Null, CWeek(ziel)), IIf(IIf(IsNull(ende), Null, CWeek(ende)) <= 23, IIf(IsNull(ende), Null, CWeek(en
de)), Null)) As week FROM Import) AS tbl1
INNER JOIN Kostenstelle ON tbl1.k_ID = Kostenstelle.k_ID)
LEFT JOIN VBZ_Basis ON tbl1.k_ID = VBZ_Basis.k_ID)
LEFT JOIN VBZ_Woche ON tbl1.week = VBZ_Woche.woche
WHERE tbl1.gueltig <> 0 AND tbl1.hg <> 9 AND tbl1.jahr = 2018 AND tbl1.woche = 23 AND Kostenstelle.name = '3105' AND week >= 1 AND VBZ_Woche.jahr = 2018 AND VBZ_Woche.woche = week AND (Year(tbl1.ende) = 2018 OR isNull(tbl1.ende) OR (Year(tbl1.ende) = 2018-1 AND Month(tbl1.ende) = 12 AND CWeek(tbl1.ende) = 1) OR (Year(tbl1.ende) = 2018+1 AND Month(tbl1.ende) = 1 AND CWeek(tbl1.ende) >= 51))
GROUP BY week, VBZ_Basis.wert, VBZ_Woche.wert;
An der Stelle: 'IIF(import.hg <=3 AND IIF(IsNull(ziel),Null,CWeek(ziel)) > 23, IIF(IsNull(ziel),Null,CWeek(ziel)),IIF(IIF(IsNull(ende), Null, CWeek(ende)) <= 23, IIF(IsNull(ende), Null, CWeek(ende)),Null))'
-
So eine Abfrage wirkt abschreckend. Hier noch einmal in lesbaren Zustand.
SELECT week,
ROUND( VBZ_Woche.wert - VBZ_Basis.wert, 3 ) AS korrektur,
IIF( SUM( potential_ist ) < ( VBZ_Woche.wert - VBZ_Basis.wert ) * -1,
ROUND( SUM( potential_ist ), 3 ) * -1,
ROUND( VBZ_Woche.wert - VBZ_Basis.wert, 3 ) ) AS p_ist_sum,
ROUND( SUM( potential_vsx ), 3 ) * -1 AS p_vsx_sum,
ROUND( SUM( potential_vs ), 3 ) AS p_vs_sum
FROM ( ( ( SELECT gueltig,
hg,
jahr,
woche,
k_ID,
ende,
potential as potential_ist,
0 as potential_vs,
0 as potential_vsx,
IIf( IsNull( ende ), Null, CWeek( ende ) ) As week
FROM Import
WHERE hg = 4
Union
SELECT gueltig,
hg,
jahr,
woche,
k_ID,
ende,
0 as potential_ist,
potential as potential_vs,
0 as potential_vsx,
IIf( Import.hg <= 3,
IIf( IsNull( ziel ), Null, CWeek( ziel ) ),
IIf( IsNull( ende ), Null, CWeek( ende ) ) ) As week
FROM Import
Union
SELECT gueltig,
hg,
jahr,
woche,
k_ID,
ende,
0 as potential_ist,
0 as potential_vs,
potential as potential_vsx,
IIf( Import.hg <= 3 And
IIf( IsNull( ziel ), Null, CWeek( ziel ) ) > 23,
IIf( IsNull( ziel ), Null, CWeek( ziel ) ),
IIf( IIf( IsNull( ende ), Null, CWeek( ende ) ) <= 23,
IIf( IsNull( ende ), Null, CWeek( ende ) ), Null) ) As week
FROM Import) AS tbl1
INNER JOIN Kostenstelle
ON tbl1.k_ID = Kostenstelle.k_ID)
LEFT JOIN VBZ_Basis
ON tbl1.k_ID = VBZ_Basis.k_ID)
LEFT JOIN VBZ_Woche
ON tbl1.week = VBZ_Woche.woche
WHERE tbl1.gueltig <> 0 AND
tbl1.hg <> 9 AND
tbl1.jahr = 2018 AND
tbl1.woche = 23 AND
Kostenstelle.name = '3105' AND
week >= 1 AND
VBZ_Woche.jahr = 2018 AND
VBZ_Woche.woche = week AND
( Year( tbl1.ende ) = 2018 OR
IsNull( tbl1.ende ) OR
( Year( tbl1.ende ) = 2018 - 1 AND
Month( tbl1.ende ) = 12 AND
CWeek( tbl1.ende ) = 1 ) OR
( Year( tbl1.ende ) = 2018 + 1 AND
Month( tbl1.ende ) = 1 AND
CWeek( tbl1.ende ) >= 51 ) )
GROUP BY week, VBZ_Basis.wert, VBZ_Woche.wert;
Ich würde die Einzelabfrage prüfen und CWeek Null-tauglich machen, um die vielen IIfs loszuwerden.