Confirm

Expand all | Collapse all

SQL to show how many photos were taken on a condition survey

  • 1.  SQL to show how many photos were taken on a condition survey

    Posted 11-26-2020 09:08
    Does anyone know any SQL to show how many photos were taken on a condition survey?

    I've got the below for bringing back the number of photos attached to jobs

    (SELECT COUNT(document_link.entity_key)
    FROM document_link
    WHERE document_link.entity_key =CAST( inspection_feature.insp_batch_no as VARCHAR (15)) AND
    document_link.entity_type = 'JOB' AND
    document_link.document_notes LIKE 'Photo added by_%' )
    as Photo_count

    and have changed that to

    (SELECT COUNT(document_link.entity_key)
    FROM document_link
    WHERE document_link.entity_key =CAST( inspection_feature.insp_batch_no as VARCHAR (15)) AND
    document_link.entity_type = 'INSP_FEAT' AND
    document_link.document_notes LIKE 'Photo added by_%' )
    as Photo_count

    however this keeps bringing back a value of 0 - even when I know there are photos on the condition survey.

    ------------------------------
    Rob Morris
    Telford & Wrekin Council
    Telford
    ------------------------------


  • 2.  RE: SQL to show how many photos were taken on a condition survey

    Posted 11-26-2020 10:01
    Edited by James Corletto 11-26-2020 10:02
    Hi Rob,

    The entity_key in the document_link table for a job is the job number but the entity_key for a feature inspection is a combination of the inspection batch number, site code and plot number for the feature seperated by a "/".

    Try something like this SQL which will return the number of photos for each individual inspection feature

    SELECT site_code, plot_number, insp_batch_no, COUNT(*) as photo_count
    FROM grounds.document_link, grounds.inspection_feature
    WHERE document_link.entity_key = CONVERT(VARCHAR,insp_batch_no) + '/' + site_code + '/' + CONVERT(VARCHAR,plot_number) AND
    document_link.entity_type = 'INSP_FEAT' AND
    document_link.document_notes LIKE 'Photo added by_%'
    GROUP BY site_code, plot_number, insp_batch_no

    The second where clause is probably not needed as the join should only return inspection features

    result:

    site_code plot_number insp_batch_no photo_count
    ADMIRALAML 105000 81960 1
    COMMER17SA 105012 22662 1
    MORTESSTBL 9002800 202867 1
    NELSON06PV 2405006 21778 2


    ------------------------------
    James Corletto
    City of Salisbury
    Salisbury SA
    ------------------------------



  • 3.  RE: SQL to show how many photos were taken on a condition survey

    Posted 11-26-2020 10:26
    Hi Rob

    For more information about the different database tables that hold different types of Document Links, look at the knowledge article Using SQL to show Document Links in Confirm.

    ------------------------------
    Chris Wareham
    Senior Technical Support Analyst
    Confirm
    ------------------------------



  • 4.  RE: SQL to show how many photos were taken on a condition survey

    Posted 11-26-2020 10:40
    Hi Rob,

    You may also wish to remove the following:

    document_link.document_notes LIKE 'Photo added by_%'

    To capture every document link attached to the Inspection Feature. For example, the Inspector may have chosen to override the Document Notes in ConfirmConnect, or you have permutations of 'Photo added by', 'Photo attached by' or 'Photo taken by'.

    Kind regards,
    Steve

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