flog

	-- Display the project hours/timeslots, 
	-- with needed hours per timeslot smeared across each hour,
	-- with the displayed hour localized for the specified time zone
	SELECT
	  @PeriodStartDate AS PeriodStartDate,
	  @ProjNum AS ProjNum,
      e.DOW,
	  e.PartOfDay,
	  e.HourNum,
      f.TotalRequestHours,
      f.TotalSchedHours,
      f.TotalNeedHours,
      f.NumHoursPerPartOfDay,
	  f.NeededBodiesPerHour,
	  f.IdealBodiesPerHour,
	  LTRIM(RTRIM(z.TZ)) + ' - ' + z.Name AS TimeZoneDisplayName, 
      -- Hour numbers in scheduling database are based on 
	  -- Eastern Time (that's why -5 and -4)
	  CASE r.IsDst
		WHEN 0 THEN HourNum - (((Bias + StandardBias)/60) - 5)
		WHEN 1 THEN HourNum - (((Bias + DaylightBias)/60) - 4)
	  END AS LocalHourNum
	FROM dbo.Calendar r 
	INNER JOIN dbo.TimeZoneInfo z
	  ON r.ThisDate = @PeriodStartDate
	INNER JOIN dbo.ProjectHours e 
	  ON z.TZ = @LocalTimeZone
	LEFT OUTER JOIN (
		-- Figure out how many hours per time slot, by day of week, by part of day
		SELECT 
		  d.PeriodStartDate, 
		  d.ProjNum,
          c.DOW,
		  c.PartOfDay,
          d.TotalRequestHours,
          d.TotalSchedHours,
          d.TotalNeedHours,
          c.NumHoursPerPartOfday,
		  d.TotalNeedHours / c.NumHoursPerPartOfDay AS NeededBodiesPerHour,
		  d.TotalRequestHours / c.NumHoursPerPartOfday AS IdealBodiesPerHour
		FROM  (
            -- Count up the number of hours the Ops Mgr defined
            -- for each day's part of day
			SELECT 
              ProjNum,
              DOW,
              PartOfDay,
              COUNT(HourNum) AS NumHoursPerPartOfDay
			FROM dbo.ProjectHours
			WHERE ProjNum = @ProjNum
			GROUP BY 
              ProjNum,
              DOW,
              PartOfDay
		) c INNER JOIN (
            -- Figure out how many of the requested hours we still need to fill
			SELECT 
			  a.PeriodStartDate,
			  a.ProjNum,
			  a.PartOfDay,
			  a.TotalRequestHours,
              b.TotalSchedHours,
			  CASE
				WHEN b.TotalSchedHours Is Null THEN a.TotalRequestHours
				ELSE a.TotalRequestHours - b.TotalSchedHours 
			  END AS TotalNeedHours
			FROM (
				-- The hours requested by Ops Manager, with padding applied, by part of day
				-- 1 = day; 2 = early eve; 3 = late eve; 4 = weekend
				SELECT 
				  PeriodStartDate, 
				  ProjNum, 
				  1 AS PartOfDay,         
				  TotalProdHours * PaddingFactor * PercentDay AS TotalRequestHours
				FROM dbo.OpsMgrRequests
				WHERE PeriodStartDate = @PeriodStartDate
				UNION
				SELECT 
				  PeriodStartDate, 
				  ProjNum, 
				  2 AS PartOfDay,
				  TotalProdHours * PaddingFactor * PercentEveEarly AS TotalRequestHours
				FROM dbo.OpsMgrRequests
				WHERE PeriodStartDate = @PeriodStartDate
				UNION
				SELECT 
				  PeriodStartDate, 
				  ProjNum, 
				  3 AS PartOfDay,
				  TotalProdHours * PaddingFactor * PercentEveLate AS TotalRequestHours
				FROM dbo.OpsMgrRequests
				WHERE PeriodStartDate = @PeriodStartDate
                UNION
                SELECT
                  PeriodStartDate,
                  ProjNum,
                  4 AS PartOfDay,
                  TotalProdHours * PaddingFactor * PercentWkn AS TotalRequestHours
				FROM dbo.OpsMgrRequests
				WHERE PeriodStartDate = @PeriodStartDate
			) a LEFT OUTER JOIN (
				-- The hours we've already scheduled
				SELECT 
				  s.PeriodStartDate, 
				  s.ProjNum, 
				  h.PartOfDay, 
				  SUM(S.Duration) AS TotalSchedHours
				FROM CurrentSchedulesDetails s INNER JOIN ProjectHours h
				  ON s.ProjNum = h.ProjNum
                  AND s.DayOfWeek = h.DOW
				  AND s.StartHour = h.HourNum
				WHERE PeriodStartDate = @PeriodStartDate
                  AND s.ProjNum = @ProjNum
				GROUP BY 
                  s.PeriodStartDate,
                  s.ProjNum,
                  h.PartOfDay
			) b
			ON a.PeriodStartDate = b.PeriodStartDate
			AND a.ProjNum = b.ProjNum
			AND a.PartOfDay = b.PartOfday
		) d
		ON c.ProjNum = d.ProjNum
		AND c.PartOfDay = d.PartOfDay
	) f
	ON e.ProjNum = f.ProjNum
    AND e.DOW = f.DOW
	AND e.PartOfDay = f.PartOfDay

	WHERE e.HourNum BETWEEN 6 AND 23
	AND f.NeededBodiesPerHour Is Not Null
    AND e.HourNum IN (
		-- Exclude hours from the available list in 3-hour shifts
		SELECT StartHour
		FROM dbo.HourShiftsWithFreeSlots(3, f.IdealBodiesPerHour, @PeriodStartDate, @ProjNum)
	)
)