UPDATE: I'm looking for a technique to compute data for all edge cases of my algorithm (or arbitrary algorithm for that matter).
What i tried so far is just thinking about what might be edge cases + producing some "random" data, but i don't know how can i be more sure i didn't miss something real users will be capable of messing up..
I want to check i didn't miss something important in my algorithm and i don't know how to generate test data to cover all possible situations:
The task is to report snapshots of data for every Event_Date
, but make a separate row for edits that may belong to the next Event_Date
- see Group 2) on input and output data illustration:
My algorithm:
- make a list of
event_date
s and computenext_event_date
s for them - join the results to
main_audit_table
and compute the biggesttransaction_id
for each snapshot (Groups 1-4 in my illustration) - groupped byid
,event_date
and by 2 options based on whethertransaction_date < next_event_date
is true or not - join
main_audit_table
to the results to get the other data from the sametransaction_id
- join
costs_audit_table
to the results - use the biggesttransaction_id
that is smaller thantransaction_id
from the result
My question(s):
- How can i generate test data that would cover all possible scenarios, so i know i got the algorithm right?
- Can you see any mistakes in my algorithm logic?
- Is there a better forum for this kind of questions?
My code (that needs to be tested):
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