We help IT Professionals succeed at work.
Get Started

SQL 2000 - Order by Question.

189 Views
Last Modified: 2021-04-21
Please note, there are items in my where clause that will look odd to you .... please disregard them, the SQL is handled dynamically by the SQL server.

My client has requested to order the fac.listname in a distinct manner. Is this possible? I know I can do it in ASC and DESC order, but not by a specific manner. Any help is appreciated. If possible, could I get it in a seperate field so I can use that field in Crystal to Group off .... ??

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
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'))
	)

Open in new window

Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 16 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE