set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		Kevin McPhail
-- Create date: 07-11-2006
-- Description:	Retrieve BI-Weekly Inventory Report
-- =============================================
ALTER PROCEDURE [dbo].[GetBiWeeklyUsage] 
	-- Add the parameters for the stored procedure here
	
	
AS

        DECLARE @dtGetDate DateTime;
	DECLARE @dtStartDate DateTime;
	DECLARE @dtEndDate DateTime;

	SELECT @dtGetDate = cast(floor(cast(getdate() as float)) as datetime)
	SELECT @dtEndDate =DATEADD(dd, 1 - DATEPART(dw, @dtGetDate), @dtGetDate)
	SELECT @dtStartDate = @dtEndDate-14

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT     
		Site.CompanyName, 
		Site.LocationCity, 
		Site.LocationState, 
		Site.LbsPerInch,
		(SELECT 
			Sum(Deliveries.Deliver_LBS)
		 FROM
			Deliveries
		 WHERE
			Deliveries.DeliveryDt >= @DtStartDate and Deliveries.DeliveryDt < @dtEndDate and Site.SiteID = Deliveries.SiteID)
		 AS
			DeliveryLBS,
		(SELECT 
			Reading.Reading * Site.LbsPerInch
		 FROM
			Reading
		 WHERE
			reading.readingdt >= @dtStartDate and reading.readingdt < (@dtStartDate +1) and Site.SiteID = Reading.SiteID)
		 AS BeginningInvlbs,
		(SELECT 
			Reading.Reading * Site.LbsPerInch
		 FROM
			Reading
		 WHERE
			reading.readingdt >= @dtEndDate and reading.readingdt < (@dtEndDate +1) and Site.SiteID = Reading.SiteID)
		 AS EndingInvLbs
	FROM         
		Site
	WHERE 
		Site.Invoicing = 'B'
END