November 9, 2009 at 10:08 am
Hello everyone,
I have pulled a SQL query via running SQL Profiler on a Time report. I have also been able to tweak the query to display primarily what I want it to display for the input file. This file will be used to integrate into a Payroll application.
I need to also exclude records where the (PREarnCode = 'REGULAR' and c5 = 'Salaried Exempt'). I have tried this in a couple of different ways (they are commented out), but both generate errors.
When I include the CASE statement, I get the following error:
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'NOT'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'as'.
When I include the AND.... I get the following error:
Msg 207, Level 16, State 1, Line 32
Invalid column name 'c5'.
Here is the SQL statement I am working with and a sample of the output that I get when I comment out my two attempts at excluding those records:
use "practicemgmt"
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,
-- CASE WHEN C5 = 'Salaried Exempt' and PREarnCode = 'REGULAR'
-- THEN NOT PREarnCode
-- ELSE PREarnCode END AS 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 = 'Salaried Exempt')
ORDER BY EmpLname,PREarncode
--------------------------------------------------------------------------------------------
THERE IS A SAMPLE OF THE OUTPUT ATTACHED AS A .PNG FILE.
Any help will be appreciated - my limited SQL expertise is showing!
Thanks in advance!
Laurie
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply