In a monthly report, I need to obtain the oldest date of a sequence of records for a given individual where the amount is greater than 0. I will continue reporting this date each month until the amount is zero at which point no date will be reported. If a nonzero amount is reported again afterwards, I will use this new date and continue reporting that date until the amount is once again zero.

In the example below for September's reporting, I would expect to return row 6 and row 17 since these are the oldest records in the most recent sequence of nonzero records. For the sake of simplicity, I grouped each person's records sequentially below but in the actual table they will be grouped by date rather than by person. In other words, all the 1/1/24 (M/D/YY) records will come first, then 2/1/24, and so on.

In a monthly report, I need to obtain the oldest date of a sequence of records for a given individual where the amount is greater than 0. I will continue reporting this date each month until the amount is zero at which point no date will be reported. If a nonzero amount is reported again afterwards, I will use this new date and continue reporting that date until the amount is once again zero.

In the example below for September's reporting, I would expect to return row 6 and row 17 since these are the oldest records in the most recent sequence of nonzero records. For the sake of simplicity, I grouped each person's records sequentially below but in the actual table they will be grouped by date rather than by person. In other words, all the 1/1/24 (M/D/YY) records will come first, then 2/1/24, and so on.

I was thinking of looping through the records in descending order (per individual) and stop when the amount field is no longer greater than 0. I would extract the date field for the last record that had an amount field greater than 0. Below is a crude example of the idea that I'm struggling to make work.

WHILE (SELECT amount FROM table ORDER BY id DESC) > 0
BEGIN
  SELECT date FROM table 
END

• sql
• sql-server
• t-sql

• 1 SQL is a set-based language. You don't loop your records. – Eric Commented Nov 19, 2024 at 20:53

4 Answers 4

There's likely a way to use lead/lag but here's an option that uses a CTE to get your max-zero date, then left join to it for getting the rows you want. The name "Linda" does not return a row because her most recent record is zero. This might need some work if you expect to see a user like "Marsha" who has multiple non-zero rows without ever having a zero. Just not sure if care about Marsha in this sample.

insert into table1 values 
(1, 'John', '01-01-2024', 156),
(2, 'John', '02-01-2024', 0),
(3, 'John', '03-01-2024', 221),
(4, 'John', '04-01-2024', 118),
(5, 'John', '05-01-2024', 0),
(6, 'John', '06-01-2024', 295),
(7, 'John', '07-01-2024', 134),
(8, 'John', '08-01-2024', 85),
(9, 'John', '09-01-2024', 42),
(10, 'Matt', '01-01-2024', 0),
(11, 'Matt', '02-01-2024', 0),
(12, 'Matt', '03-01-2024', 92),
(13, 'Matt', '04-01-2024', 842),
(14, 'Matt', '05-01-2024', 0),
(15, 'Matt', '06-01-2024', 150),
(16, 'Matt', '07-01-2024', 0),
(17, 'Matt', '08-01-2024', 76),
(18, 'Matt', '09-01-2024', 101),
(19, 'Linda', '01-01-2024', 55), 
(20, 'Linda', '02-01-2024', 0), 
(21, 'Marsha', '01-01-2024', 5);

with max_dates as (
  select name, max(order_date) as max_dt
  from table1
  where amount = 0
  group by name
  )
select t.*
from table1 t
left join max_dates md
  on t.name = md.name
where (
    (t.order_date = dateadd(month, 1, md.max_dt)) 
    or (md.max_dt is null)
    );

fiddle

1. Calculate max order_date for Name where amount=0
2. Take previous order_date If this values equal and current row amount is>0, include row to output. See example

select ID, Name, order_date, Amount
from(
  select *
    ,max(case when amount=0 then order_date end) over(partition by Name ) max0dt
    ,lag(order_date)over(partition by Name order by order_date) prevdt
  from table1
)t
where amount>0 and max0dt=prevdt

fiddle

If you want result for date use query with additional WHERE clause

select *
from(
select *
  ,max(case when amount=0 then order_date end) over(partition by Name ) max0dt
  ,lag(order_date)over(partition by Name order by order_date) prevdt
from table1
where order_date<='6/1/24' -- report date
)t
where amount>0 and max0dt=prevdt

OR

select *
from(
select *
  ,max(case when amount=0 then order_date end) over(partition by Name ) max0dt
  ,lag(order_date)over(partition by Name order by order_date) prevdt
from table1
where order_date<='9/1/24'  -- report date
)t
where amount>0 and max0dt=prevdt

fiddle

fiddle

using cte and window function is a great strategy

    
;with t
as
  (
select id,name,order_date,amount
       ,row_number() over (partition by name order by order_date desc) as rn
  from table1
 where amount > 0
)
select id,name,order_date,amount from t where rn = 1

or rank function

rank approach

;with t
as
  (
select id,name,order_date,amount
       ,rank() over (partition by name order by order_date desc) as rank
  from table1
 where amount > 0
)
select id,name,order_date,amount,rank from t where rank = 1

I’m sure there’s a fancier way using UNBOUND FOLLOWING, but this will do it too.

First, I set up data, adding info for users with no “zero” date and only a "zero date":

DROP TABLE if exists #SomeData
CREATE TABLE #SomeData
 (
   ID        int          not null
  ,Name      varchar(50)  not null
  ,SomeDate  date         not null
  ,Amount    int          not null
 )


INSERT #SomeData values
  (1,'John', '1/1/2024', 156)
 ,(2,'John', '2/1/2024', 0)
 ,(3,'John', '3/1/2024', 221)
 ,(4,'John', '4/1/2024', 118)
 ,(5,'John', '5/1/2024', 0)
 ,(6,'John', '6/1/2024', 295)
 ,(7,'John', '7/1/2024', 134)
 ,(8,'John', '8/1/2024', 85)
 ,(9,'John', '9/1/2024', 42)
 ,(10,'Matt', '1/1/2024', 0)
 ,(11,'Matt', '2/1/2024', 0)
 ,(12,'Matt', '3/1/2024', 92)
 ,(13,'Matt', '4/1/2024', 842)
 ,(14,'Matt', '5/1/2024', 0)
 ,(15,'Matt', '6/1/2024', 150)
 ,(16,'Matt', '7/1/2024', 0)
 ,(17,'Matt', '8/1/2024', 76)
 ,(18,'Matt', '9/1/2024', 101)
 ,(19,'Tony', '7/1/2024', 37)
 ,(19,'Tony', '8/1/2024', 29)
 ,(19,'Tony', '9/1/2024', 17)
 ,(20,'Aaron', '9/1/2024', 0)

Besides the datatypes, the following makes a few assumptions:

• No negative amounts
• No dates earlier than Jan 1, 1980 (could make this 1900, but I’m a traditionalist)

    SELECT
       sd.Name
      ,min(sd.SomeDate)  CurrentSequence_OldestNonZeroDate
     from #SomeData  sd
      left outer join (--  Most recent zero date, if any)
                       select
                          Name
                         ,max(SomeDate)  MostRecentZero
                        from #SomeData
                        where Amount = 0
                        group by Name)  mrz
       on mrz.Name = sd.Name
     where sd.SomeDate > isnull(mrz.MostRecentZero, 'Jan 1, 1980')
     group by sd.Name