SQL 2000 - Order by Question.

SELECT DISTINCT
      crii.PP_Last,
      crii.PP_First,
      crii.PP_Middle,
      crii.PP_Birthdate,
      crii.PP_Address1,
      crii.PP_Phone1,
      pvp.DateOfServiceFrom,
      pp.MedicalRecordNumber,
      pvd.ICD9Code,
      fac.ListName as Facility,
      df.ListName as Doctor
FROM  
      PatientVisit pv
      INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
      INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
      INNER JOIN cusCRIInterview crii ON pv.PatientProfileId = crii.PatientProfileID
      INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
      INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
      INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
WHERE
      (
      (NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
      (NULL IS NULL)
      )
      AND
      (
      (NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
      (NULL IS NULL)
      )
      AND
      (
      pvp.DateOfServiceFrom >= ISNULL('01/01/2008','1/1/1900') AND
      pvp.DateOfServiceFrom < dateadd(month, 1, ISNULL('01/29/2009','1/1/3000'))
      )

ORDER BY  fac.listname ASC

Special order for field fac.ListName. All Others are discarded:

1H - Hillman
2V - Visalia
3L - Lindsay
4D - Dinuba
5F - Farmersville
6P - Porterville
7H - Tulare Peds
I only want these facilities ... I have others I need to exclude.

This is a very interesting WHERE statement:

      (
      (NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
      (NULL IS NULL)
      )
      AND
      (
      (NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
      (NULL IS NULL)
      )

What in the world are you trying to do here?  Can't you just do this:

pv.DoctorID IS NULL OR pv.FacilityId IS NULL

Chapmandew ....
Please read my original post ... I know it looks odd ... its handled dynamically.

Brandon -
I think your on the right track ... testing it all now. Why the Order by 12? What is 12?

Whoops...sorry about that.

12 is the 12th column.

You can order by "column #" when it is an alias, or the column name appears twice in the select statement.  For example:

Let's say you have a 50 column table and you want to order by, and see the right most column on the far left.

select Value1,* from MyTable
order by value1

That will produce an error about ambiguous column name because value1 is in the * as well.  But who wants to list out ALL 50 columns in the select list

select Value1,* from MyTable
order by 1

You can use the "order by #" syntax where # is the position of the desired sort column in the select list.

chapmandew ....
Trust me ... I hate it too and its a pain to mess with. Its also something I have to explain every time I post for help.

Brandon -
You just taught me a cool trick ... very cool. I'll let you know on my testing how it goes.

Try out the below code tuned to some extent:

Uncomment the two places as required.

SELECT 
--DISTINCT 
        crii.PP_Last, 
        crii.PP_First, 
        crii.PP_Middle, 
        crii.PP_Birthdate, 
        crii.PP_Address1, 
        crii.PP_Phone1, 
        pvp.DateOfServiceFrom, 
        pp.MedicalRecordNumber, 
        pvd.ICD9Code, 
        CASE when fac.ListName = 'Hillman' THEN '1H'
             when fac.ListName = 'Visalia' THEN '2V'
             when fac.ListName = 'Lindsay' THEN '3L'
             when fac.ListName = 'Dinuba' THEN '4D'
             when fac.ListName = 'Farmersville' THEN '5F'
             when fac.ListName = 'Porterville' THEN '6P'
             when fac.ListName = 'Tulare Peds' THEN '7H'
         ELSE NULL
         END AS Facility, 
        df.ListName as Doctor
FROM   
        PatientVisit pv 
        INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId 
        INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
        INNER JOIN cusCRIInterview crii ON pv.PatientProfileId = crii.PatientProfileID 
        INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId 
        INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
WHERE pv.FacilityId is NULL
  AND pv.DoctorID IS NULL
--  AND fac.ListName in ('Hillman', 'Visalia', 'Lindsay', 'Dinuba', 'Farmersville', 'Porterville','Tulare Peds')
  AND pvp.DateOfServiceFrom >= '01/01/2008'
  AND pvp.DateOfServiceFrom < dateadd(month, 1,'01/29/2009')
order by 10

Select allOpen in new window

Scott,
I learned yet another cool thing.
"You don't have to actually SELECT that value to sort by it :-) ."
I am now getting this message. If I remove the DISTINCT keyword I get results, but I need them Distinct.
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

OOPS, sorry, forgot about that.  If you specify DISTINCT, yes, you will have to SELECT the value.

Thanks!