SQL 2005 - Sub-Query Assistance Needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 - Sub-Query Assistance Needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-01-02 : 13:19:00

Please note, the SQL is handled dynamically by the SQL server. Alot that is with-in the Where clauses will look odd to you, please disregard them as they are not an issue.

The query in my code snippet below works in its present state. It returns exactly what I ask and intend. (Please see below the code for what I need).


SET NOCOUNT ON
 
DECLARE @DOBFrom datetime, @DOBTo datetime
 
IF 1 = 1
   BEGIN
	SET @DOBFrom = getdate() - (100 * 365.25)
	IF 1 = 1 
	      BEGIN	
		SET @DOBTo = getdate() - ((1-1) * 365.25)
	      END
	ELSE
	      BEGIN
		SET @DOBTo = getdate() - (1 * 365.25)
	      END
   END
ELSE
   BEGIN
                IF NULL = NULL
                        BEGIN
		SET @DOBFrom = getdate() - (365.25*110)
		SET @DOBTo = getdate()
	        END
	ELSE
	        BEGIN
		SET @DOBFrom = NULL
		SET @DOBTo = NULL
	        END
   END
 
DECLARE @maxamt money,
    	  @minamt money
 
if 1 = '1'
begin
    	set @minamt = -99999999.00
    	set @maxamt = 999999999.00
end
if 1 = '2'
begin
    	set @minamt = -9999999.00
    	set @maxamt = NULL
end
if 1 = '3'
begin
    	set @minamt = NULL
    	set @maxamt = 99999999.00
end
if 1 = '4'
begin
    	set @minamt = NULL
    	set @maxamt = NULL
end
 
DECLARE @insmaxamt money,
    	  @insminamt money
 
if 1 = '1'
begin
    	set @insminamt = -99999999.00
    	set @insmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @insminamt = -9999999.00
    	set @insmaxamt = NULL
end
if 1 = '3'
begin
    	set @insminamt = NULL
    	set @insmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @insminamt = NULL
    	set @insmaxamt = NULL
end
 
CREATE TABLE #Bill
	(
	patientvisitid int
	)
INSERT #Bill
 
SELECT  distinct PatientVisit.patientvisitid
FROM    PatientVisit 
	INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId 
	LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId 
	INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId 
	INNER JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId 
	INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId 
	LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId 
	LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId 
	INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId 
	INNER JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId 
	LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId 
	LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder 
	INNER JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
	
WHERE     (MedLists_1.TableName = 'BillStatus')
	AND  --Filter on CPT Code
	(
	(NULL IS NOT NULL AND PatientVisitProcs.ProceduresId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND PatientVisit.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Diagnosis
	(
	(NULL IS NOT NULL AND PatientVisitDiags_0.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_1.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_2.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_3.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_4.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_5.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_6.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_7.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_8.DiagnosisId IN (NULL)) OR 
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND PatientVisit.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on pat sex
	(
	(Null IS NOT NULL AND PatientProfile.sex in (Null)) OR
	(Null IS NULL)
	)
	AND  --Filter on Date
	(
	(1 = 1 AND (PatientVisit.Entered >= ISNULL(NULL, '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))                 OR
	(1 = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom <                                                                	DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))
	)
	AND  --Filter on DOBDate
	(
	PatientProfile.Birthdate >= @DOBFrom  AND PatientProfile.Birthdate <= @DOBTo
	)
	AND  --Filter on Insurance Carrier
	(
	(NULL IS NOT NULL AND PatientVisit.PrimaryInsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on patient balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.PatBalance <= @maxamt) OR
	(1 = 3 AND PatientVisitAgg.PatBalance >= @minamt) OR
	(1 = 4 AND PatientVisitAgg.PatBalance >= @minamt AND PatientVisitAgg.PatBalance <= @maxamt)
	)
	AND  --Filter on Insurance balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance <> 0 AND PatientVisitAgg.InsBalance <= @insmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance >= @insminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance >= @insminamt AND PatientVisitAgg.InsBalance <= @insmaxamt)
	)
	AND --Filter on patient
	(
	('365' IS NOT NULL AND  PatientProfile.PatientProfileID IN (365)) OR
	('365' IS NULL)
	)
 
SELECT DISTINCT	
	PatientVisit.TicketNumber AS [Ticket Number], 
       	PatientVisitProcs.DateOfServiceFrom AS [Date Of Service], 
	DoctorFacility.ListName AS Doctor, 
	ISNULL(InsuranceCarriers.ListName, '') AS [Insurance Carrier], 
	ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')AS [Ins Address],
	--MedLists.Description AS [Financial Class], 
	--ISNULL(InsuranceGroup.Name, '') AS [Insurance Group], 
	dbo.FormatName(PatientProfile.Prefix, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last, PatientProfile.Suffix) AS [Patient Name], 
	patientprofile.PatientId, 
	patientprofile.PatientProfileID, 
	ISNULL(patientprofile.medicalrecordnumber,'No MRN#')as [Medical Record Number],
	ISNULL(patientprofile.address1,'') + '' + ISNULL(patientprofile.address2, '') + ' ' + ISNULL(patientprofile.city,'') + ' ' + ISNULL(patientprofile.state,'') + ' ' + ISNULL(patientprofile.zip,'') AS [Patient Address],
	ISNULL(patientprofile.Phone1,'No Phone')as PatPhone,
	ISNULL(Convert(VarChar(20), patientprofile.birthdate, 101),'No DOB')as PatientDOB,
	datediff(YYYY, isnull(patientprofile.birthdate, getdate()), getdate())AS [Patient Age],
	'Age @ TOS' = CASE
     	WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom) <= 0
        	THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Month(s)'
        	ELSE CAST(DATEDIFF(YY,patientprofile.birthdate, patientvisitprocs.dateofservicefrom)as varchar(10))+ ' Yr(s)'
       	END,
	ISNULL(patientprofile.sex,'')as PatSex,
	PatientVisit.Entered AS [Date Of Entry], 
	DoctorFacility_1.ListName AS Facility, 
     	MedLists_1.Description AS [Visit Status], 
	PatientVisitProcs.TotalFee AS Fee, 
	PatientVisitProcs.CPTCode AS [CPT Code], 
	PatientVisitProcs.Units AS Units,
	MedLists_2.Code AS PlaceOfService, 
	MedLists_3.Code AS TypeOfService, 
	ISNULL(CONVERT(varchar(4), MedLists_4.Code), ' ') 
	+ ' ' + ISNULL(CONVERT(varchar(4), MedLists_5.Code), ' ') 
	+ ' ' + ISNULL(CONVERT(varchar(4), MedLists_6.Code), ' ')
	+ ' ' + ISNULL(CONVERT(varchar(4), MedLists_7.Code), ' ') AS Modifier, 
	PatientVisitProcsAgg.InsPayment, 
	PatientVisitProcsAgg.PatPayment, 
	PatientVisitProcsAgg.InsAdjustment, 
	PatientVisitProcsAgg.PatAdjustment, 
	PatientVisitProcsAgg.InsBalance, 
	PatientVisitProcsAgg.PatBalance,
	PatientVisitProcsAgg.InsBalance + PatientVisitProcsAgg.PatBalance AS TotalBalance, 
	MedLists_8.Description AS Department,
	DoctorFacility_2.ListName AS Company, 
	ISNULL(PatientVisitDiags_0.Code,'') AS Diag1, 
	ISNULL(PatientVisitDiags_1.Code,'') AS Diag2, 
	ISNULL(PatientVisitDiags_2.Code,'') AS Diag3, 
	ISNULL(PatientVisitDiags_3.Code,'') AS Diag4,
	ISNULL(PatientVisitDiags_4.Code,'') AS Diag5,
	ISNULL(PatientVisitDiags_5.Code,'') AS Diag6,
	ISNULL(PatientVisitDiags_6.Code,'') AS Diag7,
	ISNULL(PatientVisitDiags_7.Code,'') AS Diag8,
	CASE
	WHEN 0 = 1 THEN DoctorFacility.ListName
	WHEN 0 = 2 THEN DoctorFacility_1.ListName
	WHEN 0 = 3 THEN DoctorFacility_2.ListName
	WHEN 0 = 4 THEN ISNULL(InsuranceCarriers.ListName,'No Carrier') + ' ' + ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')
	ELSE NULL
	END AS GROUPING
 
FROM    PatientVisit 
	INNER JOIN #Bill b ON PatientVisit.PatientVisitId = b.PatientVisitId
	INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId 
	LEFT JOIN InsuranceCarriers ON PatientVisit.PrimaryInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId 
	LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId 
	INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId 
	INNER JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId 
	INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId 
	LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId 
	LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId 
	INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId 
	INNER JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId 
	LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId 
	LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder 
	INNER JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
WHERE     (MedLists_1.TableName = 'BillStatus')
ORDER BY [Patient Name]ASC
DROP TABLE #Bill

What I want to do is introduce the following into my report:


PatientCorrespondence.Created AS [Date Created],
PatientCorrespondence.CreatedBy AS [Created By], 
 CASE PatientCorrespondence.Description
WHEN '**long**' THEN cast(PatientCorrespondence.DescriptionLong as varchar(8000)) 
 ELSE PatientCorrespondence.Description
END AS Notes

The issue I have has to do with how the patientvisit table hooks into the patientvisitprocs table. Its kicking back the same note for every procedure I have, which lead me to believe I needed a sub-query.

I took one specific patientvisitId to get back what I needed in a seperate query. Whats in this code snippet is what I need to add into my main query. If anyone can help that would be AWESOME.


SELECT DISTINCT
    PatientCorrespondence.Created AS [Date Created] ,
    PatientCorrespondence.CreatedBy AS [Created By] ,
    CASE PatientCorrespondence.Description
      WHEN '**long**' THEN CAST(PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
      ELSE PatientCorrespondence.Description
    END AS Notes
FROM
    PatientVisit
    LEFT JOIN PatientCorrespondence ON PatientVisit.PatientVisitId = PatientCorrespondence.PatientVisitId
WHERE
    PatientVisit.PatientVisitId = '846'
ORDER BY
    [Date Created] DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:22:56

whats the purpose of those trivial if conditions? 1=1,null=null,...

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-01-02 : 13:34:37

Please see inital post - SQL Handled Dynamically. All I need help on is the sub-query's. It looks odd to you I know, however its not the issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources