USE [GLCTest]
GO

/****** Object:  UserDefinedFunction [dbo].[GetMillDataByDate]    Script Date: 11/25/2009 13:20:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		Erin Quick-Laughlin
-- Create date: 11/24/2009
-- Description:	Collects all mill uptime and tons produced for a 24 hr period starting at @startDateTime
-- =============================================
ALTER FUNCTION [dbo].[GetMillDataByDate]
(
	@startDateTime datetime
)
RETURNS TABLE
AS
RETURN
(
	SELECT
		SUM(dbo.MinutesOnDate(met.StartTime,met.EndTime,@startDateTime)) AS Uptime,
		--SUM(mre.BeltScale * dbo.MinutesOnDate(met.StartTime,met.EndTime,@startDateTime)/met.MINUTES ) AS Tons,
		-- to do this for Mill or Classifier, we must get a list of all products in that run,
			-- get an average of the product's TonsPerHour
			-- multiply that avg TPH times the MinutesOnDate/60
		dbo.AvgTonsPerHourInMillRun(mre.Id) * SUM(dbo.MinutesOnDate(met.StartTime,met.EndTime,@startDateTime)) as Tons,
		dbo.ProductionMinutesOnDate(@startDateTime) AS ProductionMinutes
		--met.MillRecordId,
		--,SUM(mre.BeltScale) as BeltScaleSum
		--met.StartTime,
		--met.EndTime,
		--met.MINUTES
	FROM
		dbo.MillRunEvent AS mre
			INNER JOIN
		dbo.MillEventTimes AS met
			ON met.EventType = 'Run' AND mre.Id = met.OriginalId
	WHERE
		dbo.MinutesOnDate(met.StartTime,met.EndTime,@startDateTime) > 0