PalAzId	ID	KST		Datum	                    Beginn	            	Ende
9	1	2003	2008-05-20 00:00:00.000		1900-01-01 02:15:00		1900-01-01 02:16:00
2	1	2003	2008-05-20 00:00:00.000		1900-01-01 05:15:00		1900-01-01 07:33:00
3	1	2005	2008-05-20 00:00:00.000		1900-01-01 07:33:00		1900-01-01 07:44:00
4	1	2003	2008-05-20 00:00:00.000		1900-01-01 07:44:00		1900-01-01 14:55:00
5	1	2003	2008-05-20 00:00:00.000		1900-01-01 14:55:00		1900-01-01 16:41:00
7	1	2003	2008-05-20 00:00:00.000		1900-01-01 21:00:00		1900-01-01 22:41:00
8	1	2003	2008-05-20 00:00:00.000		1900-01-01 23:00:00		1900-01-01 23:12:00

DECLARE @datum DATETIME 
 
DECLARE @fruehSchichtStart SMALLDATETIME
DECLARE @fruehSchichtEnde SMALLDATETIME
DECLARE @normalZeitEnde SMALLDATETIME
DECLARE @spaetSchichtEnde SMALLDATETIME
 
SET @fruehSchichtStart = '1.1.1900 00:00'
SET @fruehSchichtEnde = '1.1.1900 06:00'
SET @normalZeitEnde = '1.1.1900 22:00'
SET @spaetSchichtEnde = '2.1.1900 00:00'
SET @datum = '20.05.2008'
 
--Frühschicht
SELECT *, 
	Beginn AS RealerBeginn,
	(CASE 
		WHEN ende < @fruehSchichtEnde THEN ende
		ELSE @fruehSchichtEnde
	END) AS RealesEnde,
	DATEDIFF(mi, Beginn,
		(CASE 
			WHEN ende < @fruehSchichtEnde THEN ende
			ELSE @fruehSchichtEnde
		END)
	) AS DauerMinuten, 
	'Frueh' AS Schicht
FROM PalAz 
WHERE Datum=@datum AND Beginn>=@fruehSchichtStart
AND DATEDIFF(mi, Beginn,
		(CASE 
			WHEN ende < @fruehSchichtEnde THEN ende
			ELSE @fruehSchichtEnde
		END)
	)>0
 
UNION
 
--Normalzeit
SELECT *, 
	(CASE 
		WHEN Beginn > @fruehSchichtEnde THEN Beginn
		ELSE @fruehSchichtEnde
	END) AS RealerBeginn,
	(CASE 
		WHEN ende < @normalZeitEnde THEN ende
		ELSE @normalZeitEnde
	END) AS RealesEnde,
	DATEDIFF(
		mi, 
		(CASE 
			WHEN Beginn > @fruehSchichtEnde THEN Beginn
			ELSE @fruehSchichtEnde
		END),
		(CASE 
			WHEN ende < @normalZeitEnde THEN ende
			ELSE @normalZeitEnde
		END)
	) AS DauerMinuten, 
	'Normal' AS Schicht
FROM PalAz 
WHERE Datum=@datum 
AND 
   ((Beginn>=@fruehSchichtEnde AND beginn < @normalZeitEnde) 
     OR (ende > @fruehSchichtEnde ))
AND DATEDIFF(
	mi, 
	(CASE 
		WHEN Beginn > @fruehSchichtEnde THEN Beginn
		ELSE @fruehSchichtEnde
	END),
	(CASE 
		WHEN ende < @normalZeitEnde THEN ende
		ELSE @normalZeitEnde
	END)
)>0
 
UNION
 
--Spätschicht
 
SELECT *, 
	(CASE 
		WHEN Beginn > @normalZeitEnde THEN Beginn
		ELSE @normalZeitEnde
	END) AS RealerBeginn,
	(CASE 
		WHEN ende < @spaetSchichtEnde THEN ende
		ELSE @spaetSchichtEnde
	END) AS RealesEnde,
	DATEDIFF(
		mi, 
		(CASE 
			WHEN Beginn > @normalZeitEnde THEN Beginn
			ELSE @normalZeitEnde
		END),
		(CASE 
			WHEN ende < @spaetSchichtEnde THEN ende
			ELSE @spaetSchichtEnde
		END) 
	) AS DauerMinuten, 
	'Spät' AS Schicht
FROM PalAz 
WHERE Datum=@datum AND (Beginn>=@normalZeitEnde OR Ende>@normalZeitEnde)
--	AND DATEDIFF(
--		mi, 
--		(CASE 
--			WHEN Beginn > @fruehSchichtEnde THEN Beginn
--			ELSE @fruehSchichtEnde
--		END),
--		(CASE 
--			WHEN ende < @normalZeitEnde THEN ende
--			ELSE @normalZeitEnde
--		END)
--	)>0
 
ORDER BY Schicht, RealerBeginn