I've got the 2 queries below connected with a UNION statement.  I need
to combine them into 1 query because I am getting duplicate rows in my
result set for students selected by the 2 separate queries.  I am not
sure how.  The tricky part is:
1) not all students selected by the query have a record in the
stoplist s table
2) I only want to retrieve from the stoplist s table where the
stop_reason equals the values AD, AS, EX and READ and I want to print
the long desc of these in the Reason column. For students that may
have records in the stoplist that aren't equal to this but still
belong in the query, I need to print nothing in the Reason column.
Also, some students have multiple records in the stoplist table and in
those cases I still only need one entry in my list where the
stop_reason is equal to one of the 4 above).

Help??


declare @action int
declare @actiond int
set @action = 1
set @actiond = 0

--all students: newly admitted and currently enrolled last 3 years
select distinct a.people_code_id, p.first_name, p.middle_name,
p.last_name, pt.people_type, a.college, @action as action, SPACE(NULL)
as reason
from academic A, address C, people p, peopletype pt
where a.people_code_id = c.people_org_code_id and
p.people_code_id = c.people_org_code_id and
p.preferred_add = c.address_type and
p.people_code_id = pt.people_code_id and
a.primary_flag='Y' and a.academic_flag='Y' and
pt.people_type = 'STUD' and
((a.academic_year=year(getdate()) or
a.academic_year=year(getdate())-1 or a.academic_year=year(getdate
())-2) and
a.academic_year<>'+' and a.academic_year<>'.') and
a.academic_session='' and
a.degree <> 'NONDEG' and
a.academic_session <> 'WKSHP'

UNION

--all stop out or dismissed students
select distinct a.people_code_id, p.first_name, p.middle_name,
p.last_name, pt.people_type, a.college, @actiond as action,
cs.long_desc as reason
from academic A, address C, people p, stoplist s, code_stoplist cs,
peopletype pt
where a.people_code_id = c.people_org_code_id and
p.people_code_id = c.people_org_code_id and
p.preferred_add = c.address_type and
p.people_code_id = pt.people_code_id and
p.people_code_id = s.people_code_id and
s.stop_reason = cs.code_value_key and
a.primary_flag='Y' and a.academic_flag='Y' and
pt.people_type = 'STUD' and
a.degree <> 'NONDEG' and
a.academic_session <> 'WKSHP' and
((s.stop_reason = 'AD' or
s.stop_reason = 'AS' or
s.stop_reason = 'EX' or
s.stop_reason = 'READ') and
s.cleared = 'N')

You can use LEFT OUTER JOIN to merge the two queries.

SELECT A.people_code_id,
        P.first_name,
        P.middle_name,
        P.last_name,
        PT.people_type,
        A.college,
        @action AS action,
        CS.long_desc AS reason
FROM academic AS A
JOIN address AS C
   ON A.people_code_id = C.people_org_code_id
JOIN people AS p
   ON P.people_code_id = C.people_org_code_id
  AND P.preferred_add = C.address_type
JOIN peopletype AS pt
   ON P.people_code_id = PT.people_code_id
LEFT OUTER JOIN stoplist AS S
   ON P.people_code_id = S.people_code_id
  AND S.stop_reason IN ('AD', 'AS', 'EX', 'READ')
  AND S.cleared = 'N'
LEFT OUTER JOIN code_stoplist AS CS
   ON S.stop_reason = CS.code_value_key
WHERE A.primary_flag = 'Y'
   AND A.academic_flag = 'Y'
   AND PT.people_type = 'STUD'
   AND A.academic_year >= YEAR(CURRENT_TIMESTAMP) - 2
   AND A.academic_year <= YEAR(CURRENT_TIMESTAMP)
   AND A.academic_year <> '+'
   AND A.academic_year <> '.'
   AND A.academic_session = ''
   AND A.degree <> 'NONDEG'
   AND A.academic_session <> 'WKSHP';

Wow Plamen, this makes perfect sense now that I see it.  Can I bother
you with 2 other minor questions?

1) I need to get the max Academic Year for each individual in this
section:
AND A.academic_year >= YEAR(CURRENT_TIMESTAMP) - 2
   AND A.academic_year <= YEAR(CURRENT_TIMESTAMP)
Can you tell me how to add that logic?

2) For the action variable - I need it to be a 1 if they do not have
one of the stoplist values selected by the query and a 0 if they do.
How do I add that logic in without adding another column?

Thanks again!

I do not understand what you need for 1. For 2, you can just use a CASE
expression to define the action column:

CASE WHEN S.stop_reason IN ('AD', 'AS', 'EX', 'READ')
       AND S.cleared = 'N'
      THEN 0
      ELSE 1
END AS action

Also, it is best to specify the SQL Server version you use, it matters a
lot for particular solutions.

A guess about 1 is that you want the filter to be not on the current
year, but on the last 3 years for each individual. On SQL Server
2005/2008 you can do something like this:

SELECT A.people_code_id,
        P.first_name,
        P.middle_name,
        P.last_name,
        PT.people_type,
        A.college,
        CASE WHEN S.stop_reason IN ('AD', 'AS', 'EX', 'READ')
              AND S.cleared = 'N'
             THEN 0
             ELSE 1
        END AS action,
        CS.long_desc AS reason
FROM (SELECT people_code_id,
              college,
              DENSE_RANK() OVER(PARTITION BY people_code_id
                                ORDER BY academic_year DESC) AS rk
       FROM academic
       WHERE primary_flag = 'Y'
         AND academic_flag = 'Y'
         AND academic_year <> '+'
         AND academic_year <> '.'
         AND academic_session = ''
         AND degree <> 'NONDEG'
         AND academic_session <> 'WKSHP') AS A
JOIN address AS C
   ON A.people_code_id = C.people_org_code_id
JOIN people AS p
   ON P.people_code_id = C.people_org_code_id
  AND P.preferred_add = C.address_type
JOIN peopletype AS pt
   ON P.people_code_id = PT.people_code_id
LEFT OUTER JOIN stoplist AS S
   ON P.people_code_id = S.people_code_id
  AND S.stop_reason IN ('AD', 'AS', 'EX', 'READ')
  AND S.cleared = 'N'
LEFT OUTER JOIN code_stoplist AS CS
   ON S.stop_reason = CS.code_value_key
WHERE PT.people_type = 'STUD'
   AND A.rk <= 3;

Hi -
I am using SQL 2005.  The CASE statement worked beautifully, I should
have thought of that :).  As far as the academic years, I want the
filter to select students who have records in the last 3 years (which
is is doing) but if a student has a record in more than 1 of those 3
years, I want it to select the max year for each student.  Is that
possible with a max statement?

Thanks!

You can change the query as follows to get only the MAX year from the
last 3 years. Basically you rank by the years in descending order and
take only the top one (which is the latest).

SELECT A.people_code_id,
        P.first_name,
        P.middle_name,
        P.last_name,
        PT.people_type,
        A.college,
        CASE WHEN S.stop_reason IN ('AD', 'AS', 'EX', 'READ')
              AND S.cleared = 'N'
             THEN 0
             ELSE 1
        END AS action,
        CS.long_desc AS reason
FROM (SELECT people_code_id,
              college,
              ROW_NUMBER() OVER(PARTITION BY people_code_id
                                ORDER BY academic_year DESC) AS rk
       FROM academic
       WHERE primary_flag = 'Y'
         AND academic_flag = 'Y'
         AND academic_year >= YEAR(CURRENT_TIMESTAMP) - 2
         AND academic_year <= YEAR(CURRENT_TIMESTAMP)
         AND academic_year <> '+'
         AND academic_year <> '.'
         AND academic_session = ''
         AND degree <> 'NONDEG'
         AND academic_session <> 'WKSHP') AS A
JOIN address AS C
   ON A.people_code_id = C.people_org_code_id
JOIN people AS p
   ON P.people_code_id = C.people_org_code_id
  AND P.preferred_add = C.address_type
JOIN peopletype AS pt
   ON P.people_code_id = PT.people_code_id
LEFT OUTER JOIN stoplist AS S
   ON P.people_code_id = S.people_code_id
  AND S.stop_reason IN ('AD', 'AS', 'EX', 'READ')
  AND S.cleared = 'N'
LEFT OUTER JOIN code_stoplist AS CS
   ON S.stop_reason = CS.code_value_key
WHERE PT.people_type = 'STUD'
   AND A.rk = 1;

This worked great.  Thanks so much.

ORDER BY @OrderBy problem
Testing Environment + TestData + QA Setup
Restoring single filegroup
Intermittent and undesired SQL execution performance behavior
where to find the errors generated by stored procedures
Migrating SQL2000 Databases to SQL2005
64 bit standard edition doesn't use page file
Transaction Log Backup Doesn't Truncate Log
SSMS Sometimes shows keys - sometimes not
Change autogrowth for a log file SQL 2005