How to get the latest data until a criteria is met? [SQL]

Question

I'm new to SQL programming, and im wondering how can one go about getting the latest row based on a criteria that needs looping. In VBA, it's just a simple do loop until, but in SQL i looked thru recursive CTE and i tried subqueries but i dont think the database itself supports it. I'm using PowerQuery from Excel to connect to the database with SQL

+--------+--------+--------+
| Status |  Date  | Amount |
+--------+--------+--------+
| n      | 18-Mar |    -50 |
| c      | 17-Mar |     50 |
| n      | 16-Mar |    -50 |
| c      | 15-Mar |     50 |
| n      | 14-Mar |    -50 |
| c      | 13-Mar |     50 |
| c      | 12-Mar |     50 |
+--------+--------+--------+

The result i'm trying to get is the date 12-MAR. Basically, the coding logic is to get the latest status 'C' without an 'N' after it.

        select status,max(date) 'Max Date',amount
    from table A
    where status <> 'n' and 
-- where i dont know how to make it check to see if there's a status 'n' after max date and if so, loop back until it finds a status 'C' without having a status 'N' after --
    group by status

Many thanks in advance for your help!

Answer 1

The query below will show you ALL rows without that match your criteria, not just the last one:

select
  *
  from (
    select status, date, amount,
        lead(status) over(order by date) as next_status
      from my_table
  ) x
  where status = 'c' and next_status <> 'n'

if you just want the latest date, then change it to:

select
  max(date)
  from (
    select status, date, amount,
        lead(status) over(order by date) as next_status
      from my_table
  ) x
  where status = 'c' and next_status <> 'n'

A note: try to avoid having a column with the name date since this is usually a reserved word. Maybe something like recorded_at or similar makes your life simpler.

Edit: If your tool does not support subqueries, what about a CTE? It's worth a try :) Here's the modified version:

with a (status, date, amount, next_status) as (
  select status, date, amount,
      lead(status) over(order by date) as next_status
    from my_table
  )
select * from a  
  where status = 'c' and next_status <> 'n'

Answer 2

UPDATE

You might have more than 1 result per day and you said you want to ensure the following:

• Current status is 'c'
• Previous status (day before) is 'c'
• There is no previous status (day before) of 'n'

So to do this you have to do a second join, a left join, to ensure there is no relationship there, something like:

SELECT a1.* FROM A
INNER JOIN A a2 ON DATEDIFF(DAY, a1.Date, a2.Date) = 1 AND a2.Status = 'c'
LEFT JOIN A a3 ON DATEDIFF(DAY, a1.Date, a3.Date) = 1 AND a3.Status = 'n'
WHERE a3.Status IS NULL

This way you ensure that there is no previous status of 'n'.

Fiddle updated with this scenario: SQl Fiddle

ORIGINAL POST

I made it work with an INNER JOIN but in my example the "date" field is called "datefield" and is an integer. But can be changed easily.

What we need to know in order to help is if you are going to have a single record per day.

Anyways here is the exasmple: SQL Fiddle

The query would be something like the following:

SELECT a1.* FROM A
INNER JOIN A a2 ON DATEDIFF(DAY, a1.Date, a2.Date) = 1 AND a2.Status = 'c'

Hope this helps.