WHERE help needed - 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)

WHERE help needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-26 : 15:18:31

I need some assistance with this section:

WHERE
pv.BillStatus = 13 AND
pv.CollectionsStatusMID = 53 and
-- ppa.PatBalance >= 0.00 and
-- ppa.PatBalance <= 0.00 and
pv.CollectionsNextContactDate <= dateadd(d,1,'03/26/2008') and
(g.BillCodeMID is null or g.BillCodeMID in (Select MedlistsID from MedLists where Tablename = 'BILLCODE' and description like 'STATEMENT%') or
(NULL is not null and g.BillCodeMID in (NULL)))

If I un-comment out the

-- ppa.PatBalance >= 0.00 and
-- ppa.PatBalance <= 0.00 and

I get no results. If I leave them commented out, I get the desired results. In the application, the user has a minimum and a maximum value entry that drives these two fields.

Full Report Query Below:

/* Collection Letter */
/* There are two portions to this query. The first is to create a storedProcedure 'rptCollectionParameters' which will be used by visit, so that what ever changes made to this query will be effective for the collection letter from visit component also. The second portion is the query for the reports component. Inorder to make this happen any changes made to the query for the reports component in the second part MUST also be made to the SQL statement in the first part for it to take effect from the Visit component. */
SET NOCOUNT ON

/*First portion for the VISIT component*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'rptCollectionParameters' AND type = 'P')
DROP PROCEDURE rptCollectionParameters

EXEC ('
CREATE PROC rptCollectionParameters @VisitId int AS

SET NOCOUNT ON
DECLARE @DBName varchar(128)
DECLARE @PatientProfileId int

CREATE TABLE #Balances
(
PatientProfileId int NULL,
InsDeposit money,
PatDeposit money,
InsBalance0 money,
PatBalance0 money,
InsBalance30 money,
PatBalance30 money,
InsBalance60 money,
PatBalance60 money,
InsBalance90 money,
PatBalance90 money,
InsBalance120 money,
PatBalance120 money,
InsBalance money,
PatBalance money
)

SELECT @DBName = DB_NAME()

SELECT @PatientProfileId = pv.PatientProfileId
FROM PatientVisit pv
JOIN PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
WHERE
pv.PatientVisitId = @VisitId

INSERT #Balances
(
InsDeposit,
PatDeposit,
InsBalance0,
PatBalance0,
InsBalance30,
PatBalance30,
InsBalance60,
PatBalance60,
InsBalance90,
PatBalance90,
InsBalance120,
PatBalance120,
InsBalance,
PatBalance
)
EXEC master..mbcxp_AgingSummary70 @DBName, @PatientProfileId, 1, NULL,1,NULL,NULL,NULL

UPDATE #Balances
SET
PatientProfileId = @PatientProfileId
WHERE
PatientProfileId IS NULL

SELECT Filler1=-1,
Patient_Id = pp.PatientId,
PatientProfileId=pv.PatientProfileId,
Patient_Prefix = pp.Prefix,
Patient_First_Name = pp.First,
Patient_Middle_Name = pp.Middle,
Patient_Last_Name = pp.Last,
Patient_Suffix = pp.Suffix,
Patient_Address_1 = pp.Address1,
Patient_Address_2 = pp.Address2,
Patient_City = pp.City,
Patient_State = pp.State,
Patient_Zip = pp.Zip,
PatientVisitId=pv.PatientVisitId,
Guarantor_Prefix = g.Prefix,
Guarantor_First_Name = g.First,
Guarantor_Middle_Name = g.Middle,
Guarantor_Last_Name = g.Last,
Guarantor_Suffix = g.Suffix,
Guarantor_Address_1 = g.Address1,
Guarantor_Address_2 = g.Address2,
Guarantor_City = g.City,
Guarantor_State = g.State,
Guarantor_Zip = g.Zip,
DaysInCollections = datediff(day, pv.CollectionsDate, getdate()),
DateInCollections = pv.CollectionsDate,
NextContactDate = pv.CollectionsNextContactDate,
Visit_Total_Balance = convert(varchar(12),pva.InsBalance + pva.PatBalance),
Visit_Insurance_Balance = convert(varchar(12),pva.InsBalance),
Visit_Patient_Balance = convert(varchar(12),pva.PatBalance),
Current_Insurance_Carrier = ic.Name,
Patient_Balance_Current = convert(varchar(11),bt.PatBalance),
Patient_Balance_0to30 = bt.PatBalance0,
Patient_Balance_31to60 = bt.PatBalance30,
Patient_Balance_61to90 = bt.PatBalance60,
Patient_Balance_91to120 = bt.PatBalance90,
Patient_Balance_121 = bt.PatBalance120,
Insurance_Balance_Current = bt.InsBalance,
Insurance_Balance_0to30 = bt.InsBalance0,
Insurance_Balance_31to60 = bt.InsBalance30,
Insurance_Balance_61to90 = bt.InsBalance60,
Insurance_Balance_91to120 = bt.InsBalance90,
Insurance_Balance_121 = bt.InsBalance120,
Facility_Name = f.OrgName,
Facility_Address_1 = f.Address1,
Facility_Address_2 = f.Address2,
Facility_City = f.City,
Facility_State = f.State,
Facility_Zip = f.Zip,
Facility_Phone_1 = ''('' + substring(f.Phone1, 1, 3) + '') '' + substring(f.Phone1, 4, 3) + ''-'' + substring(f.Phone1, 7, 4),
Facility_Phone_2 = ''('' + substring(f.Phone2, 1, 3) + '') '' + substring(f.Phone2, 4, 3) + ''-'' + substring(f.Phone2, 7, 4),
Facility_PayTo_Address_1 = f.Address1,
Facility_PayTo_Address_2 = f.Address2,
Facility_PayTo_City = f.City,
Facility_PayTo_State = f.State,
Facility_PayTo_Zip = f.Zip,
Facility_PayTo_Phone_1 = ''('' + substring(f.Phone1, 1, 3) + '') '' + substring(f.Phone1, 4, 3) + ''-'' + substring(f.Phone1, 7, 4),
Facility_PayTo_Phone_2 = ''('' + substring(f.Phone2, 1, 3) + '') '' + substring(f.Phone2, 4, 3) + ''-'' + substring(f.Phone2, 7, 4),
Facility_Group_Name = fg.OrgName,
Facility_Group_Address_1 = fg.Address1,
Facility_Group_Address_2 = fg.Address2,
Facility_Group_City = fg.City,
Facility_Group_State = fg.State,
Facility_Group_Zip = fg.Zip,
Facility_Group_Phone_1 = ''('' + substring(fg.Phone1, 1, 3) + '') '' + substring(fg.Phone1, 4, 3) + ''-'' + substring(fg.Phone1, 7, 4),
Facility_Group_Phone_2 = ''('' + substring(fg.Phone2, 1, 3) + '') '' + substring(fg.Phone2, 4, 3) + ''-'' + substring(fg.Phone2, 7, 4),
Facility_Group_PayTo_Address_1 = fg.Address1,
Facility_Group_PayTo_Address_2 = fg.Address2,
Facility_Group_PayTo_City = fg.City,
Facility_Group_PayTo_State = fg.State,
Facility_Group_PayTo_Zip = fg.Zip,
Facility_Group_PayTo_Phone_1 = ''('' + substring(fg.Phone1, 1, 3) + '') '' + substring(fg.Phone1, 4, 3) + ''-'' + substring(fg.Phone1, 7, 4),
Facility_Group_PayTo_Phone_2 = ''('' + substring(fg.Phone2, 1, 3) + '') '' + substring(fg.Phone2, 4, 3) + ''-'' + substring(fg.Phone2, 7, 4),
Doctor_Prefix = d.Prefix,
Doctor_First_Name = d.First,
Doctor_Middle_Name = d.Middle,
Doctor_Last_Name = d.Last,
Doctor_Suffix = d.Suffix,
Visit_DateOfService = convert(varchar,pv.Visit,101),
Visit_DateOfEntry = pv.Entered,
Visit_Ticket_Number = pv.TicketNumber,
Patient_Balance = Convert(varchar(12),ppa.patbalance, 1)

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId=pva.PatientVisitId
INNER JOIN PatientProfile pp ON pv.PatientProfileId=pp.PatientProfileId
INNER JOIN PatientProfileAgg ppa on pv.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor g ON pp.GuarantorId=g.GuarantorId
LEFT JOIN InsuranceCarriers ic ON pv.CurrentInsuranceCarriersId=ic.InsuranceCarriersId
LEFT JOIN DoctorFacility f ON pv.FacilityId=f.DoctorFacilityId
LEFT JOIN DoctorFacility fg ON pv.CompanyId=fg.DoctorFacilityId
LEFT JOIN DoctorFacility d ON pv.DoctorId=d.DoctorFacilityId
INNER JOIN #Balances bt ON pv.PatientProfileId = bt.PatientProfileId
WHERE
pv.PatientVisitId = @VisitId

ORDER BY pp.Last

')

/*Second portion for the REPORTS component*/
DECLARE @DBName varchar(128)
DECLARE @PatientProfileId int

CREATE TABLE #Balances
(
PatientProfileId int NULL,
InsDeposit money,
PatDeposit money,
InsBalance0 money,
PatBalance0 money,
InsBalance30 money,
PatBalance30 money,
InsBalance60 money,
PatBalance60 money,
InsBalance90 money,
PatBalance90 money,
InsBalance120 money,
PatBalance120 money,
InsBalance money,
PatBalance money
)

SELECT @DBName = DB_NAME()

DECLARE curVisits CURSOR STATIC FORWARD_ONLY LOCAL FOR
SELECT DISTINCT pv.PatientProfileId
FROM PatientVisit pv
JOIN PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
WHERE
pv.BillStatus = 13 AND pv.CollectionsStatusMID = 53

OPEN curVisits
FETCH NEXT FROM curVisits INTO @PatientProfileId

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Balances
(
InsDeposit,
PatDeposit,
InsBalance0,
PatBalance0,
InsBalance30,
PatBalance30,
InsBalance60,
PatBalance60,
InsBalance90,
PatBalance90,
InsBalance120,
PatBalance120,
InsBalance,
PatBalance
)
EXEC master..mbcxp_AgingSummary70 @DBName, @PatientProfileId, 1, NULL,1,NULL,NULL,NULL

UPDATE #Balances
SET
PatientProfileId = @PatientProfileId
WHERE
PatientProfileId IS NULL

FETCH NEXT FROM curVisits INTO @PatientProfileId
END

CLOSE curVisits
DEALLOCATE curVisits

SELECT Filler1=-1,
Patient_Id = pp.PatientId,
PatientProfileId=pv.PatientProfileId,
Patient_Prefix = pp.Prefix,
Patient_First_Name = pp.First,
Patient_Middle_Name = pp.Middle,
Patient_Last_Name = pp.Last,
Patient_Suffix = pp.Suffix,
Patient_Address_1 = pp.Address1,
Patient_Address_2 = pp.Address2,
Patient_City = pp.City,
Patient_State = pp.State,
Patient_Zip = pp.Zip,
PatientVisitId=pv.PatientVisitId,
Guarantor_Prefix = g.Prefix,
Guarantor_First_Name = g.First,
Guarantor_Middle_Name = g.Middle,
Guarantor_Last_Name = g.Last,
Guarantor_Suffix = g.Suffix,
Guarantor_Address_1 = g.Address1,
Guarantor_Address_2 = g.Address2,
Guarantor_City = g.City,
Guarantor_State = g.State,
Guarantor_Zip = g.Zip,
DaysInCollections = datediff(day, pv.CollectionsDate, getdate()),
DateInCollections = pv.CollectionsDate,
NextContactDate = pv.CollectionsNextContactDate,
Visit_Total_Balance = convert(varchar(12),pva.InsBalance + pva.PatBalance),
Visit_Insurance_Balance = convert(varchar(12),pva.InsBalance),
Visit_Patient_Balance = convert(varchar(12),pva.PatBalance),
Current_Insurance_Carrier = ic.Name,
Patient_Balance_Current = convert(varchar(11),bt.PatBalance),
Patient_Balance_0to30 = bt.PatBalance0,
Patient_Balance_31to60 = bt.PatBalance30,
Patient_Balance_61to90 = bt.PatBalance60,
Patient_Balance_91to120 = bt.PatBalance90,
Patient_Balance_121 = bt.PatBalance120,
Insurance_Balance_Current = bt.InsBalance,
Insurance_Balance_0to30 = bt.InsBalance0,
Insurance_Balance_31to60 = bt.InsBalance30,
Insurance_Balance_61to90 = bt.InsBalance60,
Insurance_Balance_91to120 = bt.InsBalance90,
Insurance_Balance_121 = bt.InsBalance120,
Facility_Name = f.OrgName,
Facility_Address_1 = f.Address1,
Facility_Address_2 = f.Address2,
Facility_City = f.City,
Facility_State = f.State,
Facility_Zip = f.Zip,
Facility_Phone_1 = '(' + substring(f.Phone1, 1, 3) + ') ' + substring(f.Phone1, 4, 3) + '-' + substring(f.Phone1, 7, 4),
Facility_Phone_2 = '(' + substring(f.Phone2, 1, 3) + ') ' + substring(f.Phone2, 4, 3) + '-' + substring(f.Phone2, 7, 4),
Facility_PayTo_Address_1 = f.Address1,
Facility_PayTo_Address_2 = f.Address2,
Facility_PayTo_City = f.City,
Facility_PayTo_State = f.State,
Facility_PayTo_Zip = f.Zip,
Facility_PayTo_Phone_1 = '(' + substring(f.Phone1, 1, 3) + ') ' + substring(f.Phone1, 4, 3) + '-' + substring(f.Phone1, 7, 4),
Facility_PayTo_Phone_2 = '(' + substring(f.Phone2, 1, 3) + ') ' + substring(f.Phone2, 4, 3) + '-' + substring(f.Phone2, 7, 4),
Facility_Group_Name = fg.OrgName,
Facility_Group_Address_1 = fg.Address1,
Facility_Group_Address_2 = fg.Address2,
Facility_Group_City = fg.City,
Facility_Group_State = fg.State,
Facility_Group_Zip = fg.Zip,
Facility_Group_Phone_1 = '(' + substring(fg.Phone1, 1, 3) + ') ' + substring(fg.Phone1, 4, 3) + '-' + substring(fg.Phone1, 7, 4),
Facility_Group_Phone_2 = '(' + substring(fg.Phone2, 1, 3) + ') ' + substring(fg.Phone2, 4, 3) + '-' + substring(fg.Phone2, 7, 4),
Facility_Group_PayTo_Address_1 = fg.Address1,
Facility_Group_PayTo_Address_2 = fg.Address2,
Facility_Group_PayTo_City = fg.City,
Facility_Group_PayTo_State = fg.State,
Facility_Group_PayTo_Zip = fg.Zip,
Facility_Group_PayTo_Phone_1 = '(' + substring(fg.Phone1, 1, 3) + ') ' + substring(fg.Phone1, 4, 3) + '-' + substring(fg.Phone1, 7, 4),
Facility_Group_PayTo_Phone_2 = '(' + substring(fg.Phone2, 1, 3) + ') ' + substring(fg.Phone2, 4, 3) + '-' + substring(fg.Phone2, 7, 4),
Doctor_Prefix = d.Prefix,
Doctor_First_Name = d.First,
Doctor_Middle_Name = d.Middle,
Doctor_Last_Name = d.Last,
Doctor_Suffix = d.Suffix,
Visit_DateOfService = convert(varchar,pv.Visit,101),
Visit_DateOfEntry = pv.Entered,
Visit_Ticket_Number = pv.TicketNumber,
Patient_Balance = '$' + Convert(varchar(12),ppa.patbalance, 1),
Budget = '$' + Convert(Varchar(12),g.Budget,1)

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId=pva.PatientVisitId
INNER JOIN PatientProfile pp ON pv.PatientProfileId=pp.PatientProfileId
INNER JOIN PatientProfileAgg ppa on pv.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor g ON pp.GuarantorId=g.GuarantorId
LEFT JOIN InsuranceCarriers ic ON pv.CurrentInsuranceCarriersId=ic.InsuranceCarriersId
LEFT JOIN DoctorFacility f ON pv.FacilityId=f.DoctorFacilityId
LEFT JOIN DoctorFacility fg ON pv.CompanyId=fg.DoctorFacilityId
LEFT JOIN DoctorFacility d ON pv.DoctorId=d.DoctorFacilityId
LEFT JOIN #Balances bt ON pv.PatientProfileId = bt.PatientProfileId
WHERE
pv.BillStatus = 13 AND
pv.CollectionsStatusMID = 53 and
-- ppa.PatBalance >= 0.00 and
-- ppa.PatBalance <= 0.00 and
pv.CollectionsNextContactDate <= dateadd(d,1,'03/26/2008') and
(g.BillCodeMID is null or g.BillCodeMID in (Select MedlistsID from MedLists where Tablename = 'BILLCODE' and description like 'STATEMENT%') or
(NULL is not null and g.BillCodeMID in (NULL)))

ORDER BY pp.LAST

DROP TABLE #Balances

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 15:25:05

Well
ppa.PatBalance >= 0.00 and
ppa.PatBalance <= 0.00 and

is the same as
ppa.PatBalance = 0.00 and

sounds like you didn't have any 0 entries in your resultset.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-26 : 15:33:31

One was 86.04 the other was 25.00. I guess I should drop off the = on both then?

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-26 : 15:34:45

Nevermind, that get's me the same result.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 15:38:53

which was 86.04 and which 25.00?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-26 : 15:41:25

Patient_Balance = '$' + Convert(varchar(12),ppa.patbalance, 1),

I had two patients pull. One had a Patient Balance of 86.04 and the other had a balance of 25.00. I was only able to retrieve these two patients when I commented out those two lines. If I uncomment those lines, I get no records in my result set.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 15:54:45

Looks like a data redpresentation issue.
Are you sure that ends up with the correct character format - presumably you want to do a character compare due to indexing or invalid data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-26 : 16:04:07

Do not format results in T-SQL, format at your presentation layer.

- Jeff
http://weblogs.sqlteam.com/JeffS

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:07:27

quote:
Originally posted by jsmith8858

Do not format results in T-SQL, format at your presentation layer.

- Jeff
http://weblogs.sqlteam.com/JeffS

You mean get the resultset back to the presentation then do the filter there?
Doesn't sound very sensible.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-26 : 16:07:51

I took the '$' + off this field. I am still stuck on the where clause though. I need the enduser to have an ability to filter for balances in a range (to also include $0.00).

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:13:38

If the want a range then you will need to receive two values as input - start and end of the range.
In the query
where ppa.PatBalance between @startrange and @endrange

if you also want to include 0 then
where (ppa.PatBalance between @startrange and @endrange or ppa.PatBalance = 0)

assuming that ppa.PatBalance is numeric.
Also need to make sure that @startrange is less than @endrange

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-26 : 16:21:05

Is PatBalance a Money data type? If so, what is the issue with allow the user to select a range of values? Are you trying to do a range and allow the person to select "all" values? Like this??

WHERE
	pv.BillStatus = 13
	AND pv.CollectionsStatusMID = 53
	AND 
	(
		-- Get range or get all (assuming @LowValue = NULL means all)
		(
			ppa.PatBalance >= @LowValue
			AND ppa.PatBalance <= @HighValue
		)
		OR
		@LowValue IS NULL
	)
	AND pv.CollectionsNextContactDate <= DATEADD(d, 1, '03/26/2008')
	AND
	(
		g.BillCodeMID IS NULL
		OR g.BillCodeMID IN
		(
			SELECT
				MedlistsID
			FROM
				MedLists
			WHERE
				Tablename = 'BILLCODE'
				AND description LIKE 'STATEMENT%'
		)
		OR
		(
			-- HUH?
			NULL IS NOT NULL
			AND g.BillCodeMID IN(NULL)
		)
	)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-26 : 16:24:24

quote:
Originally posted by nr

quote:
Originally posted by jsmith8858

Do not format results in T-SQL, format at your presentation layer.

- Jeff
http://weblogs.sqlteam.com/JeffS

You mean get the resultset back to the presentation then do the filter there?
Doesn't sound very sensible.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

No,I meant don't format a numeric value as a varchar with a leading $ in T-SQL. Or phone numbers. Or dates. Or numbers. And so on.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources