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 : 2198 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

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

0	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

0	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

Latest Posts