Querying the Entire Solution using SQL

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 data 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.

To do this 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 WHERE statements as needed to further refine and filter the data returned by the query.

SELECT * 
FROM tbl_consolidated 
-- PlunitID2 --
INNER JOIN ( 
       SELECT 
              tbl_plunit.[PlunitID] AS 'PlunitID2', 
              tbl_tending.[Tending], 
              tbl_regime.[Regime], 
              tbl_croptype.[Croptype], 
              tbl_cost.[Cost], 
              tbl_carbon.[Carbon]
       FROM 
              tbl_plunit 
              INNER JOIN tbl_tending ON tbl_plunit.[TendingID] = tbl_tending.[TendingID]
              INNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID]
              INNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID]
              INNER JOIN tbl_cost ON tbl_plunit.[CostID] = tbl_cost.[CostID]
              INNER JOIN tbl_carbon ON tbl_plunit.[CarbonID] = tbl_carbon.[CarbonID]) qry_plunit_1 ON qry_plunit_1.[PlunitID2] = tbl_consolidated.[PlunitID]
-- PlunitID3 --
INNER JOIN (
       SELECT  
              tbl_cluster.[PlunitID] AS 'PlunitID3', 
              tbl_group.[Group], 
              tbl_attribute.[Attribute] 
       FROM 
              tbl_cluster 
              INNER JOIN tbl_group ON tbl_cluster.[GroupID] = tbl_group.[GroupID] 
              INNER JOIN tbl_attribute ON tbl_cluster.[GroupID] = tbl_attribute.[GroupID]
                     AND tbl_cluster.[AttributeID] = tbl_attribute.[AttributeID]
                     AND tbl_group.[GroupID] = tbl_attribute.[GroupID]
       -- USE THIS FILTER BELOW TO GIVE A GROUP NAME AND A SPECIFIC ATTRIBUTE TO RESTRICT THE RETURN DATA TO THAT COMBINATION
       WHERE
              tbl_group.[Group] = 'All Plunits' AND tbl_attribute.[Attribute] = 'All') qry_plunit_2 ON qry_plunit_2.[PlunitID3] = tbl_consolidated.[PlunitID]
-- USE THE WHERE CLAUSE BELOW TO RESTRICT THE RETURN DATA TO A PARTICULAR STATUS AS NEEDED - CAN USE BOOLEAN LOGIC AS WELL
WHERE
       tbl_consolidated.[Status] LIKE '%'
       --tbl_consolidated.[Status] LIKE 'Yield'

Comments