Question

0

664

Views

SQL query to get forward value based on some condition? (Without using window function)

I have 2 columns - Date and status

CampaignID     Date          Status    
123            2019-07-10    Active
123            2019-07-09    Paused 
123            2019-07-08    Paused
123            2019-07-07    Active
123            2019-07-06    Paused

Let's consider this is the campaign's data and we need to find the next active date of campaign in the new column as active_date given below. E.g if campaign is paused on date, then I need the next date (in a new column) whenever the campaign is active.
Help me with the SQL query without using window functions, as my DB does not support window functions.

CampaignID  Date          Status      Active_date
123         2019-07-10    Active      
123         2019-07-09    Paused      2019-07-10
123         2019-07-08    Paused      2019-07-10 
123         2019-07-07    Active       
123         2019-07-06    Paused      2019-07-07

over 4 years ago · Santiago Trujillo

2 answers

Answer question

0

I would recommend using window functions:

select t.*,
       (case when status <> 'Active'
             then min(case when status = 'Active' then Date end) over (partition by CampaignId order by date desc)
        end) as next_active
from t
order by CampaignID, Date desc;

Here is a db<>fiddle.

over 4 years ago · Santiago Trujillo Report

Answer question

Find remote jobs

Accepted Answer · 2022-05-11T21:59:53.049Z

You could use a correlated subquery to populate the last active date column:

SELECT
    CampaignID,
    Date,
    Status,
    CASE WHEN Status <> 'Active' THEN
         (SELECT t2.Date FROM yourTable t2
          WHERE t2.CampaignID = t1.CampaignID AND t2.Date > t1.Date AND t2.Status = 'Active'
          ORDER BY t2.Date LIMIT 1) END AS Active_Date
FROM yourTable t1
ORDER BY
    CampaignID,
    Date DESC;

Demo

The basic logic used above is straightforward. For each record whose status is not active, the correlated subquery looks ahead and tries to find the date of the nearest record whose status is active. We also only perform this lookup for records all belonging to the same campaign.

0

664

SQL query to get forward value based on some condition? (Without using window function)

2 answers

0

Demo

0

Find remote jobs

Andres GPT