Reporting NULL values only - need all reported 2

[jeffsturgeon2002]

Currently, I am only receiving patients back that have NULL values for the "Termination Date" and "Effective Date". If either one of these fields has a value in it, I'm not receiving them back in my report. The ?DATES.DATE2? will be replaced by actual dates from the end user.

SECTION OF CODE THAT NEEDS ATTENTION

AND -- Filter on Termination Date
	(
	(pi.InsCardTerminationDate IS NULL OR pi.InsCardTerminationDate < DATEADD(d,1,?DATES.DATE2?)) AND (pi.InsCardTerminationDate IS NULL OR pi.InsCardTerminationDate >= ?DATES.DATE1?)
	)
	AND -- Filter on Effective Date
	(
	(pi.InsCardEffectiveDate IS NULL OR pi.InsCardEffectiveDate < DATEADD(d,1,?DATE.DATE2?)) AND (pi.InsCardEffectiveDate IS NULL OR pi.InsCardEffectiveDate >= ?DATE.DATE1?) 
	)

FULL QUERY

/* Patient Insurance List */

SET NOCOUNT ON
SELECT    dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name, 
	  pp.patientid,
	  ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
	  ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ], 
	  IsNull(dbo.formatphone(pp.Phone1,1),'') AS Phone, 
	  ISNULL(pp.Phone1Type, ' ') AS [Phone Type], 
	  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,
	  IsNull(pp.Sex,'')AS PatientSex, 
          	  g.[Last] + ', ' + g.[First] AS [Guarantor Name], 
	  ISNULL(g.Address1,'')+ ' '+ ISNULL(g.address2,'')AS [Guarantor Address],
	  ISNULL(g.city,'')+ ' '+ ISNULL(g.state,'')+' '+ ISNULL(g.zip,'')AS [Guarantor CSZ], 
          	  pp.PatientSameAsGuarantor, 
	  pi.OrderForClaims AS [Order For Claims], 
          	  'Ins First Name'= CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.First
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.First
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.First
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.First
		ELSE IsNull(pi.[First],'')
	END,
	'Ins Last Name'= CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.Last
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.Last
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.Last
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.Last
		ELSE IsNull(pi.[Last],'')
	END,
	'Ins Address 1'= CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.Address1
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.address1
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.address1
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.Address1
		ELSE IsNull(pi.address1,'')
	END,
	'Ins Address 2' = CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.Address2
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.address2
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.address2
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.Address2
		ELSE IsNull(pi.address2,'')
	END,
	'Ins City' = CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.City
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.City
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.City
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.City
		ELSE IsNull(pi.city,'')
	END,
	'Ins State' = CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.State
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.State
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.State
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.State
		ELSE IsNull(pi.State,'')
	END,
	'Ins Zip' =	CASE 
		WHEN pi.InsuredSameAsPatient = 1 THEN pp.Zip
		WHEN pi.InsuredSameAsGuarantor = 1 THEN g.zip
		WHEN pi.InsuredSameAsGuarantor IS NULL THEN g.Zip
		WHEN pi.InsuredSameAsPatient IS NULL THEN pp.zip
		ELSE IsNull(pi.zip,'')
	END,
          	  ic.ListName AS [Insurance Carrier], 
	  ISNULL(pi.InsuredId, ' ') AS [Insured ID], 
	  ISNULL(PI.groupid,'')AS [Group ID],  
	  ig.Name AS InsuranceGroup, 
          	  df.ListName AS Doctor, 
	  df1.ListName AS Facility, 
	  ml.Description AS FinancialClass,
	  ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),'No Effective Date') AS EffectiveDate, 
         	  ISNULL(CONVERT(VARCHAR,pi.InsCardTerminationDate,101),'No Termination Date') AS TerminationDate

FROM    PatientProfile pp
	JOIN PatientInsurance pi ON pp.PatientProfileId = pi.PatientProfileId 
	JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId 
	JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId 
	LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId 
	LEFT JOIN MedLists ml ON pp.FinancialClassMId = ml.MedListsId 
	LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN DoctorFacility df ON pp.DoctorId = df.DoctorFacilityId

WHERE  --Filter on doctor
	(
	(?DOCTOR.ITEMDATA? IS NOT NULL AND pp.DoctorId IN (?DOCTOR.ITEMDATA.U?)) OR
	(?DOCTOR.ITEMDATA? IS NULL)
	)
	AND
	(
	(?FACILITY.ITEMDATA? IS NOT NULL AND pp.FacilityId IN (?FACILITY.ITEMDATA.U?)) OR
	(?FACILITY.ITEMDATA? IS NULL)
	)
	AND  --Filter on Insurance Carrier
	(
	(?INSURANCECARRIER.ITEMDATA? IS NOT NULL AND pi.InsuranceCarriersId IN (?INSURANCECARRIER.ITEMDATA.U?)) OR
	(?INSURANCECARRIER.ITEMDATA? IS NULL)
	)
	AND  --Filter on Insurance Group
	(
	(?INSURANCEGROUP.ITEMDATA? IS NOT NULL AND ic.InsuranceGroupId IN (?INSURANCEGROUP.ITEMDATA.U?)) OR
	(?INSURANCEGROUP.ITEMDATA? IS NULL)
	)
	AND -- Filter on Termination Date
	(
	(pi.InsCardTerminationDate IS NULL OR pi.InsCardTerminationDate < DATEADD(d,1,?DATES.DATE2?)) AND (pi.InsCardTerminationDate IS NULL OR pi.InsCardTerminationDate >= ?DATES.DATE1?)
	)
	AND -- Filter on Effective Date
	(
	(pi.InsCardEffectiveDate IS NULL OR pi.InsCardEffectiveDate < DATEADD(d,1,?DATE.DATE2?)) AND (pi.InsCardEffectiveDate IS NULL OR pi.InsCardEffectiveDate >= ?DATE.DATE1?) 
	)
	 AND  --Filter on Patient 
                ( 
                (?PATIENT.ITEMDATA? IS NOT NULL AND pp.PatientProfileID IN (?PATIENT.ITEMDATA.U?)) OR 
                (?PATIENT.ITEMDATA? IS NULL) 
                ) 

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

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

It sounds like your where clause logic is off. The way you have it layed out,

AND -- Filter on Termination Date
    (
    (pi.InsCardTerminationDate IS NULL OR pi.InsCardTerminationDate < DATEADD(d,1,?DATES.DATE2?)) AND (pi.InsCardTerminationDate IS NULL OR pi.InsCardTerminationDate >= ?DATES.DATE1?)
    )
    [b]AND[/b] -- Filter on Effective Date
    (
    (pi.InsCardEffectiveDate IS NULL OR pi.InsCardEffectiveDate < DATEADD(d,1,?DATE.DATE2?)) AND (pi.InsCardEffectiveDate IS NULL OR pi.InsCardEffectiveDate >= ?DATE.DATE1?) 
    )

One of each of those groups of conditions in the parentheses must be true for a record to be returned. You might want to replace AND with OR, but I do not have the time to figure out your entire where clause to see ramifications of this.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Try:

((pi.InsCardTerminationDate IS NULL OR
 (pi.InsCardTerminationDate >= ?DATES.DATE1? AND pi.InsCardTerminationDate < DATEADD(d,1,?DATES.DATE2?)))
AND
 ((pi.InsCardEffectiveDate IS NULL OR 
  (pi.InsCardEffectiveDate >= ?DATE.DATE1? AND pi.InsCardEffectiveDate < DATEADD(d,1,?DATE.DATE2?))))
)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jeffsturgeon2002]

I am so close to getting this now ... Just encountered one last issue, for which I can not see why its not working right ...

I added a ISNULL(?DATES.DATE1?,'01/01/1900') and DATEADD(d,1, ISNULL(?DATES.DATE2?,'01/01/3000'))) to auto populate the 01/01/1900 and 01/01/3000 values if the fields were NULL. Now Im getting what value I key in and the ISNULL value populating ... I'm sure its more syntax issues ...

	AND -- Filter on Termination Date
	(
	(pi.InsCardTerminationDate IS NULL OR (pi.InsCardTerminationDate >= ISNULL(?DATES.DATE1?,'01/01/1900') AND pi.InsCardTerminationDate < DATEADD(d,1, ISNULL(?DATES.DATE2?,'01/01/3000')))
	)
	AND -- Filter on Effective Date
	(
	 (pi.InsCardEffectiveDate IS NULL OR (pi.InsCardEffectiveDate >= ISNULL(?DATE.DATE1?, '01/01/1900') AND pi.InsCardEffectiveDate < DATEADD(d,1, ISNULL(?DATE.DATE2?,'01/01/3000')))))
	)

IN QUERY ANALYZER

[COLOR=RED]	AND -- Filter on Termination Date
	(
	(pi.InsCardTerminationDate IS NULL OR (pi.InsCardTerminationDate >= ISNULL(NULL,'01/01/1900') AND pi.InsCardTerminationDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))
	)
	AND -- Filter on Effective Date
	(
	 (pi.InsCardEffectiveDate IS NULL OR (pi.InsCardEffectiveDate >= ISNULL('01/01/2005', '01/01/1900') AND pi.InsCardEffectiveDate < DATEADD(d,1, ISNULL('02/06/2007','01/01/3000')))))
	)
[/COLOR]

Why is it displaying the two dates when I have keyed in a value "01/01/2005" and "02/06/2007" as my effective date range? Shouldn't the ISNULL(?DATE.DATE1?, '01/01/1900')fix that? If no date is entered, use 01/01/1900 not both.

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

I think you need the top portion to say DATE.DATE1 rather than DATES.DATE1? (and the same for DATE2)

It appears that whatever tool is generating this SQL (what tool is it by the way) is recognizing DATE.DATE1 variable but not DATES.DATE1 as it is entered above.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jeffsturgeon2002]

AlexCuse,

?DATES.DATE1? & ?DATES.DATE2? is for the Termination Date
?DATE.DATE1? & ?DATE.DATE2? is for the Effective Date

To make my coding easier to read, I just renamed these parameter fields:

AND -- Filter on Termination Date
	(
	(pi.InsCardTerminationDate IS NULL OR (pi.InsCardTerminationDate >= ISNULL(?TERM.DATE1?,'01/01/1900') AND pi.InsCardTerminationDate < DATEADD(d,1, ISNULL(?TERM.DATE2?,'01/01/3000')))
	)
	AND -- Filter on Effective Date
	(
	 (pi.InsCardEffectiveDate IS NULL OR (pi.InsCardEffectiveDate >= ISNULL(?EFFECT.DATE1?, '01/01/1900') AND pi.InsCardEffectiveDate < DATEADD(d,1, ISNULL(?EFFECT.DATE2?,'01/01/3000')))))
	)

So now, TERM and EFFECT are easier to read. (Should have wrote it out to start with this way).

The interface, is GE's Medical Practice Management software. The Effective and Termination dates have a "from" and a "to" date (Effect.Date1 is the effective from) and the (Effect.Date2 is the effective to). Same concept for the Termination Date.

The interface should key in 01/01/1900 and/or 01/01/3000 if the end-user elects not to key in a value into these fields (In theory).

To test the code, I keyed a value into the field and got it to report back both (which is incorrect)

AND -- Filter on Effective Date
	(
	 (pi.InsCardEffectiveDate IS NULL OR (pi.InsCardEffectiveDate >= ISNULL('01/01/2006', '01/01/1900') AND pi.InsCardEffectiveDate < DATEADD(d,1, ISNULL(NULL,'01/01/3000')))))
	)

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

Hm. If you are sure one of the user inputted pieces of information was not null, then I've got nothing. I don't exactly understand what your problem is (what do you mean by returning both?). Are you sure that input is not being read as null, and you are just seeing everything with EffectiveDate > 1/1/1900 (which I presume would be everything)?

Ignorance of certain subjects is a great part of wisdom