Return Result Set no right - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Return Result Set no right

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-02-21 : 19:25:17

Right now, I'm getting back results; however I am getting 01/01/1900 in the Effective and Termination fields if no values were present in the Database(null). Is it possible to have it return a blank versus returning this result? I thought my ISNULL in front of these fields would have fixed that. I tried the case statement too (which you see is commented out because I couldnt get that to work as well.

Any suggestions or thoughts?


/* Patient Insurance List Revised to Patient Insurance from Visit */

SET NOCOUNT ON

CREATE TABLE #PRIMARY
	(
	PatientName VARCHAR(200),
	PatientID VARCHAR(50),
	TicketNumber VARCHAR(50),
	DateOfService DATETIME,
	DoctorName VARCHAR(200),
	FacilityName VARCHAR(200),
	FinancialClass VARCHAR(200),
	CompanyName VARCHAR(200), 
  	VisitInsBalance MONEY,
  	VisitPatBalance MONEY,
  	TotalVisitBalance MONEY,
	PatientAddress VARCHAR(500)NULL, 
	PatientCSZ VARCHAR(200)NULL,
	PhoneNumber VARCHAR(50)NULL,
	PhoneType VARCHAR(50)NULL,
	PatientSSN VARCHAR(50)NULL,
	BirthDate DATETIME NULL, 
	PatientSex VARCHAR(50)NULL,
     	GuarantorName VARCHAR(200)NULL,
	GuarantorAddr VARCHAR(400)NULL,
	GuarantorCSZ VARCHAR(400)NULL,
	PrimaryInsFirstName VARCHAR(200)NULL,
	PrimaryInsLastName VARCHAR(200)NULL,
	PrimaryInsAddr1 VARCHAR(200)NULL,
	PrimaryInsAddr2 VARCHAR(200)NULL,
	PrimaryInsCity VARCHAR(100)NULL, 
	PrimaryInsState VARCHAR(100)NULL,
	PrimaryInsZip VARCHAR(20)NULL,
	PrimaryInsCarrier VARCHAR(200)NULL,
	PrimaryInsInsuredID VARCHAR(100)NULL,
	PrimaryInsGroupID VARCHAR(100)NULL,
	PrimaryInsEffDate DATETIME NULL, 
        PrimaryInsTermDate DATETIME NULL
)

INSERT INTO #PRIMARY
  	(PatientName,PatientID,TicketNumber,DateOfService,DoctorName,FacilityName,FinancialClass,CompanyName,VisitInsBalance,VisitPatBalance,TotalVisitBalance,PatientAddress,PatientCSZ,
	PhoneNumber,PhoneType,PatientSSN,BirthDate,PatientSex,GuarantorName,GuarantorAddr,GuarantorCSZ,PrimaryInsFirstName,PrimaryInsLastName,
	PrimaryInsAddr1,PrimaryInsAddr2,PrimaryInsCity,PrimaryInsState,PrimaryInsZip,PrimaryInsCarrier,PrimaryInsInsuredID,PrimaryInsGroupID,PrimaryInsEffDate,PrimaryInsTermDate)

SELECT DISTINCT
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [PatientName], 
	  pp.patientid AS [PatientID],
	  pv.TicketNumber AS [TicketNumber], 
	  pv.Visit AS [DateOfService], 
	  df.ListName AS [DoctorName], 
	  df1.ListName AS [FacilityName], 
	  ml.Description AS [FinancialClass],
	  df2.ListName AS [CompanyName], 
  	  pva.insbalance AS [VisitInsBalance],
  	  pva.patbalance AS [VisitPatBalance],
  	  pva.insbalance + pva.patbalance AS [TotalVisitBalance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [PatientAddress],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [PatientCSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS [PhoneNumber], 
	  ISNULL(pp.Phone1Type, ' ') AS [PhoneType], 
	  ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS [PatientSSN],
	  CONVERT(varchar,pp.Birthdate,101) AS [BirthDate],
	  CASE WHEN pp.Sex = 'M' THEN 'Male'WHEN pp.Sex = 'F' THEN 'Female' ELSE IsNull(pp.Sex,'')END AS [PatientSex],
          g.[Last] + ', ' + g.[First] AS [GuarantorName], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [GuarantorAddr],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [GuarantorCSZ], 

          CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.First
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.first
		ELSE IsNull(pins1.[First],'')END AS [PrimaryInsFirstName],

	  CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.Last
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.Last
		ELSE IsNull(pins1.[Last],'') END AS [PrimaryInsLastName],

	  CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.Address1
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.address1
		ELSE IsNull(pins1.address1,'') END AS [PrimaryInsAddr1],

	  CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.Address2
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.Address2
		ELSE IsNull(pins1.address2,'')END AS [PrimaryInsAddr2],

	  CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.City
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.City
		ELSE IsNull(pins1.city,'')END AS [PrimaryInsCity],

	  CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.State
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.State
		ELSE IsNull(pins1.State,'')END AS [PrimaryInsState],

	  CASE  WHEN COALESCE(pins1.InsuredSameAsPatient,1) = 1 THEN pp.Zip
		WHEN COALESCE(pins1.InsuredSameAsGuarantor,1) = 1 THEN g.Zip
		ELSE IsNull(pins1.zip,'')END AS [PrimaryInsZip],

       	ic1.ListName AS [PrimaryInsCarrier], 
	ISNULL(pins1.InsuredId, '') AS [PrimaryInsInsuredID], 
	ISNULL(pins1.groupid,'')AS [PrimaryInsGroupID],
	isnull(convert(varchar,pins1.inscardeffectivedate,101),'')AS [PrimaryInsEffDate],
	isnull(convert(varchar,pins1.inscardterminationdate,101),'')AS [PrimaryInsTermDate]
-- 	CASE WHEN pins1.inscardeffectivedate IS NULL THEN 'No Effective Date'
-- 	ELSE cast(pins1.InsCardeffectiveDate as varchar) END AS [PrimaryInsEffDate],
-- 	CASE WHEN pins1.inscardterminationdate IS NULL THEN 'No Term Date'
-- 	ELSE CAST(pins1.inscardterminationdate AS varchar)END AS [PrimaryInsTermDate]

FROM    PatientVisit pv
	LEFT JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN insurancecarriers ic1 ON pv.primaryinsuranceCarriersid = ic1.insurancecarriersid 
	LEFT JOIN patientinsurance pins1 on pv.patientprofileID = pins1.patientprofileID and pins1.orderforclaims=1
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	LEFT JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId  
	LEFT JOIN DoctorFacility df2 ON pv.CompanyId = df2.DoctorFacilityId 
	LEFT JOIN MedLists ml ON pv.FinancialClassMId = ml.MedListsId

WHERE  --- Filter on Doctor
	(
	(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --- Filter on Facility
	(
	(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Primary Carrier
	(
	(NULL IS NOT NULL AND pins1.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND -- Filter on Primary Insurance Termination Date
	(
	(pins1.InsCardTerminationDate IS NULL OR (pins1.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins1.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Primary Insurance Effective Date
	(
	 (pins1.InsCardEffectiveDate IS NULL OR (pins1.InsCardEffectiveDate >= ISNULL(NULL, '01/01/1900') AND pins1.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)
	 AND  --Filter on Patient 
        ( 
        (NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR 
        (NULL IS NULL) 
        ) 

CREATE TABLE #SECONDARY
	(
	PatientName VARCHAR(200),
	PatientID VARCHAR(50),
	TicketNumber VARCHAR(50),
	DateOfService DATETIME,
	DoctorName VARCHAR(200), 
	FacilityName VARCHAR(200),
	FinancialClass VARCHAR(200),
	CompanyName VARCHAR(200),
 	VisitInsBalance MONEY,
 	VisitPatBalance MONEY,
 	TotalVisitBalance MONEY,
	PatientAddress VARCHAR(500)NULL,
	PatientCSZ VARCHAR(400)NULL,
	PhoneNumber VARCHAR(50)NULL,
	PhoneType VARCHAR(50)NULL,
	PatientSSN VARCHAR(50)NULL,
	BirthDate DATETIME NULL,
	PatientSex VARCHAR(50)NULL,
        GuarantorName VARCHAR(200)NULL,
	GuarantorAddr VARCHAR(500)NULL,
	GuarantorCSZ VARCHAR(400)NULL,
	SecondaryInsFirstName VARCHAR(200)NULL,
	SecondaryInsLastName VARCHAR(200)NULL,
	SecondaryInsAddr1 VARCHAR(200)NULL,
	SecondaryInsAddr2 VARCHAR(200)NULL,
	SecondaryInsCity VARCHAR(100)NULL, 
	SecondaryInsState VARCHAR(100)NULL,
	SecondaryInsZip VARCHAR(20)NULL,
	SecondaryInsCarrier VARCHAR(200)NULL, 
	SecondaryInsInsuredID VARCHAR(100)NULL,
	SecondaryInsGroupID VARCHAR(100)NULL,
	SecInsEffDate DATETIME NULL, 
        SecInsTermDate DATETIME NULL
)

INSERT INTO #SECONDARY 
	(PatientName,PatientID,TicketNumber,DateOfService,DoctorName,FacilityName,FinancialClass,CompanyName,VisitInsBalance,VisitPatBalance,TotalVisitBalance,PatientAddress,PatientCSZ,PhoneNumber,PhoneType,
	PatientSSN,BirthDate,PatientSex,GuarantorName,GuarantorAddr,GuarantorCSZ,SecondaryInsFirstName,SecondaryInsLastName,SecondaryInsAddr1,SecondaryInsAddr2,
	SecondaryInsCity,SecondaryInsState,SecondaryInsZip,SecondaryInsCarrier,SecondaryInsInsuredID,SecondaryInsGroupID,SecInsEffDate,SecInsTermDate)

SELECT DISTINCT  
	  dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [PatientName], 
	  pp.patientid AS [PatientID],
	  pv.TicketNumber AS [TicketNumber], 
	  pv.Visit AS [DateOfService], 
	  df.ListName AS [DoctorName], 
	  df1.ListName AS [FacilityName], 
	  ml.Description AS [FinancialClass],
	  df2.ListName AS [CompanyName], 
 	  pva.insbalance AS [VisitInsBalance],
 	  pva.patbalance AS [VisitPatBalance],
 	  pva.insbalance + pva.patbalance AS [TotalVisitBalance],
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [PatientAddress],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [PatientCSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS [PhoneNumber], 
	  ISNULL(pp.Phone1Type, ' ') AS [PhoneType], 
	  ISNULL(SUBSTRING(pp.SSN,1,3) +  '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'')AS [PatientSSN],
	  CONVERT(varchar,pp.Birthdate,101) AS [BirthDate],
	  CASE WHEN pp.Sex = 'M' THEN 'Male'WHEN pp.Sex = 'F' THEN 'Female' ELSE IsNull(pp.Sex,'')END AS [PatientSex],
          g.[Last] + ', ' + g.[First] AS [Guarantor Name], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [GuarantorAddr],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [GuarantorCSZ], 
		
	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.First
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.First
		ELSE IsNull(pins2.[First],'')END AS [SecondaryInsFirstName],

	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.Last
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.Last
		ELSE IsNull(pins2.[Last],'')END AS [SecondaryInsLastName],

	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.Address1
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.Address1
		ELSE IsNull(pins2.address1,'')END AS [SecondaryInsAddr1],

	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.Address2
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.Address2
		ELSE IsNull(pins2.address2,'')END AS [SecondaryIns Addr2],

	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.City
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.City
		ELSE IsNull(pins2.city,'')END AS [SecondaryInsCity],

	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.State
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.State
		ELSE IsNull(pins2.State,'')END AS [SecondaryInsState],

	  CASE  WHEN COALESCE(pins2.InsuredSameAsPatient,1) = 1 THEN pp.Zip
		WHEN COALESCE(pins2.InsuredSameAsGuarantor,1) = 1 THEN g.Zip
		ELSE IsNull(pins2.zip,'')END AS [SecondaryInsZip],

        ISNULL(ic2.ListName,'No Secondary Ins') AS [SecondaryInsCarrier], 
	ISNULL(pins2.InsuredId,'N/A') AS [SecondaryInsInsuredID], 
	ISNULL(pins2.groupid,'N/A')AS [SecondaryInsGroupID],   
	isnull(convert(varchar,pins2.InsCardEffectiveDate,101),'')AS [SecInsEffDate],
	isnull(convert(varchar,pins2.InsCardTerminationDate,101),'')AS [SecInsTermDate]
--  	ISNULL(pins2.InsCardEffectiveDate,'') AS [SecInsEffDate], 
--      ISNULL(pins2.InsCardTerminationDate,'') AS [SecInsTermDate]

FROM    PatientVisit pv
	LEFT JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN patientinsurance pins2 on pv.patientprofileID = pins2.patientprofileID and pins2.orderforclaims=2
	LEFT JOIN insurancecarriers ic2 on pins2.insurancecarriersID = ic2.insurancecarriersID
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	LEFT JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId  
	LEFT JOIN DoctorFacility df2 ON pv.CompanyId = df2.DoctorFacilityId 
	LEFT JOIN MedLists ml ON pv.FinancialClassMId = ml.MedListsId

WHERE  --- Filter on Doctor
	(
	(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --- Filter on Facility
	(
	(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on secondary Carrier
	(
	(NULL IS NOT NULL AND pins2.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND -- Filter on Secondary Insurance Termination Date
	(
	(pins2.InsCardTerminationDate IS NULL OR (pins2.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins2.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Secondary Insurance Effective Date
	(
	 (pins2.InsCardEffectiveDate IS NULL OR (pins2.InsCardEffectiveDate >= ISNULL(NULL, '01/01/1900') AND pins2.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)
	 AND  --Filter on Patient 
        ( 
        (NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR 
        (NULL IS NULL) 
        ) 

ORDER BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix)

select * from #PRIMARY
select * from #SECONDARY

DROP TABLE #PRIMARY
DROP TABLE #SECONDARY

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 20:06:31

ISNULL basically checks for NULL value and return the specified value instead of the NULL.

if you don't want records to return if the effectivedate is null then just remove that condition from the where clause.


	AND -- Filter on Secondary Insurance Termination Date
	(
	(pins2.InsCardTerminationDate IS NULL OR (pins2.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pins2.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)

ISNULL(NULL,'01/01/1900')

what are you doing here ? the isnull is redundant.
Just remove that. Also, use universal date format YYYYMMDD


pins2.InsCardTerminationDate >= '19000101'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources