Confirm

Back to discussions
Expand all | Collapse all

SQL query - can anyone help with a tree data report?

  • 1.  SQL query - can anyone help with a tree data report?

    Posted 08-19-2020 14:13
    Hi everyone!

    This is a massive longshot but has anyone ever created an SQL report in Confirm to generate the following data?

    'The number of tree work tasks (I think they mean jobs rather than individual SOR items) undertaken in the 19/20 financial year split up by the tree height category (a feature attribute).'

    If anyone knows the SQL for this which I could copy and paste I'd be extremely grateful.

    Many thanks!

    Chris
    Tree Team
    Royal Borough of Windsor and Maidenhead

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------


  • 2.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-01-2020 12:27
    *cheeky bump* in case anyone can point me in the right direction :-)

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 3.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-01-2020 20:22
    As this is more an SQL query rather than a confirm query I would suggest you try https://stackoverflow.com/ and tag with the version of SQL that confirm uses. You would need to supply the table schema. I've found this to be a better route when asking query related questions for Mapinfo and other software -ex https://stackoverflow.com/questions/63446143/sql-code-to-detect-change-between-two-tables-without-knowing-the-attributes/63447229?noredirect=1#comment112194815_63447229

    ------------------------------
    George Corea
    Mangoesmapping
    ------------------------------

    Original Message


  • 4.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-02-2020 03:15
    Hi Chris

    Is does depend a bit on the tool you want to run the query in.
    Let's assume
    • you want to run this query in MapInfo Pro,
    • and that your table is called TREE_JOBS,
    • and that your financial year column is a date column called JOB_DONE_DATE,
    • and that the tree height category is a string column called TREE_HEIGHT,
    • and that the fiscal year is between July 1, 2019, and June 30, 2020.
    If we use the assumption above, your MapInfo Pro query could look like this:

    Select TREE_HEIGHT, Count(*) "NumberOfJobs"
    From TREE_JOBS
    Where JOB_DONE_DATE >= NumberToDate(20190701) 
    And JOB_DONE_DATE <= NumberToDate(20200630) 
    Group By TREE_HEIGHT

    You can write a similar query in for instance SQL Server but you will have to change the MapBasic function NumberToDate to SQL Server's equivalent function.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Original Message


  • 5.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 07:15
    Thanks so much to both of you - that's really helpful! I'm using the Confirm Data Source / Data Miner tool for my SQL purposes so I'll try using Peter's example as a template.

    Many thanks,

    Chris

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 6.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 07:28
    Hi Peter,

    The height data comes from the table address 'attribute_type' and the 'attrib_type_code' within that is 'T001' for the height category. Does this make things much more complicated? I'm assuming so since it's not from the 'job' table (excuse my SQL ignorance).

    Thanks,

    Chris​

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 7.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 07:45
    Hi Chris

    I'm not sure I fully understand the structure of the tables.
    As long as you can "join" the two tables on a common attribute, you'll be fine.

    Maybe you can share the name of the tables and columns that you need to work with?
    Here's an example where I just have tried guessing the name of the tables and columns

    Table 1:
    TREE_JOBS
    Columns:
    JOB_DONE_DATE
    ATRRIBUTE_TYPE

    Table 2:
    Address
    Columns:
    Attribute_type
    Attribute_code

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Original Message


  • 8.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 09:52
    Hi Peter,

    Thanks again for your help, it's much appreciated!

    The tree jobs table is called 'job' and the table containing the attribute type code (attrib_type_code) that also shares common fields with 'job' is called 'feat_attrib_type'. The two tables have a common attribute which is 'site_code' or alternatively 'plot_number'.

    The date field I'd like to use is 'actual_comp_date' from the 'job' table.

    In case it's relevant, both tables also link to a table called 'job_risk_assess' which has 'job_number' and 'attrib_type_code' fields. The 'attrib_type_code' for Height is 'T001'. The different height categories are indicated by the 'attrib_value_code' which is found in the 'feat_attrib_type' table.

    Hope that makes sense!

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 9.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 10:05
    Edited by Steve Bish 09-09-2020 10:09
    Hi Chris,


    Try this:

    SELECT
       NVL(attribute_value.attrib_value_name, 'No Tree Height Attribute set against Feature') tree_height,
       COUNT(job.job_number) no_of_jobs
    FROM
       job
       JOIN feature ON
          job.site_code = feature.site_code AND
          job.plot_number = feature.plot_number
       left JOIN (
          feat_attrib_type
          JOIN attribute_value ON
             feat_attrib_type.attrib_type_code = attribute_value.attrib_type_code AND
             feat_attrib_type.attrib_value_code = attribute_value.attrib_value_code
          JOIN attribute_type ON
             attribute_type.attrib_type_code = attribute_value.attrib_type_code
          )
          ON
             feature.site_code = feat_attrib_type.site_code AND
             feature.plot_number = feat_attrib_type.plot_number AND
             feat_attrib_type.attrib_type_code = 'T001'
       JOIN feature_type ON
          feature.feature_type_code = feature_type.feature_type_code
    WHERE
       feature_type.feature_group_code = 'T001' AND
       job.actual_comp_date BETWEEN TO_DATE('01-APR-2019', 'DD-MON-YYYY') AND TO_DATE('31-MAR-2020', 'DD-MON-YYYY')
    GROUP BY
       attribute_value.attrib_value_name;

    It assumes that,

    • the Actual Completion date falls between the 1st Apr 2019 and 31st March 2020
    • Features that do not have the 'T001 - Tree Height' Attribute Type associated with them are also to be considered
    • Features that belong to the Feature Group 'T001 - Arboriculture' are deemed to all be Trees

    Hope this helps.

    Kind regards,
    Steve

    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Confirm
    ------------------------------

    Original Message


  • 10.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 10:16
    Hi Steve! You're an absolute legend! That looks perfect.

    Massive thanks to you, Peter and George :-)

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 11.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 10:30
    Out of curiosity, would it be much more difficult / complicated to show these figures for every tree-related SOR item? i.e. sor_item.sor_item_name

    I'm hoping that I won't be asked for this but in case they do want the breakdown I thought I'd enquire. I may have to put in a chargeable IT request internally to ask our IT Team to write a code for this if so since I'm assuming this would be a lot more complex.

    Thanks,

    Chris

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 12.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-09-2020 11:26
    Regarding the above, I think I have a solution which involves looking up each SOR item one by one but I think it's doable :-) hopefully I won't need any further assistance

    Thanks,

    Chris

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message


  • 13.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-10-2020 07:52
    Thanks for chiming in here, Steve.

    I would never have figured out this join with the details I had at hand.
    It certainly helps to know the database structure you are querying.

    I had imagined it was just a single maybe two tables :-)

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Original Message


  • 14.  RE: SQL query - can anyone help with a tree data report?

    Posted 09-10-2020 09:24
    Hi Peter,

    I'm sorry for not providing more information originally. I've now also realised how important it is to know the database structure.

    Thanks again,

    Chris

    ------------------------------
    Chris Mackenzie-Smith
    THE ROYAL BOROUGH OF WINDSOR AND MAIDENHEAD COUNCIL
    MAIDENHEAD
    ------------------------------

    Original Message