Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2003-02-11 : 09:43:25
|
| I have a table called Course_Cost which gives a rate for each day of the year. The rates are typically higher on the weekends compared to the weekdays. I want to show the Monday - Thursday rates in a range and the Friday - Sunday rates in a range (this rate pattern may change from time to time). Ideally, when the rate changes, a new row would be added showing the range of dates that this rate is good thru. So the output can look something like this:Course StartDate EndDate Cost CourseA 2/10/2003 2/13/2003 50.00CourseA 2/14/2003 2/16/2003 60.00CourseA 2/17/2003 2/20/2003 50.00CourseA 2/21/2003 2/23/2003 60.00etc..What I am currently seeing is this:Course StartDate EndDate Cost CourseA 2/10/2003 4/24/2003 50.00CourseA 2/12/2003 4/27/2003 60.00CourseA 4/28/2003 5/26/2003 80.00etc...Notice the overlap of dates.Here is the query that isn't working the way I want...Select A.CourseName [Course], Min(C.Rate_Date) [StartDate], Max(C.Rate_Date) [EndDate], C.Net_Cost [Cost]From Course A with (NOLOCK) inner join Course_Cost C with (NOLOCK) on A.CourseID = C.CourseIDWhere Rate_Date > Left(getdate(),11)Group by A.CourseName, C.Net_CostOrder by A.CourseName, Min(C.Rate_Date)I know it has to do with the MAX and the MIN statements. I don't know how else to do this. Any ideas?MBeal |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-02-11 : 11:10:15
|
| Can you supply the underlying table structures? |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2003-02-11 : 12:11:38
|
| I have decided to go about this a different way. I appreciate you taking the time and your willingness to investigate further -- but I think I have found a work around that will be suitable.Thanks again.MBeal |
|
|
|
|
|
|
Ordering Range of Dates