How do I sort by Date correctly?-CodePudding

I'm trying to sort by date in one of the columns but with the code I do, it comes not in order. For example, if I order by descending date, it will come up as 31st October 2022 then the next couple would be 31st May 2024 then 31st May 2023, 31 March 2023, 31st July 2023, 31st January 2023, 31st December 2022. I need to be able to sort by deals which will have a close date nearer the time.

The code is:

select D.ROWID,
    '<b style="font-size: large; color:#DC4300;">' || D.CUSTOMER || '</b><br>' ||
    '<b>Project Type: </b>' || D.PROJECT || '<br>' ||
    '<b>Engagement Type: </b>' || NVL(D.ENGAGEMENT, 'Not Specified') || '<br>' ||
    '<b>Opportunity ID: </b>' || NVL(D.OPP_ID, 'Not Specified') || '<br>' ||
    --'<b>SF Rep: </b>' || com.COMMERCIAL_MANAGER || '<br>' ||
    '<b>CPR: </b>' || D.CPR || '<br>' ||
    '<b>VP: </b>' || NVL(D.VP, 'N/A') || '<br>' ||
    '<b>Country: </b>' || c.COUNTRY as ENGAGEMENT,
    D.CUSTOMER,
    com.COMMERCIAL_MANAGER,
    D.ARR,
    case D.LICENSE
        when null then 0
        else D.LICENSE
    end as LICENSE,
    D.SALES_STAGE,
    NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') as CLOSING_DATE,
    '<b>Deal Summary: </b>' || D.COMPLEX_DEMANDS || '<br>' ||
    '<b>Approval Date: </b>' || NVL(to_char(D.APPROVALS_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br>' ||
    '<b>Drafting Date: </b>' || NVL(to_char(D.DRAFTING_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    --'<b style="color: #DC4300;">Closing Date: </b>' || NVL(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') || '<br><br>' ||
    '<b>Customer Facing: </b>' || NVL(D.CUSTOMER_FACING, 'Not Specified') || '<br>' ||
    '<b>Tier 3 & 1 Engaged: </b>' || D.TIER3_1 || '<br>' ||
    '<b>Closure Risk(s): </b>' || NVL(D.CLOSURE_RISK, 'None') || '<br><br>' ||
    '<b>Challenge(s): </b>' || NVL(D.CHALLENGES, 'None') as DEAL_DETAILS,
    NVL(D.FBE, 'N/A'),
    D.NEXT_STEPS || '<br>' || 
    '<b>Contract Standpoint: </b>' || NVL(D.CONTRACT_STANDPOINT, 'N/A')|| '<br>' ||
    '<b>Main Contact: </b>' || D.CUSTOMER_CONTACT_NAME || ' - ' || D.CUSTOMER_CONTACT_ROLE as NEXT_STEPS
   
from TECHCOM D, 
    COUNTRY_LOOKUP c, 
    COMMERCIAL_MANAGER_LOOKUP com
where D.country_ID = c.country_ID
    and D.COMMERCIAL_MANAGER_ID = com.COMMERCIAL_MANAGER_ID
    and D.ARR is not null
    and not(D.SALES_STAGE in ('Won', 'Lost'))
ORDER BY D.ARR desc

CodePudding user response：

oracle-apex tag suggests you use Oracle database.

D.ARR is that column, isn't it? Looks like its datatype is VARCHAR2 so data is sorted as strings, not dates. That's most usually a bad idea - dates should be stored into DATE datatype columns.

Anyway: if these values are as you said, then convert them to DATEs first (using to_date function with appropriate format model), and then sort them. Unfortunately, the th format model can be used only for output, so you'll have to use substrings.

Something like this:

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with test (name, arr) as
  2    (select 'Little' , '31st January 2024'  from dual union all
  3     select 'Foot'   , '13th July 2026'     from dual union all
  4     select 'Redders', '2nd September 2022' from dual
  5    )
  6  select name,
  7    arr,
  8    to_date(
  9      regexp_substr(arr, '\d ', 1, 1)          ||' '||       --> day
 10      regexp_substr(arr, '[[:alpha:]] ', 1, 2) ||' '||       --> month name
 11      regexp_substr(arr, '\d $')                             --> year
 12      , 'dd Month yyyy') arr_date
 13  from test
 14  order by arr_date;

NAME    ARR                ARR_DATE
------- ------------------ ----------
Redders 2nd September 2022 02.09.2022
Little  31st January 2024  31.01.2024
Foot    13th July 2026     13.07.2026

SQL>

Once again: if you stored dates as dates, you'd simply order by arr. If you have a large data set, performance might suffer because of what you have to do with these values.

CodePudding user response：

Use the underlying D.close_date column, which already has a DATE data-type (rather than using a string formatted date such as using the CLOSING_DATE alias in the ordering):

select D.ROWID,
       D.CUSTOMER,
       D.PROJECT as project_type,
       COALESCE(D.ENGAGEMENT, 'Not Specified') AS engagement_type,
       COALESCE(D.OPP_ID, 'Not Specified') AS Opportunity_ID,
       D.CPR,
       COALESCE(D.VP, 'N/A') AS vp,
       c.COUNTRY,
       D.CUSTOMER,
       com.COMMERCIAL_MANAGER,
       D.ARR,
       COALESCE(D.LICENSE, 0) as LICENSE,
       D.SALES_STAGE,
       COALESCE(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') as CLOSING_DATE,
       D.COMPLEX_DEMANDS As deal_demands,
       COALESCE(to_char(D.APPROVALS_DATE, 'ddth Month YYYY'), 'Not Specified') AS approval_date,
       COALESCE(to_char(D.DRAFTING_DATE, 'ddth Month YYYY'), 'Not Specified') AS Drafting_Date,
       COALESCE(to_char(D.CLOSE_DATE, 'ddth Month YYYY'), 'Not Specified') AS closing_date,
       COALESCE(D.CUSTOMER_FACING, 'Not Specified') AS Customer_Facing,
       D.TIER3_1,
       COALESCE(D.CLOSURE_RISK, 'None') AS closure_risk,
       COALESCE(D.CHALLENGES, 'None') as Challenges,
       COALESCE(D.FBE, 'N/A') AS fbe,
       D.NEXT_STEPS AS next_steps,
       COALESCE(D.CONTRACT_STANDPOINT, 'N/A') As Contract_Standpoint,
       D.CUSTOMER_CONTACT_NAME AS Main_Contact,
       D.CUSTOMER_CONTACT_ROLE
from   TECHCOM D
       INNER JOIN COUNTRY_LOOKUP c
       ON (D.country_ID = c.country_ID)
       INNER JOIN COMMERCIAL_MANAGER_LOOKUP com
       ON (D.COMMERCIAL_MANAGER_ID = com.COMMERCIAL_MANAGER_ID)
where  D.ARR is not null
and    D.SALES_STAGE NOT IN ('Won', 'Lost')
ORDER BY D.close_date desc

Then format the page using Apex (rather than embedding the HTML in the SQL code) and use semantic markup and CSS to style the page (rather than HTML markup and inline styles).