Get a list of records based on start date and end date by giving monthid as parameter

[ssreevidya.m]

Hai ,
I have 2 tables
1. Transport_Details having columns
user_id
FareStageId
StartDate
EndDate

and
2. Route_Fee having columns
FareStageId
monthId
fees
I need to get a monthwise list by passing monthid.
My problem is that , if start date and enddate of a user are 02-08-2011 and
20-02-2012 and I need to get the list of 4th month , but this user also listed.
Query I used is:

select Transport_Details.User_Id, Route_Fee.Fee, from Transport_Details  join  Route_Fee 
on Transport_Details.Bus_Route_Fare_Stage_Id=Route_Fee.Bus_Route_Fare_Stage_Id where Route_Fee.Month=4

Can i get the list of those users , the monthid between startdate and enddate.
Thanks in advance.

[MeSampath]

Hi,

Are you expecting months list between two dates?

Hai , I have 2 tables 1. Transport_Details having columns user_id FareStageId StartDate EndDate and 2. Route_Fee having columns FareStageId monthId fees I need to get a monthwise list by passing monthid. My problem is that , if start date and enddate of a user are 02-08-2011 and 20-02-2012 and I need to get the list of 4th month , but this user also listed. Query I used is:

select Transport_Details.User_Id, Route_Fee.Fee, from Transport_Details  join  Route_Fee 
on Transport_Details.Bus_Route_Fare_Stage_Id=Route_Fee.Bus_Route_Fare_Stage_Id where Route_Fee.Month=4

Can i get the list of those users , the monthid between startdate and enddate. Thanks in advance.

[adam_k]

I didn't understand the question either. Can you provide sample data and the expected results?

[ssreevidya.m]

Hai ,
the sample datea like :

if Transport_Details table contains following data,

user_id FareStageId StartDate EndDate

sam fs1 01- 06-2011 30- 02-2012
ann fs4 01- 10-2011 30- 02-2012
raj fs2 01- 06-2011 30- 02-2012

and I need a result based on a particular month that between start date and enddate
if the monthid is 8 , my expected result is:

user_id Fee
sam 200
roni 250

But I got 3 records.

[urtrivedi]

Post following 3 things here
1) sample data (you have posted 30-feb-2012 date above, roni is not in your sample data, how u expect roni)
2) sample data of Route_Fee table also is needed (at least give data of fs1, fs2 and fs4)
3) paramter u want to pass (say monthid=5 or u want to pass from date to date range)
4) sample result you want from 1 and 3 above

[urtrivedi]

check whether following query works for you or not

select Transport_Details.User_Id, Route_Fee.Fee 
from Transport_Details  join  Route_Fee 
on Transport_Details.bus_route_Fare_Stage_Id=Route_Fee.bus_route_Fare_Stage_Id 

where 

(   ( year(startDate)*100+month 
               between 
               year(startDate)*100 + month(startdate) and year(enddate)*100 + month(enddate)
   )
or  (year(endDate)*100+month 
               between 
               year(startDate)*100 + month(startdate) and year(enddate)*100 + month(enddate)
  )
)
and Route_Fee.Month=4