Overlapping dates logic

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Maybe I am making this too difficult; but I have the following issue:

A record contains a price for a specific Start Date and End Date. If
the user adds a new price, I need to make sure the Start Date doesn't
overlap something that already exists. Meaning the new Start Date
should not overlap the existing Start Date and End Date. Since I am
new to VS2005 I thought perhaps there was a function to look at these
dates... Your input is greatly appreciated!
 
Well let's just assume for a moment that all the existing date ranges for a
specfic price are contiguous and that the end date for a given price cannot
be earlier than the start date for that price.

The business rule will be that the start date for a new price must be later
than the end date for the most price with the latest end date.

The process to do this is simply:

Find the price that has the highest value for end date

Ensure that the new start date is later than the end date for the price
located

There are may ways of finding the the price with the highest value for end
date.

One way would be to sort the price objects by end date descending and then
the one you want is the first one.

If you were using a SQL database then to find the highest value for end
datae you would use a query something like:

select max([end date]) from prices
 
It kinda makes sense, and I am using SQL Server 2005 as the backEnd, but do
you have any coding examples, since I am new to VB/VS2005? Specifically,
you mention finding a price that has the highest value for end date. An
example would be greatly appreciated. I'm kinda VB stupid.


Stephany Young said:
Well let's just assume for a moment that all the existing date ranges for
a specfic price are contiguous and that the end date for a given price
cannot be earlier than the start date for that price.

The business rule will be that the start date for a new price must be
later than the end date for the most price with the latest end date.

The process to do this is simply:

Find the price that has the highest value for end date

Ensure that the new start date is later than the end date for the price
located

There are may ways of finding the the price with the highest value for end
date.

One way would be to sort the price objects by end date descending and then
the one you want is the first one.

If you were using a SQL database then to find the highest value for end
datae you would use a query something like:

select max([end date]) from prices


Jim said:
Maybe I am making this too difficult; but I have the following issue:

A record contains a price for a specific Start Date and End Date. If
the user adds a new price, I need to make sure the Start Date doesn't
overlap something that already exists. Meaning the new Start Date
should not overlap the existing Start Date and End Date. Since I am
new to VS2005 I thought perhaps there was a function to look at these
dates... Your input is greatly appreciated!
 
select top 1 * from prices order by [end date] desc


jim said:
It kinda makes sense, and I am using SQL Server 2005 as the backEnd, but
do you have any coding examples, since I am new to VB/VS2005?
Specifically, you mention finding a price that has the highest value for
end date. An example would be greatly appreciated. I'm kinda VB stupid.


Stephany Young said:
Well let's just assume for a moment that all the existing date ranges for
a specfic price are contiguous and that the end date for a given price
cannot be earlier than the start date for that price.

The business rule will be that the start date for a new price must be
later than the end date for the most price with the latest end date.

The process to do this is simply:

Find the price that has the highest value for end date

Ensure that the new start date is later than the end date for the price
located

There are may ways of finding the the price with the highest value for
end date.

One way would be to sort the price objects by end date descending and
then the one you want is the first one.

If you were using a SQL database then to find the highest value for end
datae you would use a query something like:

select max([end date]) from prices


Jim said:
Maybe I am making this too difficult; but I have the following issue:

A record contains a price for a specific Start Date and End Date. If
the user adds a new price, I need to make sure the Start Date doesn't
overlap something that already exists. Meaning the new Start Date
should not overlap the existing Start Date and End Date. Since I am
new to VS2005 I thought perhaps there was a function to look at these
dates... Your input is greatly appreciated!
 
Thanks Stephany. I'll give it a shot! Not sure what TOP 1 returns, but
I'll figure it out.

Stephany Young said:
select top 1 * from prices order by [end date] desc


jim said:
It kinda makes sense, and I am using SQL Server 2005 as the backEnd, but
do you have any coding examples, since I am new to VB/VS2005?
Specifically, you mention finding a price that has the highest value for
end date. An example would be greatly appreciated. I'm kinda VB stupid.


Stephany Young said:
Well let's just assume for a moment that all the existing date ranges
for a specfic price are contiguous and that the end date for a given
price cannot be earlier than the start date for that price.

The business rule will be that the start date for a new price must be
later than the end date for the most price with the latest end date.

The process to do this is simply:

Find the price that has the highest value for end date

Ensure that the new start date is later than the end date for the price
located

There are may ways of finding the the price with the highest value for
end date.

One way would be to sort the price objects by end date descending and
then the one you want is the first one.

If you were using a SQL database then to find the highest value for end
datae you would use a query something like:

select max([end date]) from prices


Maybe I am making this too difficult; but I have the following issue:

A record contains a price for a specific Start Date and End Date. If
the user adds a new price, I need to make sure the Start Date doesn't
overlap something that already exists. Meaning the new Start Date
should not overlap the existing Start Date and End Date. Since I am
new to VS2005 I thought perhaps there was a function to look at these
dates... Your input is greatly appreciated!
 
Back
Top