SQL return date based on another column's date

Question

I have a rain forecast table that predicts the expected rainy date of two cities. The table looks like this:

CREATE TABLE raincheck(
city varchar(255),
expected_rain_date date,
actual_rain_date date);
INSERT INTO raincheck 
(city,expected_rain_date,actual_rain_date) VALUES 
('NYC','2001-02-02',null),
('NYC','2001-02-11',null),
('NYC','2001-03-20','2001-03-21'),
('NYC','2001-03-25',null),
('NYC','2001-04-10',null),
('NYC','2001-04-12','2001-04-12'),
('LDN','2001-02-03',null),
('LDN','2001-02-07','2001-02-07'),
('LDN','2001-03-11',null),
('LDN','2001-03-12',null),
('LDN','2001-03-17',null),
('LDN','2001-03-20','2001-03-20');
SELECT * FROM raincheck;

Since the actual rainy is not always accurate, I need a table with the time duration between the first expected rain date and the latest actual rain date. The result looks like this:

CREATE TABLE rainresult(
city varchar(255),
expected_rain_date date,
actual_rain_date date);
INSERT INTO rainresult
(city,expected_rain_date,actual_rain_date) VALUE
('NYC','2001-02-02','2001-03-21'),
('NYC','2001-03-25','2001-04-12'),
('LDN','2001-02-03','2001-02-07'),
('LDN','2001-03-11','2001-03-20');
SELECT * FROM rainresult;

I think the query may have something to do with NULL, like SELECT * FROM rainresult WHERE actual_rain_date IS NOT NULL; but I can't filter the expected_rain_date

Gordon Linoff · Accepted Answer · 2021-01-29 19:40:35Z

1

You want to assign groups based on the count of actual rain dates on or after each row. Then aggregate:

select city, min(expected_rain_date), max(actual_rain_date)
from (select rc.*,
             sum(case when actual_rain_date is not null then 1 else 0 end) over (partition by city order by expected_rain_date desc) as grp
      from raincheck rc
     ) rc
group by city, grp;

Here is a db<>fiddle. It happens to use Postgres, but this is all standard SQL.

answered Jan 29, 2021 at 19:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL return date based on another column's date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related