Help to understand a query

Transact-SQL
1

Can someone please help me to understand this query. It works and I get results, however, what I really would like help with is a breakdown of the SQL statements and what they do exactly. I have to start creating these types of queries but I am at an absolute loss as to what they mean or how to put them together.

  DECLARE 
 @StartDate Datetime = '2014-11-01'
 , @EndDate DateTime = '2014-12-01'
 
 SELECT 
 PatientProfile.PatientId
 , PatientProfile.searchname as PatientName
 , HistoricalInfo.ChangeDate RespProviderSince
 , DoctorFacility.ListName RespProvider
 , DoctorFacility_1.ListName LastRespProvider
 
 INTO #LastResp
 
 FROM   
 CentricityPS.dbo.PatientProfile PatientProfile
	INNER JOIN CentricityPS.dbo.HistoricalInfo HistoricalInfo
		ON HistoricalInfo.PatientProfileId=PatientProfile.PatientProfileId
		AND HistoricalInfo.ChangeDate =
			(SELECT MAX(ChangeDate)
				FROM HistoricalInfo hi
					WHERE hi.PatientProfileId = HistoricalInfo.PatientProfileId
			)
	INNER JOIN CentricityPS.dbo.DoctorFacility DoctorFacility_1 
		ON HistoricalInfo.DoctorId=DoctorFacility_1.DoctorFacilityId
	INNER JOIN CentricityPS.dbo.DoctorFacility DoctorFacility 
		ON PatientProfile.DoctorId=DoctorFacility.DoctorFacilityId
 WHERE  PatientProfile.searchname NOT  LIKE '<MRG>%' 
 AND HistoricalInfo.DoctorId<>-1 
 AND HistoricalInfo.WhatChange=262144
 ORDER BY PatientProfile.PatientId
  

 SELECT DISTINCT 
 PatientProfile.PatientId
 , PatientProfile.searchname as PatientName
 , Appointments.ApptStart
 , ApptType.Name as VisitType
 , DoctorFacility_1.ListName as VisitProvider
 , DoctorFacility.ListName as PCP
 
 INTO #Appts
 
 FROM   
 CentricityPS.dbo.PatientProfile PatientProfile 
	LEFT JOIN CentricityPS.dbo.DoctorFacility DoctorFacility 
		ON PatientProfile.DoctorId=DoctorFacility.DoctorFacilityId
	LEFT JOIN CentricityPS.dbo.Appointments Appointments 
		ON PatientProfile.PatientProfileId=Appointments.OwnerId
	LEFT JOIN CentricityPS.dbo.ApptType ApptType 
		ON Appointments.ApptTypeId=ApptType.ApptTypeId
	LEFT JOIN CentricityPS.dbo.DoctorFacility DoctorFacility_1 
		ON Appointments.ResourceId=DoctorFacility_1.DoctorFacilityId
	LEFT JOIN CentricityPS.dbo.MedLists ApptStatus 
		ON Appointments.ApptStatusMId=ApptStatus.MedListsId
	LEFT JOIN CentricityPS.dbo.DoctorFacility DoctorFacility_2 
		ON Appointments.FacilityId=DoctorFacility_2.DoctorFacilityId
	LEFT JOIN #LastResp lr
		ON lr.PatientId = PatientProfile.PatientId
 WHERE  Appointments.ApptStart>= @StartDate
	AND Appointments.ApptStart< @EndDate
	AND Appointments.Status<>'Scheduled' 
	AND ApptStatus.Description IN 
		(
		'Arrived'
		,'Check Out'
		,'Completed'
		,'EMR Duplicate'
		,'Waiting for Provider'
		,'Waiting Room'
		,'With Provider'
		) 
	AND DoctorFacility_2.ListName IN 
		(
		'Family Practice - Gloucester' 
		,'Family Practice - Peabody' 
		,'Family Practice - Salem'
		) 
	AND  NOT ApptType.Name IN
		(
		'Behavioral Health - 30' 
		,'Behavioral Health 45' 
		,'Behavioral Health Initial - 30' 
		,'Fin Counsel - 15' 
		,'Fin Counsel - 30' 
		,'Fin Counsel 45 min' 
		,'Finacial Couinseling 45' 
		,'Financial Counseling' 
		,'Financial Counseling 45' 
		,'Financial Counseling Peabody 45' 
		,'Financial Counselor Family Visit' 
		,'Follow up Financial Counselors' 
		,'Lab Visit - 15' 
		,'Nurse Booked Visit - 15' 
		,'Nurse Booked Visit - 20' 
		,'Nurse Booked Visit - 30' 
		,'Nurse Booked Visit - 40' 
		,'Nurse Only Book - 15' 
		,'Nurse Only Book - 20' 
		,'Nurse Visit - 15' 
		,'Nurse Visit - 30' 
		,'Nurse Visit Only' 
		,'sbx visit'
		)
	AND (lr.RespProviderSince IS NULL OR Appointments.ApptStart >= lr.RespProviderSince) --either no change, or change in PCP was before the encounter

 ORDER BY DoctorFacility.ListName, PatientProfile.PatientId, Appointments.ApptStart DESC
 
 
 
 
 SELECT 
 VisitProvider 
 , CAST(SUM(CASE WHEN PCP = VisitProvider THEN 1 ELSE 0 END) AS FLOAT) / COUNT(ApptStart) as PercentVisitWithPCP
 , SUM(CASE WHEN PCP = VisitProvider THEN 1 ELSE 0 END) as VisitsWithPCP
 , COUNT(ApptStart) as TotalVisits
 
 from #Appts
 GROUP BY VisitProvider
 HAVING (SUM(CASE WHEN PCP = VisitProvider THEN 1 ELSE 0 END) > 0)
 ORDER BY VisitProvider
 
 DROP TABLE #Appts, #LastResp

Thank you.

2

A full explanation might be longer than the query itself! Especially since we can't tell from your question how much you know already.

Do you have a specific question about some (small) part of the query?

Otherwise the best way of explaining this query is to suggest that you find and follow any of the many excellent sources for learning SQL.

3

I appreciate your remarks and thank you for at least responding to my question.

What I know is Select, From, Order by, Group By and most of the basics. What I don't know is CASE, if joins need to be added in logical order when using multiple tables, or some of the more complicated query writing.

I do have books on SQL however I have found most books give you the most rudimentary of information. I have been unable to find any books that help with more advance query writing. I thought coming to the forums would be helpful but it seems that I am pissing people off when I may not understand their questions.

Anyway, thank you again for responding to my question.

4

Here's a great series of articles to help Beyond the basics

About CASE:

A case statement is like a series of IFs, or a switch statement in other languages, When I write:

SELECT 
    CASE WHEN a = 1
            THEN 'a is one'
         WHEN A = 2
            THEN 'a is two'
         ELSE
                 ' a is something else'
   END as What_Is_a
FROM mytable

I am asking SQL to look at column 'a' in my table and return one of the three strings in the example, depending on the value of 'a'.