I'm working with a SQL query for a Human Resources database. Its intended purpose is to find all the paycheck records with a check date (prckhist.chkdate ) more recent than eleven days past the employee's last raise (hrpersnl.p_paydate). It then totals the hours the employee worked from those records (prdehist.units), and if the total is within 40 hours of a threshold (hrpersnl.p_jmisc3) then the employee's name and information is returned, and the employee is eligible for another raise.
The problem is this: the database has been corrupted, and there are a few duplicate records in the hrpersnl table. Because of this, in some cases the query will use an incorrect last raise date (hrpersnl.p_paydate) and find the wrong number of paycheck records, throwing the total off.
My question is, how do I make the query calculate from the most recent hrpersnl record, in the event that there are duplicates? I've tried changing the 6th line in the WHERE section to the following: - AND (MAX(hrpersnl.p_paydate)+11) <= prckhist.chkdate
I've also tried - AND (SELECT MAX(hrpersnl.p_paydate)+11) <= prckhist.chkdate
and - AND ((SELECT MAX(hrpersnl.p_paydate)FROM hrpersnl)+11) <= prckhist.chkdate
and a few other variants. I feel like I'm missing something really basic. Anyone have any ideas?
Thanks in advance. Full code is as follows: - SELECT hrpersnl.p_empno AS Employee_Number,
-
hrpersnl.p_fname AS First_Name,
-
hrpersnl.p_lname AS Last_Name,
-
hrpersnl.p_paydate AS Effective_Date,
-
SUM(prdehist.units) +hrtkpers.u_hrswrk AS Total,
-
hrpersnl.p_jmisc3 AS Threshold
-
FROM prdehist prdehist, hrpersnl hrpersnl, prckhist prckhist,hrtkpers
-
WHERE prdehist.empno = hrpersnl.p_empno
-
AND prdehist.empno = prckhist.empno
-
AND prdehist.chknumber = prckhist.chknumber
-
AND prdehist.empno=hrtkpers.u_empno
-
AND hrpersnl.p_active <>"T"
-
AND hrpersnl.p_paydate+11 <= prckhist.chkdate
-
AND (prdehist.code = '0001')
-
GROUP BY hrpersnl.p_empno
-
HAVING (SUM(prdehist.units)+hrtkpers.u_hrswrk>= (hrpersnl.p_jmisc3 - 40)) AND (hrpersnl.p_jmisc3 >0)
-
ORDER BY hrpersnl.p_empno
4 2730
Try this:
[PHP]SELECT hrpersnl.p_empno AS Employee_Number,
hrpersnl.p_fname AS First_Name,
hrpersnl.p_lname AS Last_Name,
hrpersnl.p_paydate AS Effective_Date,
SUM(prdehist.units) +hrtkpers.u_hrswrk AS Total,
hrpersnl.p_jmisc3 AS Threshold
FROM prdehist prdehist, (select distinct * from hrpersnl) hrpersnl, prckhist prckhist,hrtkpers
WHERE prdehist.empno = hrpersnl.p_empno
AND prdehist.empno = prckhist.empno
AND prdehist.chknumber = prckhist.chknumber
AND prdehist.empno=hrtkpers.u_empno
AND hrpersnl.p_active <>"T"
AND hrpersnl.p_paydate+11 <= prckhist.chkdate
AND (prdehist.code = '0001')
GROUP BY hrpersnl.p_empno
HAVING (SUM(prdehist.units)+hrtkpers.u_hrswrk>= (hrpersnl.p_jmisc3 - 40)) AND (hrpersnl.p_jmisc3 >0)
ORDER BY hrpersnl.p_empno [/PHP]
[PHP]SELECT hrpersnl.p_empno AS Employee_Number,
hrpersnl.p_fname AS First_Name,
hrpersnl.p_lname AS Last_Name,
hrpersnl.p_paydate AS Effective_Date,
SUM(prdehist.units) +hrtkpers.u_hrswrk AS Total,
hrpersnl.p_jmisc3 AS Threshold
FROM prdehist prdehist,
(select distinct * from hrpersnl) hrpersnl,
prckhist prckhist,
hrtkpers
WHERE prdehist.empno = hrpersnl.p_empno
AND prdehist.empno = prckhist.empno
AND prdehist.chknumber = prckhist.chknumber
AND prdehist.empno=hrtkpers.u_empno
AND hrpersnl.p_active <>"T"
AND hrpersnl.p_paydate+11 <= prckhist.chkdate
AND (prdehist.code = '0001')
GROUP BY hrpersnl.p_empno
HAVING (SUM(prdehist.units)+hrtkpers.u_hrswrk>= (hrpersnl.p_jmisc3 - 40)) AND (hrpersnl.p_jmisc3 >0)
ORDER BY hrpersnl.p_empno [/PHP]
For some reason it didn't make my change bold but you will figure.
Thanks for your reply.
Unfortunately that returned the following error: - 3700: [Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognizable phrase/keyword.
Sorry, I wasn't aware it is a Fox Pro.
But still you can create a view to select distinct records from this table and use a view in this query instead of a table name.
Good Luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nova's Taylor |
last post by:
Hi folks,
I am a newbie to Python and am hoping that someone can get me started
on a log parser that I am trying to write.
The log is an ASCII file that contains a process identifier (PID),...
|
by: Pawel Banys |
last post by:
Hello,
There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present...
|
by: Tim Graichen |
last post by:
Good morning,
I have a sub-form that displays records from a table as a continuous form.
The table has several hundred records, but the subform only displays five or
six records. The records do...
|
by: Shaiguy |
last post by:
I have a table containing the following fields:
ProjectUpdateID (PrimaryKey)
ProjectID
UpdateDate
I would like to create a Query in Ms Access 2000 which will return me
the most recent 2...
|
by: Frances |
last post by:
Hi All,
I'm having a problem trying to add a record to a simple Access 2000 db
(db is very similar to an address book but with more info than the
usual address, phone, etc.).
The database is...
|
by: syvman |
last post by:
Hi all... I have a quick question, if anyone knows the answer. I've
built a query which looks at a set of records in a table (tblFilename)
and I've set it up so that it displays the following...
|
by: Jason H |
last post by:
Hi,
I am sure I am just overlooking the obvious, but I am having a little trouble with this one...
I am setting up an inventory database that tracks company tools and their location. This...
|
by: erikcw |
last post by:
Hi,
I'm getting the following error when I try to pass a list into a
function.
My List: crea =
Traceback (most recent call last):
File "wa.py", line 118, in ?
curHandler.walkData()
|
by: robert.waters |
last post by:
I need to perform the following:
- select the most recent X number of records in a table (there is a
timestamp field)
- select the Nth occurrence of X number of records
ex:
- most recent 10...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |