Abfrage der Geburtstage in den nächsten 21 Tagen mit Jahresüberschreitung

Question

Hallo zusammen,

ich beiße mir schon seit Tagen an dieser Abfrage die Zähne aus.

Ich möchte alle Geburtstage der nächsten 21 Tage meiner Kollegen anzeigen lassen.

Dies klappt auch mit folgender View:

SELECT     Mitarbeiter_ID, M_Name, M_Vorname, M_Geburtsdatum, M_Einstellungstermin, M_Austrittstermin, 
	   DATEDIFF(day, GETDATE(), CONVERT(datetime, STR(DAY(M_Geburtsdatum)) + '.' + STR(MONTH(M_Geburtsdatum)) + '.' + STR(YEAR(GETDATE())), 104)) AS AnzahlTage
FROM         dbo.tbl_Mitarbeiter
WHERE     (DATEDIFF(day, GETDATE(), CONVERT(datetime, STR(DAY(M_Geburtsdatum)) + '.' + STR(MONTH(M_Geburtsdatum)) + '.' + STR(YEAR(GETDATE())), 104)) >= 0) AND 
           DATEDIFF(day, GETDATE(), CONVERT(datetime, STR(DAY(M_Geburtsdatum)) + '.' + STR(MONTH(M_Geburtsdatum)) + '.' + STR(YEAR(GETDATE())), 104)) <= 90)
ORDER BY AnzahlTage DESC

Aber leider  gibt mir die Abfrage nicht die Geburstage die im kommenden Jahr ausstehen aus, daher habe ich Testweise die 21 Tage in 90 geändert.

zur Info, die AnzahlTage der Geburtstage die in diesem Jahr schon Geburtstag hatten, sind negative Werte.

Vielen Dank im Vorraus

Stefan

Answer 1

Hallo Stefan,

da schießt Du aber echt mit Kanonn auf Spatzen :). Das Problem lässt sich relativ einfach eingrenzen - das ist die "Vorausberechnung" der Jahreszahl. Anbei mal mein Ansatz. Bitte beachte dabei, daß ich lediglich aus Gründen der Übersichtlichkeit und der Reproduzierbarkeit der Wege mit einem SubSelect gearbeitet habe!

DECLARE	@act_date	datetime	=	getdate()
DECLARE	@diff_days int	=	21

DECLARE	@t TABLE
(
	Mitarbeiter_Id	int	IDENTITY,
	M_Name			varchar(20),
	M_Vorname		varchar(20),
	M_Geburtstag	datetime
);

INSERT INTO @t
(M_Name, M_Vorname, M_Geburtstag)
VALUES
('Duck', 'Donald', '19690405'),
('Duck', 'Daisy', '19981130'),
('Maus', 'Mickey', '19601201'),
('Maus', 'Minnie', '19751128'),
('Gans', 'Gustav', '19800101')

SELECT	r.Mitarbeiter_Id,
	r.M_Name,
	r.M_Vorname,
	r.M_Geburtstag,
	DATEADD(yy, r.AddYear, r.NextBirthday),
	DATEDIFF(dd, @act_date, DATEADD(YY, r.AddYear, r.NextBirthday))
FROM	(
		SELECT	*,
		CASE WHEN SUBSTRING(CONVERT(char(8), M_Geburtstag, 112), 5, 4) <= SUBSTRING(CONVERT(char(8), @act_date, 112), 5, 4)
THEN 1
ELSE 0
END		AS	AddYear,
CAST	(CAST(year(@act_date) AS CHAR(4)) + SUBSTRING(CONVERT(char(8), M_Geburtstag, 112), 5, 4) AS datetime) AS	NextBirthday
FROM @t) AS r
WHERE	DATEDIFF(dd, @act_date, DATEADD(YY, r.AddYear, r.NextBirthday)) <= @Diff_Days

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 2

Hallo Stefan, auf dem Skript von Uwe aufbauend eine kurze Version:

Select Mitarbeiter_Id,
M_Name,
M_Vorname,
M_Geburtstag
FROM @t
where datediff(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,
getdate()), M_Geburtstag)) between 0 and 21

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 3

Danke Uwe,

Ich habe deinen Code bei mir Eingefügt und jetzt klappt es:-)

@ Christiph: Vielen Dank dir auch, aber leider habe ich bei deinem Code das selbe problem mit der Jahresüberschreitung.

 Ich habe aber noch eine Frage.

Wenn ich zusätzlich die Jublilare anzeigen lasse und nur die Jubiliare sehen will, welche im 5er Schritten an der Reihe sind klappt dies noch net bei mir.

Sprich, ich will alle Kollegen anzeigen die 5, 10, 15, 20, 25 etc. jähriges Jubiläum haben.

Da habe ich mir folgende Überlegt:

Ich teile das kommende (daher +1) Jubiläum /5 und möchte alle "natürlichen Zahlen" ausgegeben bekommen.

WHERE ISNUMERIC((DATEDIFF(YEAR, M_Einstellungstermin, GETDATE()) + 1) / 5 ) = 1

Das klappt aber leider so net, er spuckt mir trotzdem alle Ergebnisse raus

Danke Stefan

fdsas

Answer 4

Hallo Stefan,

auch das ist möglich. Wandel mein obiges Beispiel wie folgt um:

SELECT	r.Mitarbeiter_Id,
	r.M_Name,
	r.M_Vorname,
	r.M_Geburtstag,
	DATEADD(yy, r.AddYear, r.NextBirthday)	AS	Birthday,
	DATEDIFF(yy, r.M_Geburtstag, DATEADD(yy, r.AddYear, r.NextBirthday))	AS	Age,
	CASE WHEN DATEDIFF(yy, r.M_Geburtstag, DATEADD(yy, r.AddYear, r.NextBirthday)) % 5 = 0
		 THEN 'Jubilar'
		 ELSE 'Nix Jubilar'
	END,
	DATEDIFF(dd, @act_date, DATEADD(YY, r.AddYear, r.NextBirthday))
FROM	(
		SELECT	*,
				CASE WHEN SUBSTRING(CONVERT(char(8), M_Geburtstag, 112), 5, 4) <= SUBSTRING(CONVERT(char(8), @act_date, 112), 5, 4)
						 THEN 1
						 ELSE 0
					END						AS	AddYear,
					CAST	(
								CAST(year(@act_date) AS CHAR(4)) + SUBSTRING(CONVERT(char(8), M_Geburtstag, 112), 5, 4)
								AS datetime
							)				AS	NextBirthday
			FROM @t
		) r
WHERE	DATEDIFF(dd, @act_date, DATEADD(YY, r.AddYear, r.NextBirthday)) <= @Diff_Days

Ich habe ein weiteres Attribut [Age] hinzugefügt, das lediglich die Differnz in Jahren berechnet.
Mit einem Modulo kannst Du dann herausfinden, ob es sich um ein Jubilar handelt.

Mehr zur MODULO-Berechnung in SQL Server findest Du hier:
http://msdn.microsoft.com/de-de/library/ms190279.aspx

Ist der MODULO = 0, handelt es sich um einen Jubilar!

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 5

Hallo Stefan,
meine erste Lösung war leider zu kurz gegriffen!

Wieder basierend auf Uwe's Script und seine Modulo-Idee aufgreifend.

Select Mitarbeiter_Id,
M_Name,
M_Vorname,
M_Geburtstag,
DATEDIFF(YEAR, M_Geburtstag, @act_date) as Jahre,
case when DATEDIFF(YEAR, M_Geburtstag, @act_date) % 5 = 0
THEN 'Jubilar'
ELSE 'Nix Jubilar'
END as Info
FROM @t
where datediff(DAY, @act_date, case when DATEADD(YEAR, DATEDIFF(YEAR,
M_Geburtstag,@act_date), M_Geburtstag) < @act_date then DATEADD(YEAR,
DATEDIFF(YEAR, M_Geburtstag,@act_date) + 1, M_Geburtstag)
else DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date), M_Geburtstag)
end) between 0 and 21

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 6

Damit auch die Jubilar-Berechnung jahresübergreifend funktioniert, muss das natürlich auch dort berücksichtigt werden:

Select Mitarbeiter_Id,
M_Name,
M_Vorname,
M_Geburtstag,
case when DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date),
M_Geburtstag) < @act_date then DATEADD(YEAR, DATEDIFF(YEAR,
M_Geburtstag,@act_date) + 1, M_Geburtstag)
else DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date), M_Geburtstag)
end as Naechster_Geburtstag, 
DATEDIFF(YEAR, M_Geburtstag, case when DATEADD(YEAR, DATEDIFF(YEAR,
M_Geburtstag,@act_date), M_Geburtstag) < @act_date then DATEADD(YEAR,
DATEDIFF(YEAR, M_Geburtstag,@act_date) + 1, M_Geburtstag)
else DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date), M_Geburtstag)
end ) as Jahre,
case when DATEDIFF(YEAR, M_Geburtstag, case when DATEADD(YEAR,
DATEDIFF(YEAR, M_Geburtstag,@act_date), M_Geburtstag) < @act_date then
DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date) + 1, M_Geburtstag)
else DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date), M_Geburtstag)
end ) % 5 = 0
THEN 'Jubilar'
ELSE 'Kein Jubilar'
END as Info
FROM @t
where datediff(DAY, @act_date, case when DATEADD(YEAR, DATEDIFF(YEAR,
M_Geburtstag,@act_date), M_Geburtstag) < @act_date then DATEADD(YEAR,
DATEDIFF(YEAR, M_Geburtstag,@act_date) + 1, M_Geburtstag)
else DATEADD(YEAR, DATEDIFF(YEAR, M_Geburtstag,@act_date), M_Geburtstag)
end) between 0 and 21

HTH!

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 7

Vielen Dank das war die Lösung

case when DATEDIFF(YEAR, M_Geburtstag, @act_date) % 5 = 0
THEN 'Jubilar'
ELSE 'Nix Jubilar'
END as Info

Danke zusammen.