get due amount of of all invoices of given supplier

  • hi

    i have 2 table...

    first...

    tbl_Purchase

    Purchase_idbigint

    Location_Idbigint

    PU_Refnvarchar(50)

    InvoiceDatedatetime

    Original_Amtdecimal(18, 2)

    Supplier_IDbigint

    Statusint

    and second tbl_pur_Child

    s_Nbigint

    Purchase_IDbigint

    Paid_amtdecimal(18, 2)

    Location_Idbigint

    InvoiceDatedatetime

    i want when i pass supplier id

    in store procedure

    so

    it return

    PU_Ref, Original_Amt, ( Original_Amt-sum(Paid_amt) of particule supplier id...

    1 supplier may can have more then 1 invoices....

    i write this store procedure...

    ALTER procedure [dbo].[SP_Chk_Purchase_Bal_By_SupID]

    @supid int

    as begin

    select PU_Ref ,Original_Amt, (Original_Amt-

    (select sum(rf.Paid_amt) from tbl_pur_Child as rf join

    tbl_Purchase as pu on

    rf.Purchase_ID=pu.Purchase_id ))

    from tbl_Purchase where Supplier_ID=@supid

    end

    but it not giving right result...............

  • Read the article in the first link in my signature line.

    To give a sensible answer we need:

    1) Table script

    2) Sample data

    3) Expected results based on sample data

    4) What you have tried so far

    1) and 4) are almost there, we just need 2) and 3).

    Thanks

    Gianluca

    -- Gianluca Sartori

  • My First Table

    tbl_Purchase

    Purchase_id Location_Id PU_Ref InvoiceDate Original_Amt Supplier_ID Status

    1 0 air11 2011-09-23 16:09:49.140 456.00 18 1

    2 0 air12 2011-09-23 16:17:28.540 999.90 18 1

    3 0 air13 2011-09-23 16:17:28.540 120.00 18 0

    Second Table

    tbl_pur_Child

    s_N Purchase_id Paid_amt Location_Id InvoiceDate

    11 200.0002011-09-23 16:09:49.140

    22 100.8002011-09-23 16:17:28.540

    31 120.7602011-09-23 16:47:28.000

    42 230.5402011-09-23 16:55:28.000

    53 120.0002011-09-23 16:55:28.000

    I want output

    PU_Ref Original_Amt Due amount

    air11 456.00 135.24

    air12 999.90 668.56

    how can i write store procedue for this ...

  • Since this is your first post, let me try and give you an example of what we really need to help you, just like Gianluca suggested

    i ran this thru my example scrubber to get what i could out of it so far...no suggestionsas to the fixes that might be invloved, as this was a bit of effort all by itself.,

    if you can give us data like this in the future, we can offer tested, complete solutions to whatever you post.

    CREATE TABLE [dbo].[tbl_Purchase] (

    [Purchase_id] bigint NULL,

    [Location_Id] bigint NULL,

    [PU_Ref] nvarchar(100) NULL,

    [InvoiceDate] datetime NULL,

    [Original_Amt] decimal(18,2) NULL,

    [Supplier_ID] bigint NULL,

    [Status] int NULL)

    CREATE TABLE [dbo].[tbl_pur_Child] (

    [s_N] bigint NULL,

    [Purchase_ID] bigint NULL,

    [Paid_amt] decimal(18,2) NULL,

    [Location_Id] bigint NULL,

    [InvoiceDate] datetime NULL)

    --sample data to proof the results?

    INSERT INTO...

    ALTER PROCEDURE [dbo].[SP_CHK_PURCHASE_BAL_BY_SUPID] @supid INT

    AS

    BEGIN

    SELECT

    PU_Ref,

    Original_Amt,

    ( Original_Amt - (SELECT

    SUM(rf.Paid_amt)

    FROM

    tbl_pur_Child AS rf

    JOIN tbl_Purchase AS pu

    ON rf.Purchase_ID = pu.Purchase_id) )

    FROM

    tbl_Purchase

    WHERE

    Supplier_ID = @supid

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • i sent sample data.... Plse Check this..

    Thnks

  • dilip.aim11 (9/23/2011)


    i sent sample data.... Plse Check this..

    Thnks

    that's not sample data....

    what we need is "consumable" data...copy and paste into SSMS, run it, so we have a setup that mimicks the problem you are trying to resolve.

    If you don't do that, posters who would be willing to help move on to the next post, as cleaning your data, making assumptions about it and more are not worth the time it takes to many posters.

    sample data is like this:

    INSERT INTO SomeTable(ColumnList)

    SELECT SomeData UNION ALL

    SELECT MoreData Data ---...? just a few rows to test agaisnt

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • fully consumable, copy and paste example:

    CREATE TABLE [dbo].[tbl_Purchase] (

    [Purchase_id] bigint NULL,

    [Location_Id] bigint NULL,

    [PU_Ref] nvarchar(100) NULL,

    [InvoiceDate] datetime NULL,

    [Original_Amt] decimal(18,2) NULL,

    [Supplier_ID] bigint NULL,

    [Status] int NULL)

    CREATE TABLE [dbo].[tbl_pur_Child] (

    [s_N] bigint NULL,

    [Purchase_ID] bigint NULL,

    [Paid_amt] decimal(18,2) NULL,

    [Location_Id] bigint NULL,

    [InvoiceDate] datetime NULL)

    --sample data to proof the results?

    INSERT INTO [tbl_Purchase]

    SELECT '1','0','air11','2011-09-23 16:09:49.140','456.00','18','1' UNION ALL

    SELECT '2','0','air12','2011-09-23 16:17:28.540','999.90','18','1' UNION ALL

    SELECT '3','0','air13','2011-09-23 16:17:28.540','120.00','18','0'

    INSERT INTO tbl_pur_Child

    SELECT '1','1','200.00','0','2011-09-23 16:09:49.140' UNION ALL

    SELECT '2','2','100.80','0','2011-09-23 16:17:28.540' UNION ALL

    SELECT '3','1','120.76','0','2011-09-23 16:47:28.000' UNION ALL

    SELECT '4','2','230.54','0','2011-09-23 16:55:28.000' UNION ALL

    SELECT '5','3','120.00','0','2011-09-23 16:55:28.000'

    GO

    CREATE PROCEDURE [dbo].[SP_CHK_PURCHASE_BAL_BY_SUPID] @supid INT

    AS

    BEGIN

    SELECT

    PU_Ref,

    Original_Amt,

    ( Original_Amt - (SELECT

    SUM(rf.Paid_amt)

    FROM

    tbl_pur_Child AS rf

    JOIN tbl_Purchase AS pu

    ON rf.Purchase_ID = pu.Purchase_id) )

    FROM

    tbl_Purchase

    WHERE

    Supplier_ID = @supid

    END

    GO

    EXECUTE [SP_CHK_PURCHASE_BAL_BY_SUPID] 18

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ok .. next Time i will remember about rules..

    And Thanks for valuable example...

Viewing 8 posts - 1 through 7 (of 7 total)

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