Generic Allocation Model from a Forest Description Database¶
Worksheet Allocation¶
It is possible to list every combination of species log grade that exists in the model. This simple list can then be used to quickly create a generic allocation model that can be used for initial model building and testing.
The SQL code below will produce data that can be copied and pasted to the worksheet 'Allocation'. This can then be used to quickly create an initial model for testing and debugging purposes. The 'Worksheet Price' and 'Worksheet Transport' code blocks below this current section can then be used to produce a compatible revenue model and a compatible transport model for this generic allocation data.
Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).
DECLARE @PeriodMax AS INT
DECLARE @AgeclassMax AS INT
SET @PeriodMax = (SELECT MAX([Period]) FROM tbl_period)
SET @AgeclassMax = (SELECT MAX([Age]) FROM tbl_age)
SELECT
'1' AS 'Start',
@PeriodMax AS 'End',
'Period' AS 'Type',
'All Plunits' AS 'Group',
'All' AS 'Attribute',
'0' AS 'Min',
@AgeclassMax AS 'Max',
'>' AS 'Equality',
'0' AS 'Quantity',
'm3' AS 'Unit',
'Generic' AS 'Destination',
tbl_species.[Species],
tbl_product.[Product],
'>' AS 'Sign',
'0' AS 'Mix'
FROM
tbl_plunit
INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]
INNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID]
INNER JOIN tbl_yield ON tbl_croptype.[CroptypeID] = tbl_yield.[CroptypeID]
INNER JOIN tbl_product ON tbl_yield.[ProductID] = tbl_product.[ProductID]
WHERE tbl_product.[Status] = 'YIELD'
GROUP BY
tbl_species.[Species],
tbl_product.[Product]
UNION
SELECT
'1' AS 'Start',
@PeriodMax AS 'End',
'Period' AS 'Type',
'All Plunits' AS 'Group',
'All' AS 'Attribute',
'0' AS 'Min',
@AgeclassMax AS 'Max',
'>' AS 'Equality',
'0' AS 'Quantity',
'm3' AS 'Unit',
'Generic' AS 'Destination',
tbl_species.[Species],
tbl_product.[Product],
'>' AS 'Sign',
'0' AS 'Mix'
FROM
tbl_plunit
INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]
INNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID]
INNER JOIN tbl_silviculture ON tbl_regime.[RegimeID] = tbl_silviculture.[RegimeID]
INNER JOIN tbl_product ON tbl_silviculture.[ProductID] = tbl_product.[ProductID]
WHERE tbl_product.[Status] = 'YIELD'
GROUP BY
tbl_species.[Species],
tbl_product.[Product]
ORDER BY
[Species], [Product]
Worksheet Price¶
The SQL code below will produce data that can be copied and pasted to the worksheet 'Price'. This can be used to quickly create an initial revenue model for testing and debugging purposes and is compatible with the generic allocation data produced using the 'Worksheet Allocation' code block above this current section.
If a species log grade combination does not exist
If a species log grade combination does not exist then a NULL value is generated in the data. Users should replace these NULL values with a blank cell after the data are pasted to the 'Price' worksheet in the forest description
Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).
DECLARE @Columns AS NVARCHAR(MAX)
SET @Columns = STUFF((SELECT ',' + QUOTENAME(tbl_product.[Product])
FROM tbl_product WHERE tbl_product.[Status] = 'YIELD' GROUP BY tbl_product.[Product] ORDER BY tbl_product.[Product]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = '
SELECT
*
FROM
(
SELECT
' + CHAR(39) + 'Generic' + CHAR(39) + 'AS' + CHAR(39) + 'Destination' + CHAR(39) + ',
' + CHAR(39) + 'All' + CHAR(39) + 'AS' + CHAR(39) + 'Origin' + CHAR(39) + ',
tbl_species.[Species],
' + CHAR(39) + 'None' + CHAR(39) + 'AS' + CHAR(39) + 'Index' + CHAR(39) + ',
tbl_product.[ProductID],
tbl_product.[Product]
FROM
tbl_plunit
INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]
INNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID]
INNER JOIN tbl_yield ON tbl_croptype.[CroptypeID] = tbl_yield.[CroptypeID]
INNER JOIN tbl_product ON tbl_yield.[ProductID] = tbl_product.[ProductID]
GROUP BY
tbl_species.[Species],
tbl_product.[ProductID],
tbl_product.[Product]
UNION
SELECT
' + CHAR(39) + 'Generic' + CHAR(39) + 'AS' + CHAR(39) + 'Destination' + CHAR(39) + ',
' + CHAR(39) + 'All' + CHAR(39) + 'AS' + CHAR(39) + 'Origin' + CHAR(39) + ',
tbl_species.[Species],
' + CHAR(39) + 'None' + CHAR(39) + 'AS' + CHAR(39) + 'Index' + CHAR(39) + ',
tbl_product.[ProductID],
tbl_product.[Product]
FROM
tbl_plunit
INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]
INNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID]
INNER JOIN tbl_silviculture ON tbl_regime.[RegimeID] = tbl_silviculture.[RegimeID]
INNER JOIN tbl_product ON tbl_silviculture.[ProductID] = tbl_product.[ProductID]
GROUP BY
tbl_species.[Species],
tbl_product.[ProductID],
tbl_product.[Product]
) AS qry_plunit
PIVOT
(
MAX(qry_plunit.[ProductID])
FOR [Product] IN (' + @Columns + ')
) AS pvt_plunit '
EXECUTE (@SQL)
Worksheet Transport¶
The SQL code below will produce data that can be copied and pasted to the worksheet 'Transport'. This can be used to quickly create an initial transport model for testing and debugging purposes and is compatible with the generic allocation data produced using the 'Worksheet Allocation' and 'Worksheet Price' code blocks above this current section.
Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).
SELECT tbl_origin.[Origin], '0' AS 'Generic' FROM tbl_origin WHERE tbl_origin.[OriginID] > 0