How to search records based on date range for other matching data

querysubquery

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

Best Answer

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')));

Related Solutions

Sql-server – Date alignment and pair matching extraction best done with TSQL or C#

Here's something I was fiddling with based on some work I had laying around. It doesn't handle the events clustered at a single time well. It could theoretically be helpful anyhow...:)

;WITH ordered_rows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY EventTime) AS Row,
        Identifier, Type, EventTime, DiscoveredDate, FileId FROM #EventDataTemp
)
,filtered_rows AS
(
    SELECT Row, Identifier, Type, EventTime, DiscoveredDate, FileId, 
        CAST(CASE Type WHEN 'Went Off' THEN 1 ELSE NULL END AS INT) 
            AS OffEventRow
    FROM ordered_rows
    WHERE Row = 1
    UNION ALL
    SELECT o.Row, o.Identifier, o.Type, o.EventTime, o.DiscoveredDate, o.FileId,
        CAST(CASE WHEN o.Type = 'Went Off' AND f.Type = 'Went Off' 
            OR o.Type = 'Came On' THEN f.OffEventRow ELSE o.Row END AS INT)
    FROM ordered_rows o INNER JOIN filtered_rows f 
        ON o.Row = f.Row + 1 AND o.Identifier = f.Identifier
)
,on_events AS
(
   SELECT Identifier, OffEventRow, MAX(Row) AS OnRow
   FROM filtered_rows
   WHERE Type = 'Came On' AND OffEventRow IS NOT NULL
   GROUP BY OffEventRow, Identifier
)
SELECT f.Identifier, f.Type, f.EventTime, f.DiscoveredDate, f.FileId 
FROM filtered_rows f LEFT JOIN on_events o 
    ON f.Identifier = o.Identifier 
    AND f.Row = o.OnRow
WHERE f.Type = 'Went Off' AND f.Row = f.OffEventRow
OR f.Type = 'Came On' AND o.OnRow IS NOT NULL
ORDER BY f.Identifier, f.EventTime

MySQL – Query to Find Records Between Two Date Ranges

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.

Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends). Then exclude the employees with one or more matching rows in coaching.

SELECT e.id AS employee_id
  FROM employee e
  LEFT JOIN coaching c ON c.employee_id = e.id
                      AND c.end_date >= '2014-12-26'
                      AND c.start_date <= '2015-01-30'
 WHERE c.employee_id IS NULL;

Live demo: http://sqlfiddle.com/#!9/79b39/4

Best Answer

Related Solutions

Sql-server – Date alignment and pair matching extraction best done with TSQL or C#

MySQL – Query to Find Records Between Two Date Ranges

Related Question