.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Query for Payable Invoice_Number aganist for Customer Payment

Posted By: Narayanan     Posted Date: December 16, 2012    Points:5   Category :Sql Server
Hi,
I am using two Tables in Project.One is Invoice_Master and another one is Customer_Payment.
This is my Table structure:
Invoice_Master :
Invoice_Number
Invoice_date
Customer_Id
TotalAmount

Customer_Payment
Entry_No
Entry_Date
Customer_Id
TotalAmount

Information (like)
Invoice_Master
Invoice_Number Invoice_date Customer_Id TotalAmount
IN001 15/12/2012 CUST01 100000
IN002 15/12/2012 CUST01 30000

Entry_No Entry_Date Customer_Id TotalAmount
EN001 17/12/2012 CUST01 90000


Result:
IN001
IN002
Store Procedure Parameter : StartingDate and Ending Date or Customer Id.
So, how to get this result using Store Procedure?Please help me.


Responses
Author: Sasi Prabhu             
Posted Date: December 27, 2012     Points: 20   

Use CustomerId as Parameter.
and also use another column in Customer_Payment which is used to store whether payment is fully completed.

Use SP like
select Invoice_Number from Invoice_Master a
join Customer_Payment b on a.Customer_Id =b.Customer_Id
where b.Customer_Id =@CustomerId
and IsPaymentCompleted='N'



Thanks & Regards,
Sasi Prabhu.
Author: Petr Matejovsky             
Posted Date: December 30, 2012     Points: 20   

Hi,

table structure is not optimal. Lack of relationship between Invoice and Customer.

select * from Invoice

number invoice_date customer_id totalamount
---------- ------------ ----------- ----------------------
in001 2012-12-15 cust01 100000
in002 2012-12-15 cust01 3000

in003 2012-12-12 cust02 1000
in004 2012-12-29 cust03 500
in005 2012-12-29 cust03 500

select * from Customer

entry_no entry_date customer_id totalamount
---------- ---------- ----------- ----------------------
en001 2012-12-17 cust01 90000
en002 2012-12-17 cust02 500
en003 2012-12-17 cust02 500
en004 2012-12-29 cust03 500
en005 2012-12-29 cust03 600


declare @StartDate date
declare @EndDate date

set @StartDate = '2012-12-01'
set @EndDate = '2012-12-31'

select m.number from Invoice m where (select SUM(i.totalamount) from Invoice i where i.customer_id = m.customer_id and i.invoice_date >= @StartDate and i.invoice_date <= @EndDate ) <> (select SUM(c.totalamount) from Customer c where c.customer_id = m.customer_id and c.entry_date >= @StartDate and c.entry_date <= @EndDate )



return this:

number
----------
in001
in002

in004
in005


problem:

cust03 - payment is fully completed - row in004 and row en004
cust03 - payment is fully complete - row in005 and row en005 - overpayment +100


Petr




Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend