ALTER FUNCTION GetRegisterEntries()RETURNS table
AS
RETURN
(
WITH PC ( -- Contains all registrations with previous calculated
			RegisterDateID,
			IsOfPersonRef,
			IsOfCalendarRef,
			RegisterDate,
			RegisterDateStartDateTime,
			RegisterDateEndDateTime,
			CalendarStartDateTime,
			CalendarEndDateTime,
			CreateOnDateRegisterDate,
			FlexInMinutes,
			IsExportedToForeignSystem,
			RegisterEntryId,
			IsOfProjectRef,
			IsOfJobCodeRef,
			IsOfJobcodeValue,
			IsOfRegisterTypeRef,
			Overtime,
			RegisterEntryStartDateTime,
			RegisterEntryEndDateTime,
			RegisterTimeInMin,
			IsOfShowTypeRef,
			IsOfOriginFromRef,
			Comment,
			IsRegisteredByPersonRef,
			BoolValue1,
			BoolValue2,
			BoolValue3,
			IsOfLocationRef,
			IsOfRegisterEntryRef,
			IsOfRegisterGroupEntriesRef,
			IsOfGroupingRef,
			IsOfRegisterGroupTypeRef,
			ManagementBonus,
			RegisterEntryCreatedOnDate,
			BeforeOvertime,
			BeforeSumTime,
			DefaultName,
			RegNum
		) AS  
		(		  
			SELECT	rd.Id RegisterDateID,
					rd.IsOfPersonRef,
					rd.IsOfCalendarRef,
					rd.RegisterDate,
					rd.StartDateTime RegisterDateStartDateTime,
					rd.EndDateTime RegisterDateEndDateTime,
					rd.CalendarStartDateTime,
					rd.CalendarEndDateTime,
					rd.CreateOnDate CreateOnDateRegisterDate,
					rd.FlexInMinutes,
					rd.IsExportedToForeignSystem,
					re.Id RegisterEntryId,
					re.IsOfProjectRef,
					re.IsOfJobCodeRef,
					re.IsOfJobcodeValue,
					re.IsOfRegisterTypeRef,
					re.Overtime,
					re.StartDateTime RegisterEntryStartDateTime,
					re.EndDateTime RegisterEntryEndDateTime,
					re.RegisterTimeInMin2 RegisterTimeInMin,
					re.IsOfShowTypeRef,
					re.IsOfOriginFromRef,
					re.Comment,
					re.IsRegisteredByPersonRef,
					re.BoolValue1,
					re.BoolValue2,
					re.BoolValue3,
					re.IsOfLocationRef,
					re.IsOfRegisterEntryRef,
					re.IsOfRegisterGroupEntriesRef,
					re.IsOfGroupingRef,
					re.IsOfRegisterGroupTypeRef,
					re.ManagementBonus,
					re.CreatedOnDate RegisterEntryCreatedOnDate,
					datediff(minute, 
							 dbo.OnlyTime(rd.StartDateTime), 
							 dbo.OnlyTime(rd.CalendarStartDateTime)) BeforeOvertime, --Overtime before a calendar time
					(
						SELECT SUM(RegisterTimeInMin2) 
						FROM  (
										--Devide long registrations on serveral parts
										SELECT	re2.*,
												re2.RegisterTimeInMin RegisterTimeInMin2,
												CASE WHEN NOT ds.DefaultName like 'Lunch%'
													 THEN 0
													 ELSE 1
												END RegNum
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NOT NULL
										UNION ALL
										SELECT	re2.*, --100% or 50% or 25% of RegisterTimeInMin
												CASE WHEN (re2.RegisterTimeInMin + 60 > DATEDIFF(minute, 
																								 rd2.CalendarStartDateTime, 
																								 rd2.CalendarEndDateTime))
													 THEN 
														 CASE WHEN re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																											rd2.CalendarStartDateTime, 
																										  rd2.CalendarEndDateTime) 
															  THEN re2.RegisterTimeInMin/4
															  ELSE re2.RegisterTimeInMin/2
														 END 
													 ELSE re2.RegisterTimeInMin
												END RegisterTimeInMin2,
												1 RegNum 
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
										UNION ALL	--50% or 25% of RegisterTimeInMin		
										SELECT	re2.*, 
												CASE WHEN re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																								  rd2.CalendarStartDateTime, 
																								  rd2.CalendarEndDateTime) 
													 THEN re2.RegisterTimeInMin/4
													 ELSE re2.RegisterTimeInMin/2
												END  RegisterTimeInMin2,
												2 RegNum
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.RegisterTimeInMin + 60 > DATEDIFF(minute, 
																					 rd2.CalendarStartDateTime, 
																					rd2.CalendarEndDateTime)
											AND re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
										UNION ALL --25% of RegisterTimeInMin
										SELECT	re2.*,
												re2.RegisterTimeInMin/4,
												3 RegNum 
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																					  rd2.CalendarStartDateTime, 
																					  rd2.CalendarEndDateTime)
											AND re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
										UNION ALL --25% of RegisterTimeInMin
										SELECT	re2.*,
												re2.RegisterTimeInMin/4,
												4 RegNum 
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																					  rd2.CalendarStartDateTime, 
																					  rd2.CalendarEndDateTime)
											AND re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
							  ) re3
							  LEFT JOIN DefaultSettingsUnique ds2 on re3.IsOfProjectRef = ds2.IsOfProjectRef 
						WHERE re3.IsOfRegisterDateRef = rd.Id 
							  AND (re3.RegNum < re.RegNum)
							  AND (
									NOT (ds2.DefaultName like ('Lunch%')) 
									OR ds2.DefaultName IS NULL
								  ) 
					) BeforeSumTime, -- Sum of work minutes previous registrations,
					ds.DefaultName,
					re.RegNum
					FROM dbo.RegisterDate rd
						 INNER JOIN 
									(	
										--Devide long registrations on serveral parts
										SELECT	re2.*, --only PartlyIll
												re2.RegisterTimeInMin RegisterTimeInMin2,
												CASE WHEN NOT ds.DefaultName like 'Lunch%'
													 THEN 0
													 ELSE 1
												END RegNum
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NOT NULL
										UNION ALL
										SELECT	re2.*, --100% or 50% or 25% of RegisterTimeInMin
												CASE WHEN (re2.RegisterTimeInMin + 60 > DATEDIFF(minute, 
																								 rd2.CalendarStartDateTime, 
																								 rd2.CalendarEndDateTime))
													 THEN 
														 CASE WHEN re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																											rd2.CalendarStartDateTime, 
																										  rd2.CalendarEndDateTime) 
															  THEN re2.RegisterTimeInMin/4
															  ELSE re2.RegisterTimeInMin/2
														 END 
													 ELSE re2.RegisterTimeInMin
												END RegisterTimeInMin2,
												1 RegNum 
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
										UNION ALL	--50% or 25% of RegisterTimeInMin		
										SELECT	re2.*, 
												CASE WHEN re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																								  rd2.CalendarStartDateTime, 
																								  rd2.CalendarEndDateTime) 
													 THEN re2.RegisterTimeInMin/4
													 ELSE re2.RegisterTimeInMin - re2.RegisterTimeInMin/2
												END  RegisterTimeInMin2,
												2 RegNum
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.RegisterTimeInMin + 60 > DATEDIFF(minute, 
																					 rd2.CalendarStartDateTime, 
																					rd2.CalendarEndDateTime)
											AND re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
										UNION ALL --25% of RegisterTimeInMin
										SELECT	re2.*,
												re2.RegisterTimeInMin/4,
												3 RegNum 
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																					  rd2.CalendarStartDateTime, 
																					  rd2.CalendarEndDateTime)
											AND re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
										UNION ALL --25% of RegisterTimeInMin
										SELECT	re2.*,
												re2.RegisterTimeInMin - (re2.RegisterTimeInMin/4) * 3, --rounding
												4 RegNum 
										FROM RegisterEntries re2
											 INNER JOIN RegisterDate rd2 ON re2.IsOfRegisterDateRef = rd2.Id
											 LEFT JOIN DefaultSettingsUnique ds ON re2.IsOfProjectRef = ds.IsOfProjectRef
										WHERE re2.RegisterTimeInMin/2 + 60 > DATEDIFF(minute, 
																					  rd2.CalendarStartDateTime, 
																					  rd2.CalendarEndDateTime)
											AND re2.IsOfShowTypeRef = 1
											AND ds.DefaultName IS NULL
									) re on re.IsOfRegisterDateRef = rd.Id
						 LEFT JOIN DefaultSettingsUnique ds on re.IsOfProjectRef = ds.IsOfProjectRef
			)  
SELECT *,
		DATEADD(minute, 
				(CASE WHEN t3.RegisterTimeInMinBefore IS NOT NULL 
					  THEN t3.RegisterTimeInMinBefore 
					  ELSE 0 
				 END), 
				t3.RegisterDateStartDateTime) EntryStartDateTime, --Calculate StartDateTime of the Register Entry by RegisterTimeInMinBefore and RegisterDateStartDateTime
		DATEADD(minute, 
				(CASE WHEN t3.RegisterTimeInMinBefore IS NOT NULL 
					  THEN t3.RegisterTimeInMinBefore 
					  ELSE 0 
				 END) + t3.RegisterTimeInMin, 
				t3.RegisterDateStartDateTime) EntryEndDateTime	 --Calculate EndDateTime of the Register Entry by RegisterTimeInMinBefore and RegisterTimeInMin and RegisterDateStartDateTime
FROM
(
	SELECT *,
			(
				SELECT SUM(RegisterEntries.RegisterTimeInMin) 
				FROM (
						SELECT	PC.RegisterEntryId, 
								PC.RegisterDateId, 
								PC.RegisterTimeInMin, 
								1 REType,					--Registrations before CalendarStartDateTime
								RegNum
						FROM PC
						WHERE ( 
								PC.BeforeSumTime IS NULL 
								OR PC.BeforeSumTime < BeforeOvertime 
							  ) 
							  AND (NOT isnull(PC.DefaultName, '') like ('Lunch%')) 
						UNION ALL
						SELECT PC.RegisterEntryId, 
								PC.RegisterDateId, 
								PC.RegisterTimeInMin, 
								2 REType,					--Lunch registration
								RegNum
						FROM PC
						WHERE ( PC.DefaultName like ('Lunch%') )
						UNION ALL
						SELECT PC.RegisterEntryId, 
								PC.RegisterDateId, 
								PC.RegisterTimeInMin, 
								3 REType,						--All registrations after lunch
								RegNum
						FROM PC
						WHERE (PC.BeforeSumTime >= BeforeOvertime ) 
								AND (NOT isnull(PC.DefaultName, '') like ('Lunch%')) 
					) RegisterEntries 
				WHERE RegisterEntries.RegisterDateId = t2.RegisterDateId	
					  AND (RegisterEntries.REType < t2.REType
							OR (RegisterEntries.REType = t2.REType AND RegisterEntries.RegisterEntryId < t2.RegisterEntryId)  
							OR (RegisterEntries.REType = t2.REType AND RegisterEntries.RegisterEntryId = t2.RegisterEntryId AND RegisterEntries.RegNum < t2.RegNum)  
			)	
		) RegisterTimeInMinBefore	--Calculate previous time of the registrations.
	FROM
	(
		SELECT *, 
				1 REType	--Registrations before CalendarStartDateTime
		FROM PC
		WHERE ( PC.BeforeSumTime IS NULL 
				OR PC.BeforeSumTime < BeforeOvertime ) 
				AND (NOT isnull(PC.DefaultName, '') like ('Lunch%')) 
		UNION ALL
		SELECT *,		
				2 REType --Lunch registration
		FROM PC
		WHERE ( PC.DefaultName like ('Lunch%') )
		UNION ALL
		SELECT *,		
				3 REType --All registrations after lunch
		FROM PC
		WHERE (	PC.BeforeSumTime >= BeforeOvertime ) 
				AND (NOT isnull(PC.DefaultName, '') like ('Lunch%')) 
					  
	) t2
) t3
)