Need a query to find out eligible customer for current month payment.

Posted by Thiru under Sql Server on 9/27/2011 | Points: 10 | Views : 1175 | Status : [Member] | Replies : 6
Hi
Can any one help me to get a query to fulfill the following requirement.

Table Name: tblcustomer
Fields are: id, name, joining_date,prdid

Table Name: tblproducts
Fields are: prdid,prdname,cost,paymode
paymode is having details about payment mode: Monthly or Quartely or Halfyearly or Yearly

Field: prdid acts as a relationship between tblcustomer and tblproducts

I need to write a select query to find out list of customers eligible for the current month payment.




Responses

Posted by: Mdjack on: 9/28/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Their is any relationship in mentioned table?.

N. MOHAMED ZACKKARIAH

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 9/28/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Mdjack,

Field name prdid is the relationship between tblcustomer and tblproduct.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mdjack on: 9/28/2011 [Member] Starter | Points: 25

Up
0
Down
hi Thiru,

Can u update Ur Table adding some fields. I am suggest to add the LastPaymentDate field is required.

N. MOHAMED ZACKKARIAH

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 9/28/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks for your reply.

Few sample data:

tblcustomer
id	name	doj	prdid

2 INSURANCE 2011-07-02 00:18:58 444
3 REALESTATE 2011-07-02 00:23:06 555
4 ISACK CLEMENT LEO 2011-07-08 17:55:53 444
5 MATHALAIKRS S 2011-07-08 18:01:51 444
6 G-PRAKASAM G 2011-07-08 18:07:26 444


tblproduct

prdid	prdnm	cost	mode

101 Business Kit 500 Y
333 Bronze 5000 Y
444 Silver Plan 2500 Q
555 Gold 5000 H
666 Electrum 10000 Y
777 Crystal 15000 Y




For your information:
Payment details are available in tblrenewals:


id prdid duedtp paiddtp instalment_no
2 444 2011-11-07 2011-09-28 10:57:52 2



field name: id is the relationship between tblrenewal and tblcustomer

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mdjack on: 9/28/2011 [Member] Starter | Points: 25

Up
0
Down
hi

Try this,

SELECT tblcustomer.CustName, tblproducts.LastPayementDate
FROM tblcustomer INNER JOIN
tblproducts ON tblcustomer.PID = tblproducts.PID
WHERE (tblproducts.LastPayementDate = GETDATE())

N. MOHAMED ZACKKARIAH

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: DotNetguy on: 9/28/2011 [Member] Starter | Points: 25

Up
0
Down
Hi, Try this

select TC.* from tblcustomer TC
INNER JOIN tblrenewals TR ON TC.id = TR AND paiddtp IS NULL AND
datepart(month,duedtp) = datepart(month,getdate())
and datepart(YY,duedtp) = datepart(yy,getdate())



Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response