人们按时支付账单，并非所有人按时支付账单，支付费用的人比尔我有记录，但我如何获得那些没有支付给那里的人的记录... [英] People Pay There Bill On Time ,Not All People Pay There Bill On Time,People Who Pay There Bill I Have There Record But How I Wil Get The Record Of People Who Didn't Pay There Bil For Current...

问题描述

这是表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的所有数据，即使用户已支付当月的账单，



如何提供过滤器来搜索那些没有支付账单的人对于当前账单并使用上述查询插入数据。

推荐答案

以下将回答您提出的问题...

The following will answer your question as you have asked it...

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"

这篇关于人们按时支付账单，并非所有人按时支付账单，支付费用的人比尔我有记录，但我如何获得那些没有支付给那里的人的记录...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！