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

Question

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 4-Nov-11 1:37am

sreevidya.m

Updated 7-Nov-11 17:04pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Om Prakash Pant · Answer 1 · 2011-11-04T01:43:00

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

P.Salini · Answer 2 · 2011-11-04T01:43:00

Solution 1

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'

Posted 4-Nov-11 1:43am

P.Salini

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 .