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...

Question

1.00/5 (2 votes)

See more:

, +

this is table tblinvoice, which i create every month.

SQL

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

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

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

SQL

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

when i use this query it insert all data from tblinvoice even if user has paid there bill for current month,

how i can provide a filter to search people who didnt pay there bill for current bill and insert there data using above query.

Posted 29-Apr-14 23:34pm

Tarun Jaiswal

Add a Solution

Comments

Thanks7872 30-Apr-14 5:38am

Have you gone through title of the question? Read it twice and decide is it right way to post questions like this or not. Title is meant to provide overview about your question. This question seems like spam or abuse at first sight, i repeat at first sight.

thatraja 30-Apr-14 5:43am

Agree

Tarun Jaiswal 30-Apr-14 10:34am

thx for your consideration.
i future i will take care of such things.

Ajith K Gatty 30-Apr-14 5:48am

Hi,
i think you can implement set difference in SQL.
If you would like to represent Set Difference (A-B) in SQL, here is solution for you. Let's say you have two tables A and B, and you want to retrieve all records that exist only in A but not in B, where A and B have a relationship via an attribute named ID. An efficient query for this is:
SELECT DISTINCT A.* FROM (A LEFT OUTER JOIN B on A.ID=B.ID) WHERE B.ID IS NULL

Tarun Jaiswal 30-Apr-14 10:37am

left outer join really help me
thank you

Ajith K Gatty 2-May-14 5:46am

Ohh!! That's really cool. :)

lukeer 30-Apr-14 5:51am

1) "There" means some location. Your title means possession, in plural form, which would be expressed by "their".
2) The title is far too long.
3) Use proper capitalization. All lowercase is considered childish, all uppercase is considered shouting (and therefore rude).

[no name] 30-Apr-14 6:55am

put one column IsBillPaid or not filter data with this column true or false

1 solution

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)

CHill60 · Accepted Answer · 2014-04-30T02:31:00

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

SQL

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

(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"