How we check the amount of invoices and receipt is equal in SQL

Question

5.00/5 (1 vote)

See more:

Hi, I am using SQL server 2008 and importing data from excel,by using Imoprt and Export Data

there are two tables INVOICES

SQL

COLUMN_NAME	DATA_TYPE
Invoice_Id	varchar
ItemName	varchar
Quantity	bigint
Amount	money
Date_Time	datetime

and second table is RECEIPT

SQL

COLUMN_NAME	DATA_TYPE
Receipt_Id	varchar
Invoice_Ref	varchar
Amount	money
Date_Time	datetime

and in INVOICES table Invoice_Id has primary key and in RECEIPT table Invoice_Ref has foreign key references from INVOICE table.

now suppose INVOICE001 have to pay 20000 in INVOICES table but he pay first receipt 10000 and then second receipt 10000 in RECEIPT table.

how we can check amount 20000 = 10000+10000 by SQL.
Means what I need to use, during creating RECEIPT table can we put any constraint for that?
please help me

What I have tried:

I want to try during import from excel to SQL server,how we can ensure that amount of INVOICES and Amount of RECEIPT table amount of(Receipt1 + Receipt2) is equal or may be less than but not greater than because he can pay one more receipt.please give me solution,i will be obliged you

Posted 7-Jul-16 1:46am

Member 10192073

Updated 7-Jul-16 4:59am

Add a Solution

2 solutions

Solution 2

Pause and re-think your approach.
Do you really need the amount column in the invoices table? Why store the invoice amount if it can be derived by summing up the receipt amounts in the receipt table dynamically using SQL, e.g.

SQL

SELECT SUM(receipt.amount) FROM invoices JOIN receipt ON invoice_id=invoice_ref WHERE invoice_id=SOMEVALUE

Even in the excel spreadsheets, you should use formula and condition to get the up-to-date invoice amount from the related receipt amounts.
In this way, there is no need to worry about tallying the invoice amount and the related receipt amounts.

Posted 7-Jul-16 4:59am

Peter Leow

Updated 7-Jul-16 5:08am

v6

Comments

Maciej Los 7-Jul-16 11:09am

Good point, a 5!

Peter Leow 7-Jul-16 11:10am

Thank you, Maciej.

Suvendu Shekhar Giri 8-Jul-16 2:15am

true. good point unless OP has some special/weird logic for it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Accepted Answer · 2016-07-07T04:38:00

Create an INSTEAD OF TRIGGER for INSERT something like following

SQL

CREATE TRIGGER MyTrigger ON dbo.RECEIPT
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @InvoiceId	AS VARCHAR(100) --replace with actual size
  DECLARE @Amount AS MONEY
  SELECT @InvoiceId = I.Invoice_Ref,@Amount = I.Amount from INSERTED I;

  IF((SELECT Amount FROM INVOICES WHERE Invoice_Id=@InvoiceId)>= 
         (SELECT SUM(Amount) FROM RECEIPT WHERE Invoice_Ref=@InvoideId)+@Amount)
  BEGIN
      RAISERROR('Cannot insert due to amt validation',16,1);
  END
  ELSE
  BEGIN
      INSERT INTO RECEIPT (Invoice_Ref, Amount, Date_Time)
      VALUES (@InvoiceId, @Amount, GETDATE())
  END
END

Note: This is just an approach, you need to implement the logic and changes as per your requirement. Also I haven't executed the query in SSMS so can't gurantee about typos/syntax errors.

Hope, it helps. In case of any query, please let me know :)