Useful SQL

The underlying database management system is Microsoft SQL Server. End users are free to query the data structure and extract data using their own user-defined Transact-SQL queries.

Querying the Entire Solution


It is possible to create a file that contains the complete optimization solution, along with all the planning unit metadata that were defined in the forest description.

The resulting CSV file can be double clicked and opened using Microsoft Excel. It can then form the basis of temporary pivot tables or any other comprehensive data analysis that may be of interest.

It is possible to query the same stored procedure that is used to export the optimization solution, with the additional functionality that users can add their own SQL logic to further refine the results returned by the stored procedure.

SQL Server 2008R2 & SQL Server 2005 ONLY

The query syntax below uses the 'SET FMTONLY' statement. This has been deprecated in all SQL Server versions after 2008R2. The query as shown below will only work on SQL Server versions 2008R2 and below.

To do this, users can change the SQL code between the following two statements in the main block of Transact-SQL code presented below.

  1. -- BEGIN USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE -- and,

  2. -- END USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE --

Only Change User Defined SQL Code

The Transact-SQL code out of the two statements above needs to remain static and should not be changed if the query is to return its results correctly.

Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar). Then make changes to the ...SELECT * FROM ##tmp_consolidated... statement as needed to further refine and enhance the data returned by the query.

      --BEGIN--
      --TURN ON AD HOC QUERIES--
      EXEC sp_configure 'show advanced options', 1
      RECONFIGURE
      EXEC sp_configure 'ad hoc distributed queries', 1
      RECONFIGURE
      EXEC sp_configure 'show advanced options', 0
      RECONFIGURE
      --END--


      --BEGIN--
      IF OBJECT_ID('tempdb..##tmp_consolidated') IS NOT NULL
      BEGIN
            IF OBJECT_ID('tempdb..IX01_tmp_consolidated') IS NOT NULL
            BEGIN
                  ALTER TABLE ##tmp_consolidated DROP CONSTRAINT IX01_tmp_consolidated
            END
            DROP TABLE ##tmp_consolidated
      END
      --END--


      --BEGIN--
      DECLARE     @SQL VARCHAR(MAX)
      SELECT      @SQL = '' +
            'SELECT * ' +
            'INTO ##tmp_consolidated ' +
            'FROM OPENROWSET(''SQLNCLI'', ''SERVER=' +
            CONVERT(VARCHAR(100),SERVERPROPERTY('MachineName')) +
            '\' + 
             CONVERT(VARCHAR(100),SERVERPROPERTY ('InstanceName')) +
            ';TRUSTED_CONNECTION=YES;'', ''SET FMTONLY OFF ' + 
            'EXEC "' + DB_NAME() + '".[dbo].[sp_RSConsolidated]'')'

      EXEC(@SQL)
      --END--


      --BEGIN--
      --RECREATE PKEY FROM tbl_consolidated JUST BECAUSE--
      ALTER TABLE ##tmp_consolidated ADD  CONSTRAINT [PK00_tmp_consolidated] PRIMARY KEY CLUSTERED 
      ([Period] ASC,
      [PlunitID] ASC,
      [OriginID] ASC,
      [SpeciesID] ASC,
      [Type] ASC,
      [Status] ASC,
      [Age] ASC,
      [ProductID] ASC,
      [DestinationID] ASC,
      [Proportion] ASC) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
      --END--


      -----------------------------------------------------------------------------------------
      --BEGIN USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE--
      -----------------------------------------------------------------------------------------

      -- NOW DO WHATEVER YOU WANT WITH THE DYNAMIC TEMP TABLE ##tmp_consolidated
      -- IT IS A MIRROR OF THE DATA RETURNED BY STORED PROC [sp_RSConsolidated]

      SELECT * FROM ##tmp_consolidated
      INNER JOIN mps_cf_volume
                ON ##tmp_consolidated.[PlunitID] = mps_cf_volume.[PlunitID]
        AND ##tmp_consolidated.[Age] = mps_cf_volume.[Age]
      WHERE ##tmp_consolidated.[Status] = 'Yield'



      -----------------------------------------------------------------------------------------
      --END USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE--
      -----------------------------------------------------------------------------------------


      --BEGIN--
      IF OBJECT_ID('tempdb..##tmp_consolidated') IS NOT NULL
      BEGIN
            IF OBJECT_ID('tempdb..IX01_tmp_consolidated') IS NOT NULL
            BEGIN
                  ALTER TABLE ##tmp_consolidated DROP CONSTRAINT IX01_tmp_consolidated
            END
            DROP TABLE ##tmp_consolidated
      END
      --END--


      --BEGIN--
      --TURN OFF AD HOC QUERIES FOR SAFETY--
      EXEC sp_configure 'show advanced options', 1
      RECONFIGURE
      EXEC sp_configure 'ad hoc distributed queries', 0
      RECONFIGURE
      EXEC sp_configure 'show advanced options', 0
      RECONFIGURE
      --END--

Create 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 'Create a Generic Revenue Model' and 'Create a Generic Transport Model' 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]

Create 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 'Create a Generic Allocation Model' 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)

Create 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 'Create a Generic Allocation Model' code block 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