通过将monthid作为参数获取基于开始日期和结束日期的列表 [英] Get a list based on start date and end date by giving monthid as parameter

问题描述

海，
我有2张桌子
1.具有列的Transport_Details
user_id
FareStageId
StartDate
EndDate

和
2. Route_Fee包含列
FareStageId
monthId
费用
我需要通过传递monthid来获得按月列表.
但是我的问题是，如果用户的开始日期和结束日期是02-08-2011和
2012年2月20日，我需要获取第4个月的列表，该用户也已列出.
我使用的查询是:

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:

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.

推荐答案

在其中条件检查日期也如下所示

In where condition check dates also as below

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'

要获取这些用户的列表(开始日期和结束日期之间的月号)，您可以尝试以下操作:

To get the list of those users , the monthid between startdate and enddate, you can try something like:

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 

这篇关于通过将monthid作为参数获取基于开始日期和结束日期的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！