1

Need to find the Max and Min dates using the results of a query. The following query is working but have to add additional code to find the Max/ Min dates based on its results (results of dates from last 2 AND statements). Using Oracle SQL Developer.

SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO
FROM S_ORDER SO, S_SERV SE
WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
AND TO_DATE('2018-07-30','YYYY-MM-DD')

Tried to add the Max/ Min statements to the end but getting an Invalid Statement error. Thanks for any help.

Results of the query will therefore be:

COM_DATE | RAISED_DATE | SERV_NO | OR_NO
2018-07-05 | 2018-07-04 | 69780 | GHYUYR     
2018-06-27 | 2018-06-08 | 69780 | HHUYTT  
2018-03-10 | 2018-02-04 | 69780 | JJUHYT

Therefore, after the Min/ Max code added, the results should ONLY return:

COM_DATE | RAISED_DATE | 
2018-07-05 (max)| 2018-02-04 (min)
  • Not entirely clear what your expected results are. Your SELECT list includes two date columns (so.com_date, so.raised_date); which one do you wan the MIN and MAX of? I assume that your reference to "the last 2 AND statements" really means the so.raised_date +4/24 BETWEEN part of the clause (syntax is X BETWEEN A AND B). Are you looking for the MIN and MAX for each combination of com_date, serv_no, and or_no, or for all rows returned? – RDFozz Jul 10 '18 at 17:51
  • Looking to get the Min/ Max of the Raised_Dates. Will edit the question to be clearer – R. John Jul 10 '18 at 17:54
  • Do you want all the results from this query plus the min and max dates? Or only the min and max dates? – ypercubeᵀᴹ Jul 10 '18 at 17:58
0

This should work to get the min and max date for you.

SELECT MIN(RAISED_DATE), MAX(RAISED_DATE), MIN(COM_DATE), MAX(COM_DATE)
FROM   (SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO
        FROM S_ORDER SO, S_SERV SE
        WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
        AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
        AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
        AND TO_DATE('2018-07-30','YYYY-MM-DD'))

If you want the other data in the query you will need to add a group by

SELECT SO.COM_DATE, MIN(SO.RAISED_DATE), MAX(SO.RAISED_DATE), SE.SERV_NO, SO.OR_NO
FROM S_ORDER SO, S_SERV SE
WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
AND TO_DATE('2018-07-30','YYYY-MM-DD')
GROUP BY SO.COM_DATE, SE.SERV_NO, SO.OR_NO
0

If you want all the results from the query plus the min and max dates in 2 additional columns, you can use window functions:

SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO,
       MIN(SO.RAISED_DATE) OVER () AS MIN_RAISED_DATE,
       MAX(SO.RAISED_DATE) OVER () AS MAX_RAISED_DATE
FROM  -- the rest of the query unchanged

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.