Min/ Max dates based on results of query

oracle

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)

Best Answer

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
Related Question