Confirm

Back to discussions
Expand all | Collapse all

Street Works Site SQL

  • 1.  Street Works Site SQL

    Posted 10-01-2020 04:17
    Hello,

    We are trying to make a report to retrieve information regarding street work sites, however we have been unable to add the 'updated' date from the street works site tab to the below SQL

    SELECT
    sw_notice_site.work_header_no,
    organisation.organise_name,
    sw_header.works_ref,
    sw_notice_header.notice_issue_time,
    sw_site_status.guarantee_reset,
    sw_notice_header.works_location,
    sw_notice_header.permit_status,
    sw_site.interim_date,
    sw_site.guarantee_date
    FROM
    organisation,
    sw_header,
    sw_notice_header,
    sw_notice_site,
    sw_site,
    sw_site_status,
    sw_works_status
    WHERE
    organisation.organise_code = sw_header.organise_code AND
    sw_header.work_header_no = sw_notice_header.work_header_no AND
    sw_site_status.site_status_code = sw_works_status.site_status_code AND
    sw_works_status.works_status_code = sw_notice_header.works_status_code AND
    sw_notice_header.work_header_no = sw_notice_site.work_header_no AND
    sw_notice_header.work_version_no = sw_notice_site.work_version_no AND
    sw_notice_header.work_header_no = sw_site.work_header_no AND
    sw_site.site_number = sw_notice_site.site_number

    Can anybody advise?

    Thank you

    ------------------------------
    Richard White
    Hull City County Council
    HULL
    ------------------------------


  • 2.  RE: Street Works Site SQL

    Posted 10-01-2020 12:05
    Hi Richard,

    You'll want the sw_notice_site.site_issue_time having joined to this table using the work_header_no and work_version_no.

    Kind regards,
    Steve

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

    Original Message


  • 3.  RE: Street Works Site SQL

    Posted 10-15-2020 11:14

     Hello steve, i wonder if you could help me, i posted this about a month ago and no one responded, can you have a look at this please

    https://community.pitneybowes.com/communities/community-home/digestviewer/viewthread?GroupId=31&MessageKey=05e78089-866c-45ed-b555-e3672ec1ea23&CommunityKey=93b5e08a-f346-4e30-8c8e-79a8c9579b01&tab=digestviewer&ReturnUrl=%2fcommunities%2fcommunity-home%2fdigestviewer%3ftab%3ddigestviewer%26SuccessMsg%3dThank%2byou%2bfor%2bsubmitting%2byour%2bmessage.



    ------------------------------
    Afzal Hussain
    Specialist Application Support Officer
    London Borough of Barking and Dagenham
    ------------------------------

    Original Message


  • 4.  RE: Street Works Site SQL

    Posted 10-16-2020 08:54
    @Afzal Hussain

    Certainly, I've responded to your thread.

    @Richard White

    I forgot to mention (I think we possibly discussed this on a recent Tech Support case), be mindful joining the sw_notice_header and sw_notice_site tables using the work_version_no. These don't always correlate, because we don't always log to that table when the Permit is updated; only when a change to the Street Works Site information (trenches) or the USRN (Site Code) occurs.


    Kind regards,
    Steve
    ​​

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

    Original Message