问题描述
这是表tblinvoice，我每个月创建。
this is table tblinvoice, which i create every month.
InvoiceID SocietyID Date MemberID MonthlyCharges CidcoCharges TotalCharges
1 1 2014-04-30 1 650 50 700
2 1 2014-04-30 2 721.5 50 771.5
3 1 2014-04-30 3 780 50 830
4 1 2014-04-30 4 663 50 713
5 1 2014-04-30 7 650 50 700
6 1 2014-04-30 8 910 50 960
7 2 2014-04-30 5 915 70 985
8 2 2014-04-30 6 1050 70 1120
9 2 2014-04-30 9 675 70 745
10 2 2014-04-30 10 1125 70 1195
这是表tblbill它记录了那些已支付当月账单的人。
this is table tblbill it keeps record of people who has paid there current month bill.
BillID SocietyID MemberID InvoiceID BillDate PaymentMode BankName ChequeNo ChequeDate PreviousDue Bill Amount AmountRemaining LatePayPenalty
1 1 1 NULL 2014-04-20 NULL NULL NULL NULL NULL NULL 700 0 60
2 1 3 NULL 2014-04-20 NULL NULL NULL NULL NULL NULL 500 330 60
3 1 8 NULL 2014-04-21 NULL NULL NULL NULL NULL NULL 1000 -40 60
4 2 5 NULL 2014-04-22 NULL NULL NULL NULL NULL NULL 985 0 60
我的问题来自这里，因为有些人按时支付账单，并非所有人按时支付账单。 />
人们如何支付那里的账单我有记录，但我将如何得到那些没有在当月支付账单的人的记录。
i有一个查询，可以根据tblinvoice中的数据为我插入值，但它还包括已经支付了账单的人，这是我的查询
my problem arise from here, because some people pay there bill on time ,not all people pay there bill on time.
the people how pay there bill i have there record but how i will get the records of people who didn't pay there bill for current month.
i have a query which can insert values for me according to data in tblinvoice but it also includes people who has already paid there bill, here's my query
Declare @LatePayCharge int
set @LatePayCharge= 60
insert into tblbill (SocietyID,MemberID,BillDate,AmountRemaining,LatePayPenalty)
select SocietyID,MemberID,CAST(GETDATE() AS DATE),TotalCharges,@LatePayCharge from tblinvoice
当我使用此查询时它插入来自tblinvoice的所有数据，即使用户已支付当月的账单，
如何提供过滤器来搜索那些没有支付账单的人对于当前账单并使用上述查询插入数据。
推荐答案
Declare @LatePayCharge int
set @LatePayCharge= 60
insert into tblbill (SocietyID,MemberID,BillDate,AmountRemaining,LatePayPenalty)
select I.SocietyID,I.MemberID,CAST(GETDATE() AS DATE),I.TotalCharges,@LatePayCharge
from tblinvoice I
left outer join tblbill B on B.InvoiceID=I.InvoiceID
where B.InvoiceID is null
（向Aijith K Gatty致谢，他们在评论中说了类似内容）
请注意，我使用了表别名来指定两个表中具有相同名称的列。
解决了您提出的问题后，您应该知道您的设计存在一些基本问题。 />
您说tblbill会保留已经支付账单的人的记录
换句话说，这是您记录收款或钱在里面
然后你开始添加记录，代表没收到的钱！
你怎么知道要查询哪个表找出谁欠什么？它在两者中。
只要查看你的tblinvoice也不会告诉你哪些发票付款，你还要看看tblbill - 效率不高。
考虑在tblinvoice中添加一个列，表明它是否已付款 - 这可能是状态或付费标志或支付日期。您可能还需要考虑部分付款的发票或未付金额。你显然已经考虑过你在tblbill中的列了。
事实上，你的tblbill中的一些列可能应该在tblinvoice上 - 他们主要是原始发票的属性。例如，如果您移动 AmountRemaining ，那么您自动获得发票是否已全额支付的指标。
还要考虑你的术语 - 在许多情况下发票与账单相同
你可能最好将后者重命名为tblReceipts
(with acknowledgement to Aijith K Gatty who said something similar in their comment)
Note that I have used the table alias to specifiy the columns that have the same name across both tables.
Having solved the problem you asked, you should know that you have some fundamental issues with your design.
You stated that tblbill would keep a records of people who have paid their bills
In other words this is your table to record receipt of money or "monies in"
But then you start adding records to it which represent money not received!
How will you know which table to query to find out who owes what? It's in both.
Just looking at your tblinvoice will also not tell you which invoices are paid, you have to also look at tblbill - not efficient.
Consider adding a column to tblinvoice that indicates whether or not it is paid - this could be a "status" or a "paid flag" or a "date paid". You may need to also consider "partially paid" invoices or "amount outstanding". You've obviously thought about this judging by the columns you have in your tblbill.
In fact, some of the columns in your tblbill probably should be on tblinvoice - they are mostly "attributes" of the original invoice. If you moved AmountRemaining for example, then you "automatically" get an indicator of whether or not the invoice is paid in full.
Also consider your terminology - in many contexts "invoice" is the same as "a bill"
You might be better off renaming the latter to "tblReceipts"
这篇关于人们按时支付账单，并非所有人按时支付账单，支付费用的人比尔我有记录，但我如何获得那些没有支付给那里的人的记录...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！