How to generate test data for the "group by data from other rows" algorithm

Question

UPDATE: I am looking for a method to calculate data for all cases of the edges of my algorithm (or an arbitrary algorithm, for that matter).
What I have tried so far just thinks about what could be edge cases +, producing some “random” data, but I don’t know how I can be sure that I did not miss something, being able to ruin ..

I want to check that I have not missed something important in my algorithm, and I do not know how to generate test data to cover all possible situations:

Task consists in reporting snapshots of data for each Event_Date, but making a separate line for changes that may belong to the following Event_Date- see group 2) data input and output :

My algorithm :

• make a list Event_Dateand calculate next_event_datefor them
• attach the results to main_audit_tableand calculate the largest transaction_idfor each snapshot (groups 1-4 in my illustration) - grouped by id, Event_Dateand by 2 options based on whether transaction_date < next_event_datetrue or not
• attach main_audit_tableto the results to get other data from the sametransaction_id
• attach costs_audit_tableto the results - use the largest transaction_id, which is smaller transaction_idfrom the result

():

• , , , ?
• ?
• ?

( ):

select
    snapshots.id,
    snapshots.event_date,
    main.event,
    main.transaction_date as last_change,
    costs.costs as costs_2012
  from (
    --snapshots that return correct transaction ids grouped by event_date
    select
      main_grp.id,
      main_grp.event_date,
      max(main_grp.transaction_id) main_transaction_id,
      max(costs_grp.transaction_id) costs_transaction_id
    from main_audit_table main_grp
    join (
      --list of all event_dates and their next_event_dates
      select
        id,
        event_date,
        coalesce(lead(event_date) over (partition by id order by event_date),
                 '1.1.2099') next_event_date
      from main_audit_table
      group by main_grp.id, main_grp.event_date
    ) list on list.id = main_grp.id and list.event_date = main_grp.event_date
    left join costs_audit_table costs_grp
      on costs_grp.id = main_grp.id and
         costs_grp.year = 2012 and
         costs_grp.transaction_id <= main_grp.transaction_id
    group by
      main_grp.id,
      main_grp.event_date,
      case when main_grp.transaction_date < list.next_event_date
           then 1
           else 0 end
  ) snapshots
  join main_audit_table main
    on main.id = snapshots.id and
       main.transaction_id = snapshots.main_transaction_id
  left join costs_audit_table costs
    on costs.id = snapshots.id and
       costs.transaction_id = snapshots.costs_transaction_id

Answer 1

(CTE) - SQL, , . , CTE , . Graeme Birchall DB2 SQL Cookbook ( ) SQL. Joe Celko - , SQL .