Is this query possible?

Dear *,
I need help with a query!
I've set up a Database for managing employees and their contracts in
our institution.
I have two tables (stripped-down):
tbl_persons:
PersonID, smallint(2) Primary Key
Name, varchar(50)
tbl_contracts:
ContractID, smallint(2) Primary Key
Person_ID, smallint(2)
Begin, datetime(8)
End, datetime(8)
Now, for a number of reasons, it is possible, that Persons can have
consecutive entries in this database (changing of status
in the organisation, a number of fixed-term contracts etc.).
On our Intranet-Webpages, I would like present a list with people
leaving our institution. For this purpose i Wrote the following query:
CREATE VIEW dbo.qry_people_leave
AS
SELECT TOP 100 PERCENT dbo.tbl_contracts.Begin, dbo.tbl_contracts.End,
dbo.tbl_persons.Name
FROM dbo.tbl_persons INNER JOIN
dbo.tbl_contracts ON dbo.tbl_persons.PersonID =3D
dbo.tbl_contracts.PersonID
WHERE (dbo.tbl_contracts.End BETWEEN { fn NOW() } - 7 AND { fn NOW() }
+ 92) AND (dbo.tbl_contracts.Begin <=3D { fn NOW() })
ORDER BY dbo.tbl_contracts.End
But then people will appear on the list, whose contracts end during the
next three months, but who've got another contract subsequent to the on
shown in the list. This is irritating.
It's the same for a similar query to list "new" employees. People
appear as new employees, that have worked for years in our institute,
just because they've got a new contract.
Is it possible to have a query that display persons, whose contract
ends in the next three months, but only if there are
no later contracts for this person entered in the Database?
Any help/hint would be greatly appreciated!
Thanks in advance,
Manuel Sch=FCrenThere are some drawbacks to the approach I will describe (one of which is yo
u
do not account for gaps in contract periods), but maybe this might help.
Use a derived table t oget to the contract with the greatest expiration date
and join to your contract table to get all of the other data columns. For
instance:
SELECT c.Begin, c.End, p.Name
FROM dbo.tbl_persons p
INNER JOIN dbo.tbl_contracts c ON p.PersonID = c.PersonID
INNER JOIN (SELECT PersonID, MAX(End) AS End FROM tbl_contracts GROUP BY
Person_ID) m
ON c.PersonID = m.PersonID AND c.End = m.End
WHERE (dbo.tbl_contracts.End BETWEEN { fn NOW() } - 7 AND { fn NOW() }
+ 92) AND (dbo.tbl_contracts.Begin <= { fn NOW() })
ORDER BY dbo.tbl_contracts.End
So your derived table includes the MAX End Date for each PersonID. Now when
you link to it you will get the contract record for that person with that en
d
date. Note that if you have more than one contract with the same end date
for a given person, you will get back multiple rows.
HTH,
John Scragg
"manuel.schueren@.web.de" wrote:

> Dear *,
> I need help with a query!
> I've set up a Database for managing employees and their contracts in
> our institution.
> I have two tables (stripped-down):
> tbl_persons:
> PersonID, smallint(2) Primary Key
> Name, varchar(50)
> tbl_contracts:
> ContractID, smallint(2) Primary Key
> Person_ID, smallint(2)
> Begin, datetime(8)
> End, datetime(8)
>
> Now, for a number of reasons, it is possible, that Persons can have
> consecutive entries in this database (changing of status
> in the organisation, a number of fixed-term contracts etc.).
> On our Intranet-Webpages, I would like present a list with people
> leaving our institution. For this purpose i Wrote the following query:
> CREATE VIEW dbo.qry_people_leave
> AS
> SELECT TOP 100 PERCENT dbo.tbl_contracts.Begin, dbo.tbl_contracts.End,
> dbo.tbl_persons.Name
> FROM dbo.tbl_persons INNER JOIN
> dbo.tbl_contracts ON dbo.tbl_persons.PersonID =
> dbo.tbl_contracts.PersonID
> WHERE (dbo.tbl_contracts.End BETWEEN { fn NOW() } - 7 AND { fn NOW() }
> + 92) AND (dbo.tbl_contracts.Begin <= { fn NOW() })
> ORDER BY dbo.tbl_contracts.End
> But then people will appear on the list, whose contracts end during the
> next three months, but who've got another contract subsequent to the on
> shown in the list. This is irritating.
> It's the same for a similar query to list "new" employees. People
> appear as new employees, that have worked for years in our institute,
> just because they've got a new contract.
> Is it possible to have a query that display persons, whose contract
> ends in the next three months, but only if there are
> no later contracts for this person entered in the Database?
> Any help/hint would be greatly appreciated!
> Thanks in advance,
> Manuel Schüren
>|||Dear John,
thank you very much, this works like a charm!
Not accounting for gaps in contract periods is not a main problem, but
what about the other drawbacks for this solution, you've mentioned in
the beginning of your article?
Are there any serious ones?
Nevertheless, this helped a lot, great approach.
Thanks again.
Best regards,
Manuel

Is this possible? SQL Server 2000 Write times

No timezone discrepancy, they did it in our labs here at our institution. I
didn't think so, but had to ask. What logs would i search and what would I
look for?

Shawn Ferguson wrote:
> I created an online application for one of our educational programs.
> The data is inserted via website entry -> client side validation ->
> stored procedure -> database. Given a table stucture as follows, if 100
> or even 1000 people applied at the exact same time using an online
> application, what is the maximum time interval that could separate each
> record. How long would SQL Server 2000 generally take to insert each
> record into the database. The problem is that each year, only the top
> 100 students are admitted in the program, but there is one student who
> says they applied at 9:00 am and their record insert time shows 9:59am.
> There are records before it and after it that shows times like 9:58,
> 9:58, 9:59: 10:00, 10:00, etc. Could it really take SQL Server 59
> minutes to actually write the record if 1000 people hit the database at
> the same time? Please help, they want to get counsil involved.
>
> ApplicantID int primary key identity
> SSN varchar(11)
> FirstName varchar(30)
> LastName varchar(50)indexed
> Address varchar (50)
> City varchar (50)
> State char (2)
> Zip varchar (11)
> Phone varchar (20)
> Email varchar (50)
> dateEntered smalldatetime
I would not expect that sort of delay. 9:00am/9:59am, could there be a
time zone discrepancy? Can you use your website logs to determine when
the user made their submission?Shawn Ferguson wrote:
> No timezone discrepancy, they did it in our labs here at our
> institution. I didn't think so, but had to ask. What logs would i
> search and what would I look for?
>
That would be a question for your web server folks. Your web server
should maintain logs of the visits to the web site.|||> No timezone discrepancy, they did it in our labs here at our institution.
That doesn't mean that some computer along the way doesn't have an incorrect
timezone and/or system time specified!