wheels0323
Programmer
Code:
SELECT TTBL.RD_DISTRICT, TTBL.LOCATION, JOBTITLE, NAME, Date, SORTORDER
FROM
(SELECT RD_DISTRICT, LOCATION, CONVERT(CHAR(26), JOBTITLE) as JOBTITLE, JOBCODE, SORTORDER
FROM
(SELECT DISTINCT CONVERT(CHAR(26), JOBTITLE) as JOBTITLE, JOBCODE,
SORTORDER=CASE WHEN JOBCODE = 1231 THEN 1
WHEN JOBCODE = 1405 THEN 2
WHEN JOBCODE = 2336 OR JOBCODE = 2337 THEN 3
WHEN JOBCODE = 2335 THEN 4
WHEN JOBCODE = 1890 THEN 5
WHEN JOBCODE = 1136 THEN 6
WHEN JOBCODE = 2169 THEN 7
WHEN JOBCODE = 1194 THEN 8
WHEN JOBCODE = 1017 THEN 9
WHEN JOBCODE = 1045 THEN 10
WHEN JOBCODE = 1581 THEN 11
WHEN JOBCODE = 2041 THEN 12
WHEN JOBCODE = 2339 THEN 13
WHEN JOBCODE = 2340 THEN 14
WHEN JOBCODE = 2350 THEN 15
WHEN JOBCODE = 2342 THEN 16
WHEN JOBCODE = 2341 THEN 17
WHEN JOBCODE = 1105 THEN 18
WHEN JOBCODE = 1260 THEN 19
WHEN JOBCODE = 2338 THEN 20
END
FROM dbo.PS_RD_EMPLOYEES_VW
WHERE JOBCODE IN (1231, 1405, 2336, 2337, 2335, 1890, 1136, 2169, 1194,
1017, 1045, 1581, 2041, 2339, 2340, 2350, 2342,
2341, 1105, 1260, 2338)) AS JTBL
CROSS JOIN
(SELECT DISTINCT RD_DISTRICT, LOCATION
FROM dbo.PS_RD_EMPLOYEES_VW
WHERE RD_DISTRICT = 'D1') AS LTBL) AS TTBL
LEFT OUTER JOIN
(SELECT RD_DISTRICT, LOCATION, NAME, CONVERT(VARCHAR(8), DEPT_ENTRY_DT, 1) AS Date, JOBCODE
FROM dbo.PS_RD_EMPLOYEES_VW
WHERE
RD_DISTRICT = 'D1' AND
JOBCODE IN (1231, 1405, 2336, 2337 , 2335, 1890, 1136, 2169, 1194,
1017, 1045, 1581, 2041, 2339, 2340, 2350, 2342,
2341, 1105, 1260, 2338)) AS PTBL
ON TTBL.LOCATION = PTBL.LOCATION AND
TTBL.JOBCODE = PTBL.JOBCODE
ORDER BY TTBL.LOCATION, SORTORDER
In my code above, I have two JOBCODE'S for number '3'. I need to do this b/c of work reasons. The problem with this, is that it always brings back two lines of row of data when I only need one, unless two people exist with that JOBCODE.
So right now it comes back like...
D1 06301 GROCERY OPERATIONS MANAGER BOB 11/16/08 2336 3
D1 06301 GROCERY OPERATIONS MANAGER NULL NULL 2337 3
Just for that one job code. I need only that first line to come back, UNLESS another person has that jobcode at the assigned location.
How would I do this?
I know i hafta modify the
Code:
WHEN JOBCODE = 2336 OR JOBCODE = 2337 THEN 3
section somehow. Any ideas would be great. Thanks. I will give many stars for an answer.