Wondering if someone might know the answer please to my following problem:
I'll keep the analogy as basic as possible, but lets say we sell vouchers. The vouchers are individually numbered.
We sell the vouchers in batches, and are stored in the DB as one record per sale with a range of the vouchers sold (so they scan the first and last voucher number to get the range)
SalesID Description From To
SalesID0001 Batch of paper 100001 100015
SalesID0002 More paper 100016 100150
The vouchers come back in individually however, so we might get voucher 100011 back and is stored in another table as an individual item.
I'm trying to write a report which shows which vouchers have not come back yet and I'm struggling since the data is stored in ranges not on an individual basis... hopefully you're still with me?
My thinking is leading me to maybe create a temp table where I can convert those ranges into real numbers....which I'll be able to more easily compare - but I have not idea how I might do that.
Is someone able to help please?
(Example output from above example
SalesID Description Voucher
SalesID0001 Batch of paper 100001
SalesID0001 Batch of paper 100002
SalesID0001 Batch of paper 100003
SalesID0001 Batch of paper 100004
SalesID0001 Batch of paper 100005
Suggest considering that at report run-time you read the "ranges table" and generate temp-table entries for each number in that range using the beginning and ending number as the control for that range. Continue thru the ranges untill all have been entered into the temp-table.
Then join the temp table with the individually returned voucners and report on the "not found" entries.
Last edited by papadi; 06-14-12 at 16:54.
select t.SalesID, t.Description, (t.VoucherFrom + a.number) as Voucher
from Table1 as t
cross join master.dbo.spt_values AS a
where (a.number >= 0) and
(a.type = 'P') and
(a.number <= (t.VoucherTo - t.VoucherFrom))