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
Hey Joseph --