November 9, 2009 at 11:46 am
Try this:
SELECT
CONVERT(VARCHAR(12), PRPHourlyEnd, 112) + 'PR' AS PRBatchID,
EmpLname,
EmpPayrollnum,
CASE WHEN PREarnCode IS NULL
THEN CASE WHEN EmpHourly = 0 THEN 'INVALID EARN CODE'
ELSE ''
END
ELSE PREarnCode
END AS PREarnCode,
CASE WHEN EmpHourly = 1 THEN 'Hourly'
ELSE CASE WHEN EmpCalcOT = 1 THEN 'Salaried NonExempt'
ELSE 'Salaried Exempt'
END
END AS c5,
PREarnCode,
CONVERT(VARCHAR(10), PRPHourlyBeg, 101) AS PRBegDate,
CONVERT(VARCHAR(10), PRPHourlyEnd, 101) AS PREndDate,
PRHours,
EmpCalcOT,
EmpHourly,
(SELECT
skdesc
FROM
SkillLevel
WHERE
SKID = EmpLevel) AS empLevelDesc
FROM
Employee E
LEFT JOIN PRTimesheet
ON E.ID = PRTimeSheet.PRempID
LEFT JOIN PRPeriod
ON PRPId = PRTimesheet.PRPeriodID
INNER JOIN Office
ON empoff = Office.OffID
WHERE
(PRPeriodId = 37
OR PRPeriodID IS NULL)
AND EmpNoPayroll = 0
AND E.Deleted = 0
AND Empstatus = 'A'
AND E.Id > 0
AND
NOT (PREarnCode = 'REGULAR'
AND c5 != 1)
ORDER BY
EmpLname,
PREarncode;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2009 at 1:02 pm
Thanks for the prompt reply!
I tried your query and received this message:
Msg 207, Level 16, State 1, Line 46
Invalid column name 'c5'.
It seems that 'c5' is defined...
Laurie
November 10, 2009 at 6:44 am
Change "c5 != 1" to "EmpCalcOT != 1" in the Where clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2009 at 8:20 am
Thanks!
The query is now working!
But unfortunately it is excluding the REGULAR hours for the Hourly employees (as well as the REGULAR hours for the Salaried Exempt - or basically ALL REGULAR hours). I only want to exclude the REGULAR hours for the Salaried Exempt employees.
This is why I was trying to use the 'c5' column. Any ideas?
Laurie
November 10, 2009 at 8:30 am
What it excludes will be based on values in the EmpCalcOT column. Set up the Where clause to include the values you want in that column, and it'll work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2009 at 10:14 am
Thanks again!
I was able to get exactly the payroll records I need from the query!
Now I have also eliminated columns that are not required for the payroll import - except for one column (empLevelDesc). For some reason, when I try to comment it out, I get the following error:
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'FROM'.
Here is the most recent script with the code commented out that I wouldl like to eliminate:
SELECT
CONVERT(VARCHAR(12), PRPHourlyEnd, 112) + 'PR' AS PRBatchID,
EmpPayrollnum,
CASE WHEN PREarnCode IS NULL
THEN CASE WHEN EmpHourly = 0 THEN 'INVALID EARN CODE'
ELSE ''
END
ELSE PREarnCode
END AS PREarnCode,
CONVERT(VARCHAR(10), PRPHourlyBeg, 101) AS PRBegDate,
CONVERT(VARCHAR(10), PRPHourlyEnd, 101) AS PREndDate,
PRHours,
-- (SELECT
-- skdesc
-- FROM
-- SkillLevel
-- WHERE
-- SKID = EmpLevel) AS empLevelDesc
FROM
Employee E
LEFT JOIN PRTimesheet
ON E.ID = PRTimeSheet.PRempID
LEFT JOIN PRPeriod
ON PRPId = PRTimesheet.PRPeriodID
INNER JOIN Office
ON empoff = Office.OffID
WHERE
(PRPeriodId = 37
OR PRPeriodID IS NULL)
AND EmpNoPayroll = 0
AND E.Deleted = 0
AND Empstatus = 'A'
AND E.Id > 0
AND
NOT (PREarnCode = 'REGULAR'
AND EmpHourly != 1)
ORDER BY
EmpLname,
PREarncode;
----------------------------------------------------------------------------------------
Is there a better way to eliminate that column from the output?
Laurie
November 11, 2009 at 6:49 am
The error is because of the comma after PRHours.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 8:36 am
Perfect! Thanks so much for your help!
Laurie
November 11, 2009 at 8:38 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply