September 23, 2011 at 6:32 am
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...............
September 23, 2011 at 7:28 am
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
September 23, 2011 at 7:33 am
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 ...
September 23, 2011 at 7:36 am
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
September 23, 2011 at 7:39 am
i sent sample data.... Plse Check this..
Thnks
September 23, 2011 at 7:43 am
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
September 23, 2011 at 7:50 am
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
September 23, 2011 at 8:05 am
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