/************* Time Spent (TS) *************/
Select
 [Source]
,[Ticket_ID]
,[Time_Spent_Log_ID]
,[Time_Spent_Submit_DateTime]
,[Person_ID]
,[Total_Time_Spent]
,[Time_Spent_Start_DateTime_Hour]
,[Time_Spent_End_DateTime_Hour]
,[DateTime_Hour]
,[Time_Spent_In_Hour] = Case
    When [Time_Spent_Start_DateTime_Hour] = [DateTime_Hour] and
    [Time_Spent_End_DateTime_Hour] = [DateTime_Hour]
    Then [Total_Time_Spent]
    When [Time_Spent_End_DateTime_Hour] = [DateTime_Hour]
    Then Extract(MINUTE from [Time_Spent_Submit_DateTime])
    When [Time_Spent_Start_DateTime_Hour] = [DateTime_Hour]
    Then 60 - Extract(MINUTE from trunc([Time_Spent_Submit_DateTime] - [Total_Time_Spent],'MI'))
    Else 60 End
FROM (
/************* Create Time Keys (CTK) *************/
Select
 [Source]
,[Ticket_ID]
,[Time_Spent_Log_ID]
,[Time_Spent_Submit_DateTime]
,[Person_ID]
,[Total_Time_Spent]
,[Time_Spent_Start_DateTime_Hour]
,[Time_Spent_End_DateTime_Hour]
,[Earliest_Time_Key] = Case
	When [Time_Spent_Start_DateTime_Hour] < trunc(sysdate() - @Months, 'MM')
	Then trunc(sysdate() - @Months, 'MM')
	Else [Time_Spent_Start_DateTime_Hour] End
,[Latest_Time_Key] = Case
	When [Time_Spent_End_DateTime_Hour] > trunc(
		DateAdd(
			Dateadd(sysdate(),1,'MM')
		,-1,'HH'))
	Then trunc(
		DateAdd(
			Dateadd(sysdate(),1,'MM')
		,-1,'HH'))
	Else [Time_Spent_End_DateTime_Hour] End
FROM (
/************* Various Sources with Times Unioned Together *************/
/************* Source Without Time Union (SWTU) *************/
Select
 [Source]
,[Ticket_ID]
,[Time_Spent_Log_ID]
,[Person_ID]
,[Total_Time_Spent]
,[Time_Spent_Start_DateTime_Hour] = trunc(Case
	When [Row_ID] = 1
	Then dateadd([Work_Day],8,'HH')
	Else dateadd([Work_Day],8,'HH') + (
		(LAG([Hour_Sum],1) Over(Partition By [Person_ID], [Work_Day] Order By [Ticket_ID] Asc))
		* 60 )
	End, 'HH')
,[Time_Spent_End_DateTime_Hour] = trunc(dateadd([Work_Day],8,'HH') + ([Hour_Sum] * 60),'HH')
,[Time_Spent_Submit_DateTime] = dateadd([Work_Day],8,'HH') + ([Hour_Sum] * 60)
From (
Select
 [Entry_ID] as [Ticket_ID]
,[Person_ID]
,[Work_Day]
,[Hours]
,[Row_ID] = ROW_NUMBER() OVER(Partition By [Person_ID], [Work_Day] Order By [Entry_ID] Asc)
,[Hour_Sum] = SUM(Hours]) OVER(Partition By [Person_ID], [Work_Day] Order By [Entry_ID] Asc)
From System.dbo.Daily_Time
Where [Work_Day] >= trunc(sysdate() - @Months,'MM')
Group By [Entry_ID], [Person_ID], [Work_Day], [Hours]
) as SWTU
) as CTK
) as TS
LEFT JOIN Date_Hour_Dimension DHD on
	DHD.[DateTime_Hour] >= TS.Earliest_Time_Key
	and DHD.[DateTime_Hour] <= TS.Latest_Time_Key