Any idea how to approach this , maybe i need script or cursor ?
I have data from joining results and registration tables that looks like this:
CompanyID, CandidateID, ExamID, #Attempts, #Vouchers
In the data each candidate will have multiple different exams. Each exam allow 1 free attempt and then they get one more free attempt per voucher.
#Vouchers is the total they have, so the number repeats across all rows for the candidate. The voucher can be used to buy a re-sit for any exam
I need to analyse Q1 data and produce
Number of chargeable exams per company
Detail by Candidate
Some data to store so that when I run in Q2, I know who has used free attempts etc.
Just trying to get my head around the approach to use rather than needing SQL help. It seems more of a spreadsheet formulae problem , working down row by row, checking and updating the number of free passes and seeing how many attempts are chargeable each row, but I need to keep a counter per candidate which can't do in a spreadsheet