Re: Combining start and end dates to create a date range

Hi,


A first query:

SELECT id, surname, firstName, team, start as x FROM myTable
UNION ALL
SELECT id, surname, firstName, team, end FROM myTable


save it, as, say, q1

Then

SELECT id, surname, firstName, team, x
FROM q1
GROUP BY id, surname, firstName, team, x
HAVING COUNT(*)=1


should return the limits (we eliminate those dates that occur twice in their
merge starting/ending data. Save it as q2.


A last query:
------------------------
SELECT b.id, b.surname, b.firstname, b.team, b.x AS starting, MIN(c.x) AS
ending

FROM ( myTable As a INNER JOIN q2 AS b
ON a.id=b.id AND a.surname=b.surname AND a.firstName = b.firstname AND
a.team=b.team AND b.x=a.start )
INNER JOIN q2 AS c
ON a.id=c.id AND a.surname=c.surname AND a.firstName = c.firstname AND
a.team=c.team AND c.x=a.end

WHERE c.x > b.x

GROUP BY b.id, b.surname, b.firstname, b.team, b.x
------------------------


should re-establish the interval limits on a same record ( I haven't
tested )




Hoping it may help,
Vanderghast, Access MVP

"Mark Stephenson" <MarkStephenson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:70657C60-0687-406B-8C20-37241EA9C703@xxxxxxxxxxxxxxxx
Hi,

I have a table with the following fields;

ID
SURNAME
FIRST NAME
TEAM
START
END

The table records clients open to a team so for instance

5 - STEPHENSON - MARK - TEAM A - 1/1/04 - 3/1/04
5 - STEPHENSON - MARK - TEAM B - 3/1/04 - 5/1/04
5 - STEPHENSON - MARK - TEAM A - 7/1/05 -

As you can see from the above, the first 2 rows of data show an unbroken
allocation to someone (team a/b) from 1/1/04 to the 5/1/04, then another
run
from 7/1/05 - Null...

What i want to do is make the above 3 rows for example look like

5 - STEPHENSON - MARK - 1/1/04 - 5/1/04
5 - STEPHENSON - MARK - 7/1/05

Any help is appreciated.

Thanks,


Mark.



.

Relevant Pages

  • Re: Combining start and end dates to create a date range
    ... A first query: ... SELECT id, surname, firstName, team, start as x FROM myTable ... merge starting/ending data. ... FROM (myTable As a INNER JOIN q2 AS b ...
    (microsoft.public.access.queries)
  • Re: Trigger Update - Help?
    ... >I simplified the query. ... If the LastName and surname are ... >UPDATE Employee SET LastName=I.surname ... >FROM Inserted I INNER JOIN Employee IT ...
    (microsoft.public.sqlserver.programming)
  • Re: asp from relational access db
    ... reveiw.reveiwer As Reviewer, personel_1.surname As Personel_1_Surname ... ... > ID CODE SURNAME ... > FROM personel INNER JOIN (reveiw INNER JOIN personel AS ... > CODE SURNAME REVEIWER SURNAME ...
    (microsoft.public.frontpage.programming)
  • Re: Trigger Update - Help?
    ... I simplified the query. ... If the LastName and surname are different then ... UPDATE Employee SET LastName=I.surname ... FROM Inserted I INNER JOIN Employee IT ...
    (microsoft.public.sqlserver.programming)