--------------------------------------------------------------------------------
--
-- Mixes_Materials_view_sql2000.sql
--
--------------------------------------------------------------------------------
--
-- Author: Lief Martin
-- Version: 1.0
-- Date Modified: 14/08/2008
-- Products: QESTLab
--
-- Description:
--   This script creates a view in QESTLab similar to the QESTMix product master
--   report, which can be used with QESTLab's management reporting engine.
--
--   This version is compatible with SQL Server 2000.  Customers using SQL 
--   Server 2005 should use 'Mixes_Materials_View.sql' instead.
--
-- Requirements:
--   This view is dependent on the view 'QESTMix_Mixes', which is created
--   using the script 'QESTMix_Mixes_View.sql'.  Version 2.0 is the minimum
--   version of the script that includes the required fields.
--
-- Notes:
--   Although it returns the same results, this view is a *lot* slower (by a 
--   factor of around 30) than the SQL Server 2005 one (which uses the crazy new 
--   'cross apply' function).  It is strongly recommended to upgrade to SQL 
--   Server 2005 if you want to make any significant use of this database view.
--
--   See also: Mixes_Materials_view.sql
--
--------------------------------------------------------------------------------

--View that gives us the 'position' (i.e. column) for the material
if object_id('ProductMaterialWeightsView', 'V') is not null
  drop view ProductMaterialWeightsView
go

create view ProductMaterialWeightsView
as 
select w.*
, m.type as materialtype
, MaterialTypeDescription = 
    CASE m.Type
      WHEN 1 THEN 'Cement'
      WHEN 2 THEN 'Flyash'
      WHEN 3 THEN 'Cement'
      WHEN 4 THEN 'Aggregate'
      WHEN 5 THEN 'Sand'
      WHEN 6 THEN 'Additive (kg)'
      WHEN 7 THEN 'Additive (unit)'
      WHEN 8 THEN 'Admixture (ml/m³)'
      WHEN 9 THEN 'Admixture (ml/cem)'
      WHEN 10 THEN 'Admixture (g/m³)'
      WHEN 20 THEN 'Water'
      WHEN 101 THEN 'Virtual Cement'
      WHEN 102 THEN 'Virtual Flyash'
      WHEN 103 THEN 'Virtual Cement'
      WHEN 104 THEN 'Virtual Aggregate'
      WHEN 105 THEN 'Virtual Sand'
      WHEN 106 THEN 'Virtual Additive (kg)'
      WHEN 107 THEN 'Virtual Additive (unit)'
      WHEN 108 THEN 'Virtual Admixture (ml/m³)'
      WHEN 109 THEN 'Virtual Admixture (ml/cem)'
      WHEN 110 THEN 'Virtual Admixture (g/m³)'
      WHEN 120 THEN 'Virtual Water'
    END

from 
(
select w1.groupcode, w1.groupversion, w1.productcode, w1.mixversion, w1.designproductrelationuniqueid, w1.batchorder, w1.materialcode, count(*) as position
,  w1.ssdweight, w1.batchweight
from
(select w.groupcode, w.groupversion, w.productcode, w.mixversion, w.designproductrelationuniqueid, gm.batchorder, w.materialcode,  w.ssdweight, w.batchweight, w.plantcode
from [qestmix_aubor].[dbo].productmaterialweights w
inner join [qestmix_aubor].[dbo].productgroupmaterials gm on w.groupcode = gm.groupcode and w.groupversion = gm.groupversion and w.materialcode = gm.materialcode
) w1
inner join
(select w.groupcode, w.groupversion, w.productcode, w.mixversion, w.designproductrelationuniqueid, gm.batchorder
from [qestmix_aubor].[dbo].productmaterialweights w
inner join [qestmix_aubor].[dbo].productgroupmaterials gm on w.groupcode = gm.groupcode and w.groupversion = gm.groupversion and w.materialcode = gm.materialcode
) w2
on 
w1.groupcode = w2.groupcode 
and w1.groupversion = w2.groupversion
and w1.productcode = w2.productcode
and w1.mixversion = w2.mixversion
and w1.designproductrelationuniqueid = w2.designproductrelationuniqueid
and w1.batchorder >= w2.batchorder
group by w1.groupcode, w1.groupversion, w1.productcode, w1.mixversion, w1.designproductrelationuniqueid, w1.batchorder, w1.materialcode,  w1.ssdweight, w1.batchweight
) w
inner join [qestmix_aubor].[dbo].materials m on w.materialcode = m.code

go


--------------------------------------------------------------------------------

-- Mixes materials view

if object_id('QESTMix_Mixes_Materials_2', 'V') is not null
  drop view QESTMix_Mixes_Materials_2
go

create view QESTMix_Mixes_Materials_2 as
select p.*
  w1.materialcode as MaterialCode1 ,  w1.batchorder as BatchOrder1 ,  w1.materialtype as MaterialType1 ,  w1.materialtypedescription as MaterialTypeDescription1 ,  w1.ssdweight as MaterialSSDWeight1 ,  w1.BatchWeight as MaterialBatchWeight1 , 
  w2.materialcode as MaterialCode2 ,  w2.batchorder as BatchOrder2 ,  w2.materialtype as MaterialType2 ,  w2.materialtypedescription as MaterialTypeDescription2 ,  w2.ssdweight as MaterialSSDWeight2 ,  w2.BatchWeight as MaterialBatchWeight2 , 
  w3.materialcode as MaterialCode3 ,  w3.batchorder as BatchOrder3 ,  w3.materialtype as MaterialType3 ,  w3.materialtypedescription as MaterialTypeDescription3 ,  w3.ssdweight as MaterialSSDWeight3 ,  w3.BatchWeight as MaterialBatchWeight3 , 
  w4.materialcode as MaterialCode4 ,  w4.batchorder as BatchOrder4 ,  w4.materialtype as MaterialType4 ,  w4.materialtypedescription as MaterialTypeDescription4 ,  w4.ssdweight as MaterialSSDWeight4 ,  w4.BatchWeight as MaterialBatchWeight4 ,
  w5.materialcode as MaterialCode5 ,  w5.batchorder as BatchOrder5 ,  w5.materialtype as MaterialType5 ,  w5.materialtypedescription as MaterialTypeDescription5 ,  w5.ssdweight as MaterialSSDWeight5 ,  w5.BatchWeight as MaterialBatchWeight5 , 
  w6.materialcode as MaterialCode6 ,  w6.batchorder as BatchOrder6 ,  w6.materialtype as MaterialType6 ,  w6.materialtypedescription as MaterialTypeDescription6 ,  w6.ssdweight as MaterialSSDWeight6 ,  w6.BatchWeight as MaterialBatchWeight6 , 
  w7.materialcode as MaterialCode7 ,  w7.batchorder as BatchOrder7 ,  w7.materialtype as MaterialType7 ,  w7.materialtypedescription as MaterialTypeDescription7 ,  w7.ssdweight as MaterialSSDWeight7 ,  w7.BatchWeight as MaterialBatchWeight7 , 
  w8.materialcode as MaterialCode8 ,  w8.batchorder as BatchOrder8 ,  w8.materialtype as MaterialType8 ,  w8.materialtypedescription as MaterialTypeDescription8 ,  w8.ssdweight as MaterialSSDWeight8 ,  w8.BatchWeight as MaterialBatchWeight8 ,
  w9.materialcode as MaterialCode9 ,  w9.batchorder as BatchOrder9 ,  w9.materialtype as MaterialType9 ,  w9.materialtypedescription as MaterialTypeDescription9 ,  w9.ssdweight as MaterialSSDWeight9 ,  w9.BatchWeight as MaterialBatchWeight9 , 
 w10.materialcode as MaterialCode10, w10.batchorder as BatchOrder10, w10.materialtype as MaterialType10, w10.materialtypedescription as MaterialTypeDescription10, w10.ssdweight as MaterialSSDWeight10, w10.BatchWeight as MaterialBatchWeight10 

from 

[QESTMix_Mixes] p

left join ProductMaterialWeightsView w1
   on w1.position = 1
  and p.ProductGroupCode = w1.groupcode 
  and p.ProductGroupVersion = w1.groupversion
  and p.productcode = w1.productcode
  and p.ProductVersion = w1.mixversion
  and p.designproductrelationuniqueid = w1.designproductrelationuniqueid

left join ProductMaterialWeightsView w2 
   on w2.position = 2
  and p.ProductGroupCode = w2.groupcode 
  and p.ProductGroupVersion = w2.groupversion
  and p.productcode = w2.productcode
  and p.ProductVersion = w2.mixversion
  and p.designproductrelationuniqueid = w2.designproductrelationuniqueid

left join ProductMaterialWeightsView w3
   on w3.position = 3
  and p.ProductGroupCode = w3.groupcode 
  and p.ProductGroupVersion = w3.groupversion
  and p.productcode = w3.productcode
  and p.ProductVersion = w3.mixversion
  and p.designproductrelationuniqueid = w3.designproductrelationuniqueid

left join ProductMaterialWeightsView w4
   on w4.position = 4
  and p.ProductGroupCode = w4.groupcode 
  and p.ProductGroupVersion = w4.groupversion
  and p.productcode = w4.productcode
  and p.ProductVersion = w4.mixversion
  and p.designproductrelationuniqueid = w4.designproductrelationuniqueid

left join ProductMaterialWeightsView w5
   on w5.position                       =  5
  and p.ProductGroupCode                       = w5.groupcode 
  and p.ProductGroupVersion                    = w5.groupversion
  and p.productcode                     = w5.productcode
  and p.ProductVersion                         = w5.mixversion
  and p.designproductrelationuniqueid   = w5.designproductrelationuniqueid

left join ProductMaterialWeightsView w6
   on w6.position                       =  6
  and p.ProductGroupCode                       = w6.groupcode 
  and p.ProductGroupVersion                    = w6.groupversion
  and p.productcode                     = w6.productcode
  and p.ProductVersion                         = w6.mixversion
  and p.designproductrelationuniqueid   = w6.designproductrelationuniqueid

left join ProductMaterialWeightsView w7
   on w7.position                       =  7
  and p.ProductGroupCode                       = w7.groupcode 
  and p.ProductGroupVersion                    = w7.groupversion
  and p.productcode                     = w7.productcode
  and p.ProductVersion                         = w7.mixversion
  and p.designproductrelationuniqueid   = w7.designproductrelationuniqueid

left join ProductMaterialWeightsView w8
   on w8.position                       =  8
  and p.ProductGroupCode                       = w8.groupcode 
  and p.ProductGroupVersion                    = w8.groupversion
  and p.productcode                     = w8.productcode
  and p.ProductVersion                         = w8.mixversion
  and p.designproductrelationuniqueid   = w8.designproductrelationuniqueid

left  join ProductMaterialWeightsView w9
   on w9.position                       =  9
  and p.ProductGroupCode                       = w9.groupcode 
  and p.ProductGroupVersion                    = w9.groupversion
  and p.productcode                     = w9.productcode
  and p.ProductVersion                         = w9.mixversion
  and p.designproductrelationuniqueid   = w9.designproductrelationuniqueid

left join ProductMaterialWeightsView w10
   on w10.position                      =  10
  and p.ProductGroupCode                       = w10.groupcode 
  and p.ProductGroupVersion                    = w10.groupversion
  and p.productcode                     = w10.productcode
  and p.ProductVersion                         = w10.mixversion
  and p.designproductrelationuniqueid   = w10.designproductrelationuniqueid