-- FINISHED PRODUCTS PRODUCTION REPORTS DATA
-- gets current WIP Job information. 
-- requires item details from this. 
USE Staging
-- declare start and end variables
DECLARE @Start DATETIME 
DECLARE @End DATETIME
SET DATEFORMAT DMY
SET @Start = '01/01/2008'
SET @End = '01/02/2008'
SELECT
    SUM(jbs.Value) AS ProducionValue,
    SUM(prd.Cost) AS Cost,
    CONVERT(CHAR(10), @Start, 103) AS [From],
    CONVERT(CHAR(10), @End, 103) AS [End]
FROM
    (
      SELECT
        Job.[Job No],
        Job.[Cust Name],
        Job.[Req'd Date],
        Job.ProdStartActual,
        Job.Dept,
        [Job].[Value],
        Job.Loan
      FROM
        Job
      WHERE
        Job.[ProdStartActual] BETWEEN @start AND @End AND
        Job.Cancel = 0 AND
        Job.Sales = 1 AND
        Job.Invoiced = 1 OR
        Job.[ProdStartActual] BETWEEN @start AND @End AND
        Job.Cancel = 0 AND
        Job.Sales = 1 AND
        Job.Invoiced = 2
    ) AS jbs INNER JOIN 
    (
      SELECT
        Item.[Job No],
        Item.[Code]
      FROM
        Item
    ) AS itm ON jbs.[Job No] = itm.[Job No] INNER JOIN
    (
      SELECT
        Product.[Product Code],
        Product.[Cost]
      FROM
        Product
    ) AS prd ON itm.Code = prd.[Product Code]

USE Staging
GO

DECLARE @Start DATETIME,
	@End DATETIME

SET DATEFORMAT DMY

SELECT	@Start = '01/01/2008',
	@End = '01/02/2008'

SELECT		SUM(jbs.Value) AS ProductionValue,
		SUM(prd.Cost) AS Cost,
		CONVERT(CHAR(10), @Start, 103) AS [From],
		CONVERT(CHAR(10), @End, 103) AS [End],
		DATEADD(DAY, DATEDIFF(DAY, '19000101', jbs.ProdStartActual), '19000101') AS ProdStartActual
FROM		Job AS jbs
INNER JOIN	Item AS itm ON itm.[Job No] = jbs.[Job No]
INNER JOIN	Product AS prd ON prd.[Product Code] = itm.Code
WHERE		jbs.ProdStartActual >= @Start
		AND jbs.ProdStartActual < @End
		AND jbs.Cancel = 0
		AND jbs.Sales = 1
		AND jbs.Invoiced IN (1, 2)
GROUP BY	DATEADD(DAY, DATEDIFF(DAY, '19000101', jbs.ProdStartActual), '19000101')