How to Select The Lowest Date in one field that's higher than the date in another field

Question

The below aim isn't actually what my query is for, but I'm using it as an analogy to explain more simply what I am trying to achieve:

I am trying to build a BigQuery Script which looks at all arcade machines which have had a vandalism alarm triggered, it then identifies all of those machines which have either had no vends made since the alarm, or those where the first vend was more than 28 days after the alarm was triggerred, in order to identify machines where free use could have occurred after the alarm was triggerred.

SO far, I have highlighted all of these instances, however where I have joined the Payments table to the query, all vends made >28 days after the alarm per machine are being returned, where I am only interested in returning the first vend after the alarm.

I have 3 tables

alarm

machine_num	alarm_date
111	2022-01-20
222	2022-01-20
123	2022-01-20
456	2022-01-20

Customer

cust_num	machine_num
1	111
2	222
3	123
4	456

payments

cust_num	vend_date
1	2022-01-10
1	2022-01-21
1	2022-02-21
2	2022-01-11
2	2022-01-19
3	2022-01-01
3	2022-01-10
3	2022-03-01
3	2022-03-03
3	2022-03-04
4	2022-01-19
4	2022-04-20
4	2022-04-21

So in this case: cust_num "1" woudln't be returned, as there was a vend less than 28 days after the alarm cust_num "2" Would be returned with the vend date as NULL since no Vends have been made since the alarm cust_num "3" Would be returned with the vend date as "2022-03-01" since this is the first Vend after the alarm cust_num "4" Would be returned with the vend date as "2022-04-20" since this is the first Vend after the alarm

I need to return all 3 fields, so based on the above example, my output would be

cust_num	machine_num	alarm_date	vend_date
2	222	2022-01-20	NULL
3	333	2022-01-20	2022-03-01
4	444	2022-01-20	2022-04-20

I've tried adding a sub-query in my select statement similar to the below:

MIN(vend_date)
FROM payments AS paymin
WHERE paymin.vend_date > alarm_date)
AS vend_date

However this just tends to cause bigquery to run longer than I have the patience to wait for it for when I add the subquery to my existing query.

I've never asked for help on one of these sites before, so apologies if I am asking in the wrong place or in the wrong way! I'm still relatively new to BQ and work very distantly from any analysts in the business.

Any help is really appreciated!

Cheers

__ EDIT:

So I gave up with the sub query, it was too intensive as it was querying almost 2 million rows!

I tried using simple MIN and grouping everything together, similar to this simplified example

SELECT
 payments.cust_num,
 alarm.machine_num, p.PAN, alarm.alarm_date, MIN(payments.vend_date) as vend_Date
 
 FROM alarm
    
LEFT JOIN customer
    ON alarm.machine_num = customer.machine_num
 INNER JOIN payments
    ON customer.cust_num =payments.cust_num 

WHERE 
vend_Date > DATE_ADD(alarm.alarm_date, INTERVAL +28 DAY) OR 
vend_Date IS NULL

GROUP BY payments.cust_num, alarm.machine_num, alarm.alarm_date
ORDER BY  payments.cust_num

however This isn't picking up instances where first vend_date after alarm_date is NULL It also only returns the first date that is newer than 28 days after the alarm date, not accounts WHEN the first vend_date is newer than 28 days after the alarm_date

Answer 1

The query below works on my system and makes the output you suppose I hope it works for you

 IF OBJECT_ID ('pay' , N'IF') IS NOT NULL
    -- deletes function
        DROP FUNCTION pay
    go
    create function pay()
    returns table
    as return 
            with cte as ----------------------------------------to merge alarm table and customer table
                (select
                customer_num,machin_num,
                (select max(alarm_date) from alarm as a 
                    where a.machin_num= c.machin_num ) as date
                from customer as c)
        select ------------------------------------------------ to marge payment table
            cte.customer_num, cte.machin_num,cte.date,
            case when datediff(day,cte.date,p.vend_date) > 0 then p.vend_date
            else Null end as vend_date,
            datediff(day,cte.date,p.vend_date) as diff
        from  cte  
        left join  peyments as p
            on cte.customer_num= p.cusromer_num
    ------------------------------------------using table valued function to make it easy to join result with itself
    Go
    select distinct p1.customer_num,p1.machin_num,p1.date, min(p1.vend_date)
    from pay() as p1
     where p1.customer_num not in (select p2.customer_num from pay() as p2 where p2.diff between 0 and 28)
     group by p1.customer_num,p1.machin_num,p1.date
     order by p1.customer_num
        

Answer 2

Thank you all so much for your help Although I didn't quite get the grips of user defined functions in the end, I did however manage to achieve my goal in the end in a similar way.

Here's my final query (heavily changed for readability) And with additional criteria too (i had to make a temporary payments table as the customer numbers were stored as string in that table, had an extra digit on the end, and any numbers shorter than 10 characters had 0's appended to the begining! the Date was also string and in some mad format!)

    WITH pay AS( 
  WITH payments AS (
      SELECT 
        CASE
          WHEN LENGTH(customer_num)>9
           THEN LEFT(CAST (CAST(customer_num AS INTEGER) AS STRING),LENGTH(CAST (CAST(customer_num AS INTEGER) AS STRING))-1)
          ELSE CAST (CAST(customer_num AS INTEGER) AS STRING) 
        END AS customer_num,
        PAN,
        CASE
          WHEN LENGTH(vend_Date)=10
           THEN PARSE_DATE('%Y-%m-%d',vend_Date) 
          ELSE NULL
          END AS vend_Date
      FROM `Vend_Records`),
    
    smart AS (
      SELECT
        customer_num, 
        Primary_Machine_Num AS Machine_Num,
        Account_Start_Date,
        Account_End_Date,   
        closure_type,
          (SELECT 
            MAX(PARSE_DATE('%Y-%m-%d',r.CREATION_DATE_TIME)), 
          FROM `alarm` AS r  
            WHERE r.Machine_Num=Primary_Machine_Num) AS alarm_date
      FROM `customer`)
        
  SELECT 
    cte.customer_num, 
    cte.Machine_Num,
    cte.msn AS Meter_Serial_No, 
    cte.alarm_date, 
    CASE WHEN
           DATE_DIFF(p.vend_date, cte.alarm_date, DAY) >0 THEN p.vend_date  
          ELSE NULL
          END AS vend_date,
      CASE
    WHEN DATE_DIFF(vend_date, alarm_date,DAY) >28
    THEN "vend outside of 28 days"
    WHEN DATE_DIFF(vend_date, alarm_date,DAY)<0
    THEN "No vend after alert"
    WHEN DATE_DIFF(p.vend_date, cte.alarm_date, DAY) BETWEEN 0 AND 28 
    THEN "vend made after alert"
    ELSE "No vend on report"
    END AS Status,
    cte.Account_Start_Date,
    cte.account_End_Date,
    cte.closure_type
  FROM smart
  LEFT JOIN payments AS p
  on cte.customer_num=p.customer_num
  WHERE (alarm_date IS NOT NULL)
  AND DATE_DIFF(Account_Start_Date, alarm_date,DAY)<0 OR Account_Start_date IS NULL
  AND closure_type NOT IN ("COS")

)

SELECT  
  customer_num,
  Machine_Num,
  alarm_date, 
  MIN(vend_Date) AS vend_date,
  Status,
  closure_type
FROM pay AS p1
  WHERE alarm_date > CAST("2021-01-01" AS DATE)
  AND p1.customer_num NOT IN 
                        (SELECT p2.customer_num FROM pay AS p2
                        WHERE p2.status = "vend made after alert")
  GROUP BY customer_num, Machine_Num, alarm_date, status, Meter_Serial_No,PAN, closure_type;