NULL VALUE Problem

[wheels0323]

SELECT TTBL.RD_DISTRICT, TTBL.LOCATION, JOBTITLE, NAME, JOB_ENTRY_DT, SORTORDER
FROM 
  (SELECT RD_DISTRICT, LOCATION, JOBTITLE, JOBCODE, SORTORDER
   FROM
    (SELECT DISTINCT JOBTITLE, JOBCODE,
       SORTORDER=CASE WHEN JOBCODE = 1231 THEN 1
                      WHEN JOBCODE = 1405 THEN 2
                      WHEN JOBCODE = 2337 THEN 3
                      WHEN JOBCODE = 2336 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, 2337, 2335, 1890, 1136, 2169, 1194,
                       1017, 1045, 1581, 2041, 2339, 2340, 2350, 2342,
                       2341, 1105, 1260, 2338, 2336)) 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, JOB_ENTRY_DT, JOBCODE
   FROM dbo.PS_RD_EMPLOYEES_VW 
   WHERE 
     RD_DISTRICT = 'D1' AND
     JOBCODE IN (1231, 1405, 2337, 2335, 1890, 1136, 2169, 1194,
                 1017, 1045, 1581, 2041, 2339, 2340, 2350, 2342,
                 2341, 1105, 1260, 2338, 2336)) AS PTBL
  ON TTBL.LOCATION = PTBL.LOCATION AND
     TTBL.JOBCODE = PTBL.JOBCODE
ORDER BY TTBL.LOCATION, SORTORDER

Heres my problem, in this line of code...

                      WHEN JOBCODE = 2337 THEN 3
                      WHEN JOBCODE = 2336 THEN 3

Column Headers
RD_DISTRICT, LOCATION, JOBTITLE, NAME, JOBENTRYDT, SORTORDER

Two lines of example code for one district, one location, one jobtitle

D1 06301 Grocery Operations Manager - H Rist,Thomas 2008-11-16 3

D1 06301 Grocery Operations Manager NULL NULL 3

For this district, and this location, I only want to bring back one row. The one with data in, but it's grabbing two rows. At first i decided to do a convert function for the JOBTITLE field to only grab 'Grocery Operations Manager' and take out the JOBCODE in the select statement. When I do that though it skips the other JOBCODE.

I need a way to display only one record if it exists for a location. Those two jobcodes 2336 and 2337 are the same JOBTITLE after the conversion function above. Sometimes I will need to bring back two lines if two people are assigned via each location. Then also sometimes I will need to bring back one row with null values in date and name if no one is assigned at that store.

Any suggestions?

So to recap.. I need to work with the two JOBCODES - 2336 and 2337 assigned to sort order 3 to only bring one row back if a person exists at that location with that jobcode. Yet, at this current point, it's grabbing the active person in the first jobcode, and then bringing back another empty row value for the other jobcode.

 

[LaurieHamlin]

What's the DB?
If you can group on sort order, you could take the max value, which would get you only the row with the data.
Hope that helps.