0

I have below records in table

APP_No Stage Start_Date End_Date 
A123   L1    01-01-19 02-01-19 
A123   D1    01-03-19 05-03-19 
A345   L1    01-01-19 02-01-19 
A345   D1    01-03-19 05-03-19

I want to search records for stage D1 for the month of Mar19 but this should include data for L1 stage also, since related app ID fall between same Mar19 date range.
I have used condition in where clause as : trunc(Col_Name) BETWEEN trunc (sysdate, 'mm')/current month/ AND SYSDATE

Please help with solution

  • Dates are tricky and frequently non-standard. Please indicate your server. You can look at my profile for several articles about how to ask questions. p.s. welcome to the forum! :-) – Vérace Mar 30 at 10:10
0

Try ans use EXISTS to get all records where another (or the same for that matter) exists with the same app ID, a stage of 'D1' and the start date or the end date in march.

Assuming you're on Oralce, this could look like this:

SELECT *
       FROM elbat t1
       WHERE EXISTS (SELECT *
                            FROM elbat t2
                            WHERE t2.app_no = t1.app_no
                                  AND stage = 'D1'
                                  AND (t2.start_date >= to_date('2019-03-01', 'yyyy-mm-dd')
                                       AND t2.start_date < to_date('2019-04-01', 'yyyy-mm-dd')
                                        OR t2.end_date >= to_date('2019-03-01', 'yyyy-mm-dd')
                                           AND t2.end_date < to_date('2019-04-01', 'yyyy-mm-dd')));

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.