Member Avatar for 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.

Edited by ssreevidya.m because: n/a
  • 4 Contributors
  • 5 Replies
  • 240 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

The thread has two separate interpretations of "monthId" and a couple of inconsistencies in the sample data (a non-existent date like 30-02-2012 and an expected row for "roni" that is not shown). Clarifying which meaning is intended will change the SQL:

  • monthId as an ordinal (the Nth billing month after StartDate), or
  • monthId as a calendar month number (1..12).

If monthId is the Nth month since StartDate (the common design for per-month fees), an easy, correct test is whether the start of that Nth month falls on or before EndDate. This avoids iterating months and works with DATEADD behavior for month-end rollovers. Example:

DECLARE @MonthId INT = 4;

SELECT td.User_Id, rf.Fees
FROM Transport_Details td
INNER JOIN Route_Fee rf
  ON td.FareStageId = rf.FareStageId
WHERE rf.MonthId = @MonthId
  AND DATEADD(month, @MonthId - 1, td.StartDate) <= td.EndDate;

To require the entire Nth month to be covered by the range (not just any overlap), replace the last condition with:

AND DATEADD(month, @MonthId, td.StartDate) <= td.EndDate

If monthId is a calendar month number, the logic must check whether any month between StartDate and EndDate has that month number. A common approach is to expand the months between StartDate and EndDate (using a numbers/tally source) and test MONTH(DATEADD(month,n,StartDate)) = @MonthId — this is more work but straightforward. Performance improves greatly if date columns are indexed and a persisted numbers table is used instead of ad-hoc generation.

Notes and troubleshooting (refs: , , , ): fix invalid sample dates (e.g., use 2012-02-29 instead of 30-02-2012), make sure MonthId semantics are explicit, test the computed DATEADD(...) values for a few rows to confirm expected behavior, and add an index on FareStageId and on StartDate/EndDate if the dataset is large.

Recommended Answers

All 5 Replies

Member Avatar for 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.

Member Avatar for adam_k

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

Member Avatar for 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.

Edited by ssreevidya.m because: n/a
Member Avatar for 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

Edited by urtrivedi because: n/a
Member Avatar for 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
Edited by urtrivedi because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.