how to write query[for missing no]

sql server India
  • 13 years ago

    my self avi developing application in vb6.0 and sql server 2000 which is running on lan. i have a table say ReceiptbookMaster having fields RBookNo,StartReceNo,EndRecNo.

    RBookNo      startreceno             Endrecno

    a5555               1                             25

    i.e. there is receiptbook of no a5555 and it has receipt from 1 to 25. now i entered each receipts entry in receipttable. i.e. it has Rbookno,receiptno,name,amount,Rdate. suppose there are records in receipttable like

    Rbookno   receiptno    name    amount   rdate

    a5555         1            avi              5000   1/1/05

    a5555        4              xyz            100      2/1/05

    a5555          7         pqr                250         5/5/05

    now i want to find the balance no and missing no of receiptbookno a5555.

    how to write query for this         

    now i want to

  • 13 years ago

     declare @rBookNo as varchar(10)
    set @rBookNo = 'a5555'



    select
        RBM.RBookNo,  
        RBM.EndRecNo - (RBM.StartRecNo - 1) - IsNull(RT.ReceiptCount, 0) as Balance
    from ReceiptBookMaster RBM
    left outer join    
    (
        select RBookNo, count(ReceiptNo) 'ReceiptCount'
        from ReceiptTable
        group by RBookNo
    )
    RT on (RBM.RBookNo = RT.RBookNo)
    where (RBM.RBookNo = @rBookNo)


    declare @ReceiptNo as int
    declare @recNoEnd as int
    declare @ReceiptNoSequence as table(RBookNo nvarchar(10), ReceiptNo int)

    select
        @ReceiptNo = StartRecNo,
        @recNoEnd = EndRecNo
    from ReceiptBookMaster
    where (RBookNo = @rBookNo)

    while (@ReceiptNo <= @recNoEnd)
    begin
        insert into @ReceiptNoSequence(RBookNo, ReceiptNo) values (@rBookNo, @ReceiptNo)
        set @ReceiptNo = @ReceiptNo + 1
    end

    select RNS.RBookNo, RNS.ReceiptNo 'UnusedReceiptNo'
    from @ReceiptNoSequence RNS
    left outer join ReceiptTable RT on (RNS.RBookNo = RT.RBookNo) and (RNS.ReceiptNo = RT.ReceiptNo)
    where (RNS.RBookNo = @rBookNo)
    and (RT.ReceiptNo is null)

     

    You can optimise the sequence code:

    http://msdn2.microsoft.com/en-us/library/aa175802(SQL.80).aspx

     

    Joe 

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Some people, when confronted with a problem, think "I know, I’ll use regular expressions." Now they have two problems.” - Jamie Zawinski