Click here to Skip to main content
15,849,494 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: (untagged)
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.
But 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 , this user also listed.
Query I used is:


SQL
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.
Posted
Updated 7-Nov-11 18:04pm
v2

To get the list of those users , the monthid between startdate and enddate, you can try something like:
SQL
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 between month(StartDate) and month(EndDate)

not sure whether this will come - Route_Fee.Month=4 
 
Share this answer
 
In where condition check dates also as below


SQL
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
and Transport_Details.StartDate between 'your startdate' and 'Your end date'
and Transport_Details.EndDate between 'your startdate' and 'Your end date'
 
Share this answer
 
Comments
sreevidya.m 9-Nov-11 2:28am    
Hai ,
I need to get the list of all users the specified month exists between their start date an dend date. then how can i specify 'your startdate' and 'Your end date' . can i use cursor for it .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900