calculating a date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table that I need to calculate the enddate field. I inherited this
database, and it is only now that the users need to calculate the enddate.
And of course, they need the endate to be retroactive.

The table is called tblPatientDispos. It is used to determine where
someonbe is at a particular date. The fields in the tableare Venue ID (pk),
DispoID, Venue, StartDate, EndDate. The EndDate should be the date before
the the next StartDate for the same DispoID.

A typical example of this the following:

VenueID DispoID StartDate EndDate

1123 4444 03/15/1999 ?
1679 4444 07/23/2004

How do I translate this into an SQL statement?

Any assistance would be appreciated greatly.

Thanks in advance.
 
The table is called tblPatientDispos. It is used to determine where
someonbe is at a particular date. The fields in the tableare Venue ID
(pk), DispoID, Venue, StartDate, EndDate. The EndDate should be the
date before the the next StartDate for the same DispoID.
Click to expand...
How do I translate this into an SQL statement?
Click to expand...

With a sub-select query

select o.dispoid,
o.venue,
o.startdate,
(
select min(i.startdate)-1
from patientdispos as i
where i.dispoid = o.dispoid
and i.startdate>o.startdate
) as enddate,

from patientdispos as o

order by dispoid


This is not tested, but the logic should be pretty self explanatory.
Hope that helps


Tim F
 
thx. i'll try it out.

Tim Ferguson said:
With a sub-select query

select o.dispoid,
o.venue,
o.startdate,
(
select min(i.startdate)-1
from patientdispos as i
where i.dispoid = o.dispoid
and i.startdate>o.startdate
) as enddate,

from patientdispos as o

order by dispoid


This is not tested, but the logic should be pretty self explanatory.
Hope that helps


Tim F
Click to expand...
 
Back
Top