PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I find the latest date where a particular query returns no rows in a table?

I have a SQL query:

select max(date) 
    from index_constituents 
    where (opening_closing ='O' 
        and index_code ='buk350n' 
        and issuer = 'cboe') and date = '2024-04-25';
id date issuer index_code opening_closing
1393 2024-04-25 cboe buk350n O
1394 2024-04-25 cboe buk350n O

When I run the same query but for the day before, I get:

```sql
select max(date) 
    from index_constituents 
    where (opening_closing ='O' 
        and index_code ='buk350n' 
        and issuer = 'cboe') and date = '2024-04-24';
```

 id    |   date        |  issuer |  index_code   | opening_closing
 -     |  -            | -       | -             | -
1402   |   2024-04-24  |   cboe  |   buk350n     |   O

When I run

```sql
select max(date) 
    from index_constituents 
    where (opening_closing ='O' 
        and index_code ='buk350n' 
        and issuer = 'cboe') and date = '2024-04-23';
```

NOTHING IS RETURNED.

So I want to return 2024-04-23

i.e. I want to find the most recent date BEFORE A GIVEN DATE where there is no data when I run my query

Tags:

3

Answers


  1. 0

    It’s working properly. May be you’re confusing about columns data type or values:

    CREATE TABLE index_constituents (id int, date date, issuer varchar(10),
  index_code varchar(255), opening_closing varchar(20));

INSERT INTO index_constituents VALUES
  (1393, '2024-04-25', 'cboe', 'buk350n', 'O'),
  (1394, '2024-04-25', 'cboe', 'buk350n', 'O'),
  (1395, '2024-03-25', 'cboe', 'buk350n', 'O'),
  (1402, '2024-04-24', 'cboe', 'buk350n', 'O');

    select max(date)
    from index_constituents 
    where (opening_closing = 'O'
        and index_code ='buk350n' 
        and issuer = 'cboe');

    For check output :

    Please check your column date & opening_closing data_type.
    date column maybe varchar and opening_closing may be boolean.

    Please check the table description:

    SELECT * 
  FROM information_schema.columns 
  WHERE table_name = 'index_constituents';
    Login or Signup to reply.
  2. 0

    If you need to find latest date with no record after existing record’s date then you can use 1st query:

    select 
"date"-1 latest_unfound_record
from 
(
select 
"date" - LEAD(date) OVER (ORDER BY date desc) date_difference_in_days,
* 
from index_constituents
) as temp
where date_difference_in_days>1
order by date desc
LIMIT 1;

    If you need to find latest date before today with no records then use this second query:

    select 
next_date-1 latest
from 
(
select 
"date" - COALESCE (LEAD(date) OVER (ORDER BY date), CURRENT_DATE) date_difference_in_days,
COALESCE (LEAD(date) OVER (ORDER BY date), CURRENT_DATE) AS next_date , 
* 
from index_constituents where date <= CURRENT_DATE
) as temp
where date_difference_in_days<-1
order by date desc
LIMIT 1;
    Login or Signup to reply.
  3. 0 
    with dates as (
    select date, date - lag(date, 1, date - 2) over (order by date) as gap
    from index_constituents 
    where opening_closing ='O' and index_code ='buk350n' and issuer = 'cboe'
    -- and date <= <start date> -- if you want to begin the search on a specific date
    group by date
)
select
    max(case when gap > 1 then date end) - 1 as GapBeforeLatestAvailableDate,
    --, case when <start date> - max(date) > 1
    --     then <start date> - 1
    --     else max(case when gap > 1 then date end) - 1 end as GapBeforeSpecificDate
from dates;

    This gets a list of distinct dates where the criteria are met. Each date is compared to the next earliest on the list to compute the gap starting with whatever is the latest date found. You want to find the day before the latest date where there’s a gap larger than one.

    If no such gap exists within the data it will be treated as though occurring on the day before the earliest date found. If there are no rows meeting the criteria at all then no date is returned either.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search