Click here to Skip to main content
15,844,364 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

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 .
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
 

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