我们如何检查发票和收据的数量在SQL中是相同的 [英] How we check the amount of invoices and receipt is equal in SQL

问题描述

我正在使用SQL Server 2008并从excel导入数据，使用 Imoprt和导出数据



有两个表发票

 COLUMN_NAME DATA_TYPE 
 Invoice_Id  varchar  
 ItemName  varchar  
数量 bigint  
金额钱 
 Date_Time  datetime  

和第二个表是RECEIPT

 COLUMN_NAME DATA_TYPE 
 Receipt_Id  varchar  
 Invoice_Ref  varchar  
金额 money  
 Date_Time  datetime  

和INVOICES表Invoice_Id有主键，在RECEIPT表中Invoice_Ref有外键来自INVOICE表的参考资料。



现在假设INVOICE001必须在INVOICES表中支付20000，但他在RECEIPT表中支付第一张收据10000，然后支付第二张收据10000.



如何通过SQL检查金额20000 = 10000 + 10000.

意味着我需要使用什么，在创建RECEIPT表时我们可以为此设置任何约束吗？ />
请帮帮我



我尝试过：



我想在从excel导入到SQL服务器的过程中尝试，我们如何确保INVOICES的金额和RECEIPT表金额（Receipt1 + Receipt2）相等或者可能小于但不大于因为他可以支付还有一个收据。请给我解决方案，我将责备你

解决方案

为INSERT创建一个INSTEAD OF TRIGGER，如下所示

< pre lang =SQL> CREATE TRIGGER MyTrigger ON db o.RECEIPT
INSTEAD OF INSERT
AS
BEGIN
DECLARE @ InvoiceId AS VARCHAR （ 100 ） - 替换为实际尺寸
DECLARE @ Amount AS MONEY
SELECT @ InvoiceId = I.Invoice_Ref， @ Amount = I.Amount 来自 INSERTED I;

IF （（ SELECT 金额 FROM 发​​票 WHERE Invoice_Id = @ InvoiceId）> =
（ SELECT SUM（金额） FROM RECEIPT WHERE Invoice_Ref = @ InvoideId）+ @ Amount）
BEGIN
RAISERROR （' 由于amt验证无法插入'， 16 ， 1 ）;
END
ELSE
BEGIN
INSERT INTO RECEIPT（Invoice_Ref，Amount，Date_Time）
VALUES （ @ InvoiceId ， @ Amount ，GETDATE（））
END
END

注意：这只是一种方法，您需要根据您的要求实施逻辑和更改。此外，我还没有在SSMS中执行查询，因此无法保证拼写错误/语法错误。



希望，它有所帮助。如有任何疑问，请告知我们：）

暂停并重新考虑您的方法。

您真的需要发票表中的金额列吗？ ？如果可以通过使用SQL动态总结收据表中的收据金额来导出发票金额，例如

  SELECT  SUM（receipt.amount） FROM 发​​票 JOIN 收据 ON  invoice_id = invoice_ref  WHERE  invoice_id = SOMEVALUE 

即使在Excel电子表格中也是如此，您应该使用公式和条件从相关收据金额中获取最新发票金额。

这样，无需担心计算发票金额和相关收据量。

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

there are two tables INVOICES

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

and second table is RECEIPT

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

解决方案

Create an INSTEAD OF TRIGGER for INSERT something like following

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 :)

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.

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.

这篇关于我们如何检查发票和收据的数量在SQL中是相同的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！