Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012

    Unanswered: create values from ranges


    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
    etc, etc)


  2. #2
    Join Date
    Oct 2009
    221B Baker St.
    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 15:54. Reason: clarification

  3. #3
    Join Date
    Apr 2012

    Try something like that:

    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))
    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts