Confirm

Expand all | Collapse all

Confirm Sql headers

  • 1.  Confirm Sql headers

    Posted 09-03-2019 12:21
    How do set sql statement so that Customer service enquiry status are horizontally displayed as header fields at the top, rather than have a list-type history record going vertically

    for example

    Customer no    Date  of application     Sent to Officer  Application Approved
    100001              01/07/2019                   03/07/2019    10/07/2019
    100002              10/09/2019                    14/09/2019    21/09/2019

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------


  • 2.  RE: Confirm Sql headers

    Posted 09-04-2019 02:53
    Hi Afzal,

    You will need to either use a Case statement or a sub-select to achieve this.

    e.g.

    CASE WHEN enquiry_status_log.enq_status_code = '[STATUS CODE]' THEN enquiry_status_log.logged_date END AS [STATUS NAME]

    or

    (SELECT TOP 1 enquiry_status_log.logged_date
     FROM enquiry_status_log
     WHERE
      enquiry_status_log.enq_status_code = '[STATUS CODE]' AND
      enquiry_status_log.enquiry_number = central_enquiry.enquiry_number) AS [STATUS NAME]

    You will need to repeat this for each status you need to display, changing the status code and field name.

    If you have the same status used multiple times, you will need a GROUP BY when using the Case statement with MIN/MAX.
    If using sub-select, TOP 1 will work, and you can add an ORDER BY depending on if you need the first or last date used. You may still also need a GROUP BY depending on the tables you have in the main query.

    To potentially simply the 'Date of Application' value, you could use central_enquiry.enquiry_time for this as this is the date the Confirm customer enquiry was raised. All other status updates can be retrieved from the enquiry_status_log table.

    Hope this helps.

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Confirm Systems Administrator
    London Borough Of Richmond Upon Thames
    Twickenham
    ------------------------------



  • 3.  RE: Confirm Sql headers

    Posted 09-05-2019 07:51
    Thank you Hafizur for this,

    I will try this out and see if this works

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------



  • 4.  RE: Confirm Sql headers

    Pitney Bowes
    Posted 09-05-2019 05:44
    Hi Afzal,

    Another way to achieve this is to use the PIVOT function.

    See example below:

    SELECT 
        enquiry_number,
        Date_of_application,
        Sent_to_Officer,
        Application_Approved
    FROM
    
    (SELECT
        enquiry_status_log.enquiry_number,
        enquiry_status.enq_status_name,
        enquiry_status_log.logged_date
    FROM
        enquiry_status_log,
        enquiry_status
    WHERE
        enquiry_status_log.enq_status_code = enquiry_status.enq_status_code)
        
    PIVOT
        (MAX(logged_date) 
        FOR enq_status_name IN (
                                'Date  of application' date_of_application,
                                'Sent to Officer' sent_to_officer,
                                'Application Approved' application_approved)
        );
        ​


    You would change the 'MAX' value to 'MIN' to give you the 1st occurrence of the status in the log, as opposed to 'MAX' which gives you the most recent.

    Regards,
    Steve



    ------------------------------
    Steve Bish
    PITNEY BOWES SOFTWARE, INC
    London
    ------------------------------



  • 5.  RE: Confirm Sql headers

    Posted 09-05-2019 07:49
    Hello Steve,

    Thank you for this, however on my enquiry status i have the following field names without spaces which i want to use but i cant put them like this in select statement, so how i would i achieve this. Do i replace the spaces with the underscore_

    DK: Application Fee Received (Code:DK02)
    DK: Passed onto Area Officer (Code: DK20)
    Application Approved (Code: DK24)


    SELECT
    enquiry_number,
    Date_of_application,
    Sent_to_Officer,
    Application_Approved

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------