Selecting multiple dates in SQL

Question

I have a data table as follows:

 id    date1      date2        rate
 1     01/01/2017 03/01/2017   0.60
 1     02/01/2017 03/01/2017   0.40
 1     03/01/2017 03/01/2017   0.00
 1     04/01/2017 03/01/2017   0.00
 1     05/01/2017 03/01/2017   0.00
 2         .          .          .

For each id, I want to select the most recent positive value for rate before it goes to 0. So since date2 's value is 03/01/3017 , the value of id 1's rate goes to 0 on 03/01/3017 . I want to choose the row where the rate is 0.40 .

I would want to do this for multiple ids. Also date2 can't be a weekend.

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Mar 14 '18 at 01:38

score 0 · Answer 1 · answered Mar 14 '18 at 00:42

I think this will do what you are asking for:

SELECT    a.*
FROM      data_table a
          INNER JOIN
(
    SELECT id
         , MIN(date1) AS date1
    FROM   data_table
    WHERE  rate > 0
    GROUP BY id
) b ON a.id = b.id
       AND a.date1 = b.date1;

score 0 · Answer 2 · answered Mar 14 '18 at 01:56

Here is one approach. I give the table name as table1.

SELECT a.id, a.rate
FROM table1 a
WHERE a.date1 = (SELECT MAX(date1) FROM table1 WHERE a.id = id and rate <> 0)

table1 includes the data you provide and 2 lines of data with id = 2 which are made up by me.

DATA

CREATE TABLE table1 (id INT, date1 DATE, date2 DATE, rate FLOAT);
INSERT table1(id, date1, date2, rate) VALUES
(1,'2017-01-01','2017-03-01',0.60),
(1,'2017-02-01','2017-03-01',0.40),
(1,'2017-03-01','2017-03-01',0.00),
(1,'2017-04-01','2017-03-01',0.00),
(1,'2017-05-01','2017-03-01',0.00),
(2,'2017-06-01','2017-03-01',0.30),
(2,'2017-07-01','2017-03-01',0.00);

RESULT

id  rate
1   0.4
2   0.3

SQL Fiddle

Selecting multiple dates in SQL

2 Answers2