An Unholy Mess - 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

General SQL Server Forums

New to SQL Server Programming

An Unholy Mess

Author

Topic

BSinatra
Starting Member

13 Posts

Posted - 2008-03-20 : 08:53:55

I am trying to verify and create new user based on Billing Statement information. The user may not be the ID of the statement but has a

relationship to that ID.

Here is the data I will be passing:

Statement PrepDate
Statement PatBal

BillTo Address
BillTo Name

Patient DateofBirth
Patient Name

relevant table info:

BILTOS
BillToID, Name (user match), Address1(user match)

STMTHIST
BillToID, PrepDate (user match), PatBal (user match)

PATIENTS
BillToID, Name (user match), BirthDate (user match), Email, PatientsID

PATWEB
PatientID, UserName (user input), PIN (user input), Question (user input), Answer (user input)

I also check to make sure the username doesn't already exist in the PATWEB table

here is what I have so far that is not working (table reference not unique - BILTOS)

INSERT INTO PATWEB (PatientID, UserName, PIN, Question, Answer, LastAccess)
SELECT PATIENTS.PatientID, 'bcolladay@pdsmed.com', 'osx5ief7*', 'What', 'green', CurDate()
FROM PATIENTS, BILLTOS
JOIN PATIENTS BILLTOS ON PATIENTS.BillToID = BILLTOS.BillToID
WHERE not exists (select * from PATWEB Where PATWEB.PatientID = PATIENTS.PatientID)
AND PATIENTS.BirthDate = '1971-05-03'
AND PATIENTS.Name LIKE 'lui%'
AND STMTHIST.BillToID = PATIENTS.BillToID
AND STMTHIST.PatBal = '215.00'
AND STMTHIST.PrepDate = '2008-03-05'
AND BILLTOS.Address1 = '987 Worley St'
AND BILLTOS.Name LIKE 'Lui@'

so I am checking to see if the billing statement and other info they supplied matches before I create the account in PATWEB.

I appreciate any help, thanks

BSinatra
Starting Member

13 Posts

Posted - 2008-03-20 : 09:40:39

This is getting closer I think, It completes but doesn't update. The criteria I am passing is correct.

INSERT INTO PATWEB (PatientID, UserName, PIN, Question, Answer, LastAccess)
SELECT PATIENTS.PatientID, 'bcolladay@pdsmed.com', 'osx5ief7*', 'What', 'green', CurDate()
FROM PATIENTS, BILLTOS, STMTHIST, PATWEB
WHERE STMTHIST.BillToID = PATIENTS.BillToID
AND BILLTOS.BillToID = STMTHIST.BillToID
AND PATIENTS.BirthDate = '1971-05-03'
AND PATIENTS.Name LIKE 'lui%'
AND BILLTOS.Address1 = '987 Worley St'
AND BILLTOS.Name LIKE 'Lui@'
AND STMTHIST.PatBal = '215.00'
AND STMTHIST.PrepDate = '2008-03-05'
AND not exists (select * from PATWEB Where PATWEB.PatientID = PATIENTS.PatientID)

BSinatra
Starting Member

13 Posts

Posted - 2008-03-20 : 11:10:54

I had a @ instead of a % in my "AND BILLTOS.Name LIKE 'Lui@'"

INSERT INTO PATWEB (PatientID, UserName, PIN, Question, Answer, LastAccess)
SELECT PATIENTS.PatientID, 'bcolladay@pdsmed.com', 'osx5ief7*', 'What', 'green', CurDate()
FROM PATIENTS, BILLTOS, STMTHIST
WHERE BILLTOS.BillToID = PATIENTS.BillToID
AND BILLTOS.BillToID = STMTHIST.BillToID
AND PATIENTS.BirthDate = '1971-05-03'
AND PATIENTS.Name LIKE '%lui%'
AND BILLTOS.Address1 LIKE '%987%'
AND BILLTOS.Name LIKE '%lui%'
AND STMTHIST.PatBal LIKE '%215%'
AND STMTHIST.PrepDate = '2008-03-05'
AND not exists (select * from PATWEB Where PATWEB.PatientID = PATIENTS.PatientID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources