Confirm

Back to discussions
Expand all | Collapse all

Enquiry Commitment SQL

  • 1.  Enquiry Commitment SQL

    Posted 08-06-2020 08:43
    Hello,

    I am trying to make a Dashboard that displays Enquiries as "overdue" or "still in time" based on the commitment timescale. When the commitment is unresolved but past the commitment target time then it would show as Overdue. If the Enquiry was unresolved before the target date then it would display as "Still in Time".

    However there isn't an unresolved/resolved table. The commit_actual_date is the date that the commitment was resolved. Until it is resolved the commit_actual_date value is 00/00/0000 00:00:00. I need a dashboard that pulls out commitments that have a commit_actual_date value of 00/00/0000 00:00:00 on/after the commit_target_date because they must be unresolved and therefore overdue. I tried the following case in the SQL:

    CASE WHEN enquiry_commitment.commit_actual_time = '00/00/0000' AND CURRENT TIMESTAMP > enquiry_commitment.commit_target_time THEN 'Late'

    And got nowhere. 

    Does anyone have any ideas how to make this work or has made a similar dashboard using the commitment schema?

    Thank you



    ------------------------------
    Rebecca Clark
    Hull City Council
    HULL
    ------------------------------


  • 2.  RE: Enquiry Commitment SQL

    Posted 08-06-2020 10:01
    Hi Rebecca, I am not an expert.. below is the SQL which we use to determine if a enquiry commitment for a particular Service has failed the Target Date or met the Target Date and provides a count, I am sure other users will be able to help.

    Kind Regards
    Scott


    SELECT commitment_status + ' - ' + cast(count(commitment_status) as varchar) as Commitment_Status,
    COUNT(commitment_status) as Subtotal
    FROM
    (
    SELECT CASE
    WHEN(ec2.commit_target_time < ec2.commit_actual_time)
    THEN 'Failed Target'
    WHEN(ec2.commit_target_time > ec2.commit_actual_time)
    THEN 'Completed on Target'
    ELSE 'Error with case statement'
    END Commitment_Status
    FROM dbo.enquiry_commitment as ec2
    INNER JOIN dbo.commitment as c ON c.commitment_code = ec2.commitment_code
    INNER JOIN dbo.central_enquiry as ce ON ce.enquiry_number = ec2.enquiry_number
    INNER JOIN dbo.enquiry_subject as es ON es.service_code = ce.service_code
    AND es.subject_code = ce.subject_code
    INNER JOIN dbo.type_of_service as tos ON tos.service_code = es.service_code
    WHERE 1 = 1
    AND ec2.commit_actual_time IS NOT NULL
    AND tos.service_code LIKE 'PUTR'
    AND c.commitment_code LIKE 'CC24'
    ) ilv_comm_stat
    GROUP BY commitment_status

    ------------------------------
    Scott Hill-Anderson
    Senior Technical Officer
    WEST LOTHIAN COUNCIL
    LIVINGSTON
    ------------------------------

    Original Message


  • 3.  RE: Enquiry Commitment SQL

    Posted 08-07-2020 09:32
    Hello Rebecca,

    For the actual date not being populated you would want to add a statement like WHERE commit_actual_time IS NULL. Then the report would only have rows of data showing those that had not yet been resolved.

    You can then compare the current system date against the target to determine if the outstanding items are overdue or still in time in your case statement.

    Regards,

    Andrew


    ------------------------------
    Andrew Stacey
    Ringway Specialist Services
    Horsham
    ------------------------------

    Original Message


  • 4.  RE: Enquiry Commitment SQL

    Posted 08-07-2020 10:37
    Hi Andrew,
    Thank you, that was really useful. I also haven't done a nested case before which is what I am assuming is this is? I have got this far:

    CASE WHEN enquiry_commitment.commit_actual_time IS NULL THEN (
         CASE WHEN CURRENT_TIMESTAMP > enquiry_commitment.commit_target_time THEN 'Late'
         ELSE 'IN TIME'
    END as 'Time_Category'
    )

    I can't make it work, which is very frustrating for a Friday afternoon. Do you have any advice?

    ------------------------------
    Rebecca Clark
    Hull City Council
    HULL
    ------------------------------

    Original Message


  • 5.  RE: Enquiry Commitment SQL

    Posted 08-07-2020 11:09
    Hi Rebecca,

    Do you need those commitments that have already been met in your report output? If you don't you could put that IS NULL statement in the WHERE statement after any joins.

    If you need those that have been completed, then the simplest way may be to just have 4 scenarios in your case statement:

    CASE
    WHEN not completed (IS NULL) and overdue THEN
    WHEN not completed (IS NULL) and still in time THEN
    WHEN completed (IS NOT NULL) and not in time THEN
    WHEN completed (IS NOT NULL) and in time THEN
    END as

    When looking at not completed compare the current time to the commitment target and when completed compare the commitment completed date against the commitment target

    I hope that helps

    Andrew

    ------------------------------
    Andrew Stacey
    Ringway Specialist Services
    Horsham
    ------------------------------

    Original Message


  • 6.  RE: Enquiry Commitment SQL

    Posted 08-10-2020 06:12
    Hi Rebecca,

    Try this example:

    SELECT 
   central_enquiry.enquiry_number,
   central_enquiry.enquiry_desc,
   central_enquiry.enquiry_time,
   central_enquiry.commit_log_number,
   enquiry_commitment.commit_log_time,
   enquiry_commitment.commit_target_time,
   enquiry_commitment.commit_actual_time,   
   (CASE      
      WHEN enquiry_commitment.commit_target_time <= SYSDATE THEN 'Overdue'
      WHEN enquiry_commitment.commit_target_time > SYSDATE THEN 'Still in time'
   END) commitment_due
FROM 
   central_enquiry,
   enquiry_commitment
WHERE
   central_enquiry.enquiry_number = enquiry_commitment.enquiry_number AND
   central_enquiry.commit_log_number = enquiry_commitment.commit_log_number AND
   enquiry_commitment.commit_actual_time IS NULL AND
   central_enquiry.outstanding_flag = 'Y'​

    Hope this helps.

    Kind regards,
    Steve

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

    Original Message


  • 7.  RE: Enquiry Commitment SQL

    Posted 08-17-2020 08:13
    Hi Andrew and Steve,

    Thank you for your help with Enquiry Commitment SQL, I now have a useful dashboard.

    Thanks again.

    ------------------------------
    Rebecca Clark
    Hull City Council
    HULL
    ------------------------------

    Original Message