Dear,
I have two tables called AgtBusiness, AgentMaster. agent master is having agent details like agentid, agentname joindate and relievedate. AgtBusiness table is having agentid, applicationNo, businessdate.
Now i have scenario like agentMaster is having same agentIds with different joindate and relievedate. like date fields represents Format dd/mm/yyyy
agentID AgentName joinDate RelieveDate
A001 XYZ 01/01/2010 15/01/2010
A001 ABC 16/01/2010 04/02/2010
A001 MNO 05/02/2010 NULL
AgtBusiness AgentID Businessdate
A001 02/01/2010
A001 04/01/2010
A001 17/01/2010
A001 07/02/2010
we have to use same agentcode in agentmaster and startdate and enddate is different for each agents.
Agent XYZ is having 2 businesses ,ABC is having 1 business, MNO is having 1 business. Now i want to map the two tables based on agentid and startdate and enddate.
now i have written query like this.
select a.* from agtBusiness a left join agentMaster b on a.agentid=b.agentid and a.businessdate between b.joindate and b.relievedate.
But i think this is not correct. are there any other ways? Can anybody help on this to map businesses to the respective agents based on startdate and enddate.
we need all business so i used left join.
Thanx in advance.
