Post reply

Repeating a SQL Query

  • November 23, 2009 at 1:59 am

    #137083

    I currently run this query to find missing transactions on my database

    Select outletid, tillReceiptnumber+1

    from ireceipt where outletid = 1422

    and effectiveon >= '2009-11-19 09:00:00' and effectiveon < '2009-11-19 14:00:00'

    except

    Select outletid, tillReceiptnumber

    from ireceipt

    I need to change the outletid each time to cover all stores, how would I structure this to run from all outletids (1001-1400)?

    Thanks

    Craig Henderson

  • November 23, 2009 at 2:17 am

    #1082841

    Craig, You might want to create a stored procedure to achieve this. I don't know whether I understood you correctly. Can you explain in terms of result you want?

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 23, 2009 at 2:20 am

    #1082842

    I normally get

    outletid then receiptnumber (if there is a missing receipt)

    I would like a list of all outletid's where there is missing a receipt

    Thanks

  • November 23, 2009 at 3:51 am

    #1082871

    Is tillReceiptnumber Unique troughout the table?

    Can you please post some test data? It would be very helpful.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • November 23, 2009 at 3:59 am

    #1082872

    Tillreceipt number is unique to each outletid but there will be duplicates in the table as each branch starts a tillreceipt number 1 and increases as they make trades.

    My current query finds breaks in the sequence of tillreceipt numbers and displays that as a missing tillreceipt.

    outletid run from 1001 - 1400

    tillreceipt numbers run from 1 - 30000

    not sure what sort of test/example data you require?

    Thanks

  • November 23, 2009 at 7:25 am

    #1082929

    I'm now using this which seems to be giving me the correct results

    Select outletid, tillReceiptnumber+1

    from ireceipt where outletid between 1001 and 1400

    and effectiveon >= '2009-11-19 09:00:00' and effectiveon < '2009-11-19 14:00:00'

    except

    Select outletid, tillReceiptnumber

    from ireceipt

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply