Hi Stefan,
SET DATEFORMAT YMD
DECLARE @YEAR INT
SET @YEAR = CONVERT(INT,YEAR(GETDATE()))
print CONVERT(INT,YEAR(GETDATE()))
DECLARE @MONTH DATETIME
SET @MONTH = MONTH(GETDATE())
DECLARE @FIRSTOFMONTH DATETIME
SET @FIRSTOFMONTH = GETDATE()
print CONVERT(NVARCHAR(8),@FIRSTOFMONTH,112)
DECLARE @LASTOFMONTH DATETIME
SET @LASTOFMONTH = DATEADD(DAY, -(DAY(DATEADD(MONTH,1, GETDATE()))), DATEADD(MONTH,1, GETDATE()))
print CONVERT(NVARCHAR(8),@LASTOFMONTH,112)
DECLARE @FIRSTNEXTMONTH DATETIME
SET @FIRSTNEXTMONTH = DATEADD(day,1,@LASTOFMONTH)
print CONVERT(NVARCHAR(8),@FIRSTNEXTMONTH,112)
DECLARE @LASTNEXTMONTH DATETIME
SET @LASTNEXTMONTH = DATEADD(DAY, -(DAY(DATEADD(MONTH,1, @FIRSTNEXTMONTH))), DATEADD(MONTH,1, @FIRSTNEXTMONTH))
print CONVERT(NVARCHAR(8),@LASTNEXTMONTH,112)
DECLARE @FIRSTINTWOMONTH DATETIME
SET @FIRSTINTWOMONTH = DATEADD(day, 1, @LASTNEXTMONTH)
print CONVERT(NVARCHAR(8),@FIRSTINTWOMONTH,112)
DECLARE @LASTOFYEAR DATETIME
SET @LASTOFYEAR = CONVERT(NVARCHAR(4),YEAR(GETDATE()))+ '1231'
print CONVERT(NVARCHAR(8),@LASTOFYEAR,112)
declare @tmpUrlaub table (KSTNR int, Nachname nvarchar(50), Vorname nvarchar(50), PNR int, GLZANF decimal(10,2), GLZAKT decimal(10,2), GLZDIFF decimal(10,2),URLANS int, URLVOR int, URLSUM int, URLGEN int, URLPLAN1 int, URLPLAN2 int, URLPLANREST int, URLREST int, URLUNK int, PGNR int)
USE tisoware_production
insert into @tmpUrlaub
SELECT p.KSTNR, p.PSNACHNA AS Nachname, p.PSVORNA AS Vorname, p.PSPERSNR AS PNR,
-- SubSELECT GLZ-Stand 01.01.2012
'GLZANF' =
ISNULL((SELECT CONVERT(DECIMAL(10,2),(Zeitkont.ZKGLEVVM)/60.0)
FROM ZEITKONT
WHERE
ZEITKONT.JAHR = CONVERT(NVARCHAR(4),YEAR(GETDATE()))
AND
ZEITKONT.MONAT = 1
AND
p.PSNR = ZEITKONT.PSNR),0),
--Ausgabe des aktuellen GLZ-Standes
CONVERT(DECIMAL(10,2),((Zeitkont.ZKGLEVVM + Zeitkont.ZKGLEUMB + Zeitkont.ZKGLELFM)/60.0)) AS 'GLZAKT',
0.0 as GLZDIFF,
URLKON.VLFDTAG AS 'URLANS',
URLKON.VVORTAG - URLKON.VEVORTAG AS 'URLVOR',
URLKON.VLFDTAG +(URLKON.VVORTAG - URLKON.VEVORTAG) AS 'URLSUM',
URLKON.GLFDTAG + URLKON.GVORTAG AS 'URLGEN',
-- SubSELECT Urlaubstage im 1. Monat
URLPLAN1 =
(SELECT COUNT(PSNR)-COUNT(kfeidate)
FROM ABWKAL
LEFT JOIN
KALFEIER ON KFEIDATE = ABWDATE
AND
KALFEIER.LANR = 'BW'
WHERE
ABWDATE BETWEEN CONVERT(NVARCHAR(8),@FIRSTOFMONTH,112)
AND
CONVERT(NVARCHAR(8),@LASTOFMONTH,112)
AND
LOANR = 'URL'
AND
abwkal.ABWSOLL >0
AND
abwkal.PSNR = p.PSNR),
-- SubSELECT Urlaubstage im 2. Monat
URLPLAN2 =
(SELECT COUNT(PSNR)-COUNT(kfeidate)
FROM ABWKAL
LEFT JOIN
KALFEIER ON KFEIDATE = ABWDATE
AND
KALFEIER.LANR = 'BW'
WHERE
ABWDATE BETWEEN CONVERT(NVARCHAR(8),@FIRSTNEXTMONTH,112)
AND
CONVERT(NVARCHAR(8),@LASTNEXTMONTH,112)
AND
LOANR = 'URL'
AND
abwkal.ABWSOLL >0
AND
abwkal.PSNR = p.PSNR),
-- SubSELECT Urlaubstage des restlichen Jahres
URLPLANREST = (SELECT COUNT(PSNR)-COUNT(kfeidate)
FROM ABWKAL
LEFT JOIN
KALFEIER ON KFEIDATE = ABWDATE
AND
KALFEIER.LANR = 'BW'
WHERE
ABWDATE BETWEEN CONVERT(NVARCHAR(8),@FIRSTINTWOMONTH,112)
AND
CONVERT(NVARCHAR(8),@LASTOFYEAR,112)
AND
LOANR = 'URL'
AND
abwkal.ABWSOLL >0
AND
abwkal.PSNR = p.PSNR),
-- Resturlaub Start
(URLKON.VLFDTAG +(URLKON.VVORTAG - URLKON.VEVORTAG)) -
(URLKON.GLFDTAG + URLKON.GVORTAG) - ((SELECT COUNT(PSNR)-COUNT(kfeidate)
FROM ABWKAL
LEFT JOIN
KALFEIER ON KFEIDATE = ABWDATE
AND
KALFEIER.LANR = 'BW'
WHERE
ABWDATE BETWEEN CONVERT(NVARCHAR(8),@FIRSTOFMONTH,112)
AND
CONVERT(NVARCHAR(8),@LASTOFMONTH,112)
AND
LOANR = 'URL'
AND
abwkal.ABWSOLL >0
AND
abwkal.PSNR = p.PSNR))
-
(SELECT COUNT(PSNR)-COUNT(kfeidate)
FROM ABWKAL
LEFT JOIN
KALFEIER ON KFEIDATE = ABWDATE
AND
KALFEIER.LANR = 'BW'
WHERE
ABWDATE BETWEEN CONVERT(NVARCHAR(8),@FIRSTNEXTMONTH,112)
AND
CONVERT(NVARCHAR(8),@LASTNEXTMONTH,112)
AND
LOANR = 'URL'
AND
abwkal.ABWSOLL >0
AND
abwkal.PSNR = p.PSNR)
AS 'URLREST',0.0 as URLUNK
-- Resturlaub Ende
, p.PGNR
FROM PERSTAMM AS p
INNER JOIN
URLKON ON p.PSNR = URLKON.PSNR
INNER JOIN
ZEITKONT ON p.PSNR = Zeitkont.PSNR
-- PSAUSDAT muss immer aktuelles Datum haben
WHERE
URLKON.JAHR = @YEAR
AND
-- Nur Personengruppe Gewerbliche, Angestellte + OFK & Personal
p.PGNR in (1,2,7)
AND
p.PSAUSDAT > CONVERT(NVARCHAR(8),@FIRSTOFMONTH,112)
AND
--Ausgrenzung der Vorstände
p.PSPERSNR not in(2012,2019,2016)
AND
ZEITKONT.JAHR = CONVERT(NVARCHAR(4),YEAR(GETDATE()))
AND
ZEITKONT.MONAT = MONTH(GETDATE())
ORDER BY KSTNR, PSNACHNA
Danke!
Viele Grüße Holger M. Rößler
