------------------------------------------------------------------ -- -- QESTMix Mixes View -- -- -- Author: Lief Martin -- Version: 2.1 -- Date Modified: 16/08/2007 -- Product: QESTLab -- -- -- Description: -- This script creates a view in QESTLab similar to the QESTMix audit report, -- which can be used in management reports. -- -- IMPORTANT: You need to replace the text "[QESTMix].[dbo]" with -- the correct database name. the name "QESTMix" is usually -- correct, however, if it is different, you need to edit this -- script before running it. -- -- Change Log: -- 1.1 Added handling of 0 for specific gravity in Yield calc (convert to null) -- 1.2 Added QestID and QestOwnerLabNo so that the view actually works in QESTLab -- 1.3 Significant optimisations to query execution. Added additional fields as -- per Boral requirements -- 1.4 Changed view name and added Plants.Regions to the view -- 1.5 Added Plants.Regions to GROUP BY clause -- 1.6 Added code to drop the view if it already exists -- 1.7 Added ShortProductCode -- 1.8 Convert 'null' client and project codes to empty string -- -- 2.0 Added GroupVersion, ProductVersion and DesignProductRelationUniqueID. -- These fields are required for the QESTMix_Mixes_Materials view to work. -- 2.1 Added Deleted flag -- ------------------------------------------------------------------ -- If the view already exists, remove it first IF OBJECT_ID ('QESTMix_Mixes', 'view') IS NOT NULL DROP VIEW QESTMix_Mixes; GO --The rest of the script creates the actual view. CREATE VIEW QESTMix_Mixes AS SELECT 0 AS QestOwnerLabNo , 99999 As QestID ,ISNULL(ProductMixes.ClientCode, '') AS ClientCode ,ISNULL(ProductMixes.ProjectCode, '') AS ProjectCode ,ProductMixes.Plant AS PlantCode ,ProductMixes.GroupCode AS ProductGroupCode ,ProductMixes.GroupVersion AS ProductGroupVersion ,ProductMixes.ProductCode AS ProductCode ,ProductMixes.Version AS ProductVersion ,ProductMixes.DateModified AS DateModified ,ProductMixes.DesignProductRelationUniqueID AS DesignProductRelationUniqueID ,ProductMixes.Active AS Active ,ISNULL(ProductMixes.Deleted, 0) AS Deleted ,Alternative = CASE WHEN PATINDEX('%.[0-9][0-9]', ProductMixes.ProductCode) > 0 THEN CAST(right(ProductMixes.ProductCode,2) AS int) ELSE 0 END --product code without the 'alternative' number. ,ShortProductCode = CASE WHEN PATINDEX('%.[0-9][0-9]', ProductMixes.ProductCode) > 0 THEN SUBSTRING(ProductMixes.ProductCode, 0, PATINDEX('%.[0-9][0-9]', ProductMixes.ProductCode)) ELSE ProductMixes.ProductCode END ,ProductMixes.Exported AS Exported ,ProductMixes.ExportedDateTime AS ExportedDateTime ,ProductMixes.ExportedBy AS ExportedBy ,ProductMixes.INTERFACE_Mix_Code AS INTERFACE_Mix_Code ,ProductMixes.INTERFACE_Product_Code AS INTERFACE_Product_Code ,AVG(ProductMixes.TotalWater) AS TotalWater ,ProductMixes.Grade AS Grade ,AVG(ProductMixes.Strength) AS Strength ,AVG(ProductMixes.InitialSlump) AS DesignSlump ,AVG(ProductMixes.Slump) AS DesignSlumpAfterSuper ,AVG(ProductMixes.AirContent) * 100 AS DesignAirPercent ,ProductMixes.Code AS DesignCode ,ProductMixes.ModifiedBy AS ModifiedBy ,ProductMixes.ModifiedReason AS ModifiedReason ,Plants.Regions As Regions --Yield calculation --Yield = (SUM(SSD * SG) + Water) * (1+Air) ,Yield = ROUND((( SUM( CASE WHEN Materials.Type IN (1,2,3,4,5,6) AND Materials.SpecificGravity > 0 THEN MaterialWeights.SSDWeight / NULLIF(Materials.SpecificGravity, 0) ELSE 0 END ) + (AVG(ProductMixes.TotalWater) / 1000)) * (1 + AVG(ProductMixes.AirContent))), 4) ,TotalWeight = SUM( CASE WHEN Materials.Type IN (1,2,3,4,5,6) AND Materials.SpecificGravity > 0 THEN MaterialWeights.SSDWeight ELSE 0 END ) + AVG(ProductMixes.TotalWater) ,TotalCement = SUM( CASE WHEN Materials.Type = 1 THEN MaterialWeights.SSDWeight ELSE 0 END ) ,TotalCementitious = SUM( CASE WHEN Materials.Type IN (1,2,3) THEN MaterialWeights.SSDWeight ELSE 0 END ) ,TotalAggregatesAndSands = SUM( CASE WHEN Materials.Type IN (4,5) THEN MaterialWeights.SSDWeight ELSE 0 END ) ,ROUND(SUM(MaterialWeights.BatchWeight * Materials.NettCost) / 1000, 2) AS ProductionNettCost ,ROUND(SUM(MaterialWeights.BatchWeight * (PlantMaterialCosts.DeliveredCost - PlantMaterialCosts.Cost)) / 1000, 2)AS ProductionGrossCost ,ROUND(SUM(MaterialWeights.BatchWeight * PlantMaterialCosts.CartageNettCost) / 1000, 2) AS CartageNettCost ,ROUND(SUM(MaterialWeights.BatchWeight * PlantMaterialCosts.Cost) / 1000, 2) AS CartageGrossCost ,ROUND(SUM(MaterialWeights.BatchWeight * (Materials.NettCost + ISNULL(PlantMaterialCosts.CartageNettCost, PlantMaterialCosts.Cost))) / 1000, 2) AS DeliveredNettCost ,ROUND(SUM(MaterialWeights.BatchWeight * PlantMaterialCosts.DeliveredCost) / 1000, 2) AS DeliveredGrossCost FROM [QESTMix].[dbo].ProductMixes AS ProductMixes LEFT JOIN [QESTMix].[dbo].Plants AS Plants ON ProductMixes.Plant = Plants.Code LEFT JOIN [QESTMix].[dbo].ProductMaterialWeights AS MaterialWeights ON ProductMixes.GroupCode = MaterialWeights.GroupCode AND ProductMixes.GroupVersion = MaterialWeights.GroupVersion AND ProductMixes.ProductCode = MaterialWeights.ProductCode AND ProductMixes.Version = MaterialWeights.MixVersion AND ProductMixes.DesignProductRelationUniqueID = MaterialWeights.DesignProductRelationUniqueID LEFT OUTER JOIN [QESTMix].[dbo].Materials AS Materials ON Materials.Code = MaterialWeights.MaterialCode AND (Materials.Deleted IS NULL OR Materials.Deleted = 0) LEFT OUTER JOIN [QESTMix].[dbo].PlantMaterialCosts AS PlantMaterialCosts ON MaterialWeights.MaterialCode = PlantMaterialCosts.MaterialCode AND ProductMixes.Plant = PlantMaterialCosts.PlantCode GROUP BY ProductMixes.ClientCode ,ProductMixes.ProjectCode ,ProductMixes.Plant ,ProductMixes.GroupCode ,ProductMixes.GroupVersion ,ProductMixes.ProductCode ,ProductMixes.Version ,ProductMixes.DateModified ,ProductMixes.DesignProductRelationUniqueID ,ProductMixes.Code ,ProductMixes.ModifiedBy ,ProductMixes.ModifiedReason ,ProductMixes.Version ,ProductMixes.Active ,ProductMixes.Deleted ,ProductMixes.Exported ,ProductMixes.ExportedDateTime ,ProductMixes.ExportedBy ,ProductMixes.Grade ,ProductMixes.INTERFACE_Mix_Code ,ProductMixes.INTERFACE_Product_Code ,Plants.Regions