Overlapping Dates

[SeanMatyas]

Im hoping this can be done...I thank you in advance for your
attention...

We keep track on our database of dates that companies are working in
our state... if they go over 180 days in a calender year, they need a
license... they can be in the state multiple times I have no problem
using datediff and getting the number of days of each "work order". I
can even sum the datediffs together. However, our old database did
not reject overlapping dates by the same company. Thus there are
numerous overlaps (ex: One company is in the state from 1/15/07 to
3/23/07 and again from 1/18/07 to 4/15/07 on two different
projects).

This leads right into my question: Is there a way to disregard
overlapping work order dates when figuring out total days? Or, since
i am building a new database for this information, is there a way to
prevent a user from entering an overlapping date in the first place?
(i know thats more of a form question, i think)

Thank you in advance.

Sean

 

[Michael Gramelspacher]

Im hoping this can be done...I thank you in advance for your
attention...

We keep track on our database of dates that companies are working in
our state... if they go over 180 days in a calender year, they need a
license... they can be in the state multiple times I have no problem
using datediff and getting the number of days of each "work order". I
can even sum the datediffs together. However, our old database did
not reject overlapping dates by the same company. Thus there are
numerous overlaps (ex: One company is in the state from 1/15/07 to
3/23/07 and again from 1/18/07 to 4/15/07 on two different
projects).

This leads right into my question: Is there a way to disregard
overlapping work order dates when figuring out total days? Or, since
i am building a new database for this information, is there a way to
prevent a user from entering an overlapping date in the first place?
(i know thats more of a form question, i think)

Thank you in advance.

Sean

Actually it is a table constraint question too. Google recent thread Design
Question and Jamie Collins.

Probably no reason it cannot be both a table constraint and a form validation.

 

[Jamie Collins]

We keep track on our database of dates that companies are working in
our state... if they go over 180 days in a calender year, they need a
license... they can be in the state multiple times I have no problem
using datediff and getting the number of days of each "work order". I
can even sum the datediffs together. However, our old database did
not reject overlapping dates by the same company.

I'll wager the designer thought, "I'll code that bit in the front
end..."

Thus there are
numerous overlaps (ex: One company is in the state from 1/15/07 to
3/23/07 and again from 1/18/07 to 4/15/07 on two different
projects).

.... and this is what happens when comstraints are omitted from the
data layer.

This leads right into my question: Is there a way to disregard
overlapping work order dates when figuring out total days? Or, since
i am building a new database for this information, is there a way to
prevent a user from entering an overlapping date in the first place?
(i know thats more of a form question, i think)

Here:

http://groups.google.com/group/micr...3b6e7e7751/0fb58491ff039e1c?#0fb58491ff039e1c

The recommendation was to use a Calendar table (http://
sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-
auxiliary-calendar-table.html) and GROUP BY the calendar date.

Using Northwind, this finds the number of dates on order (between
order date and shipped date) for a particular customer:

SELECT DT1.CustomerID, COUNT(*) AS days_on_order
FROM
(
SELECT O1.CustomerID, C1.dt
FROM Orders AS O1, Calendar AS C1
WHERE C1.dt BETWEEN O1.OrderDate AND O1.ShippedDate
) AS DT1
WHERE CustomerID = 'ANTON'
GROUP BY DT1.CustomerID;

This find the number of *unique* days (note a GROUP BY has been added
to the derived table DT1):

SELECT DT1.CustomerID, COUNT(*) AS days_on_order
FROM
(
SELECT O1.CustomerID, C1.dt
FROM Orders AS O1, Calendar AS C1
WHERE C1.dt BETWEEN O1.OrderDate AND O1.ShippedDate
GROUP BY O1.CustomerID, C1.dt
) AS DT1
WHERE CustomerID = 'ANTON'
GROUP BY DT1.CustomerID;

Jamie.

--