A Lookup value for saving data

[MichaelaLee]

Hi Everyone,
Was not sure how to name the subject, but here is what I need to do. I have the followinf stored proc:
INSERT INTO BillingSupport ( PatientId, ServiceDate, Diagnostic, Program, AttendedGroup )
SELECT ATTENDANCE.PatientID, ATTENDANCE.AttendDate, ATTENDANCE.Diagnostic, ATTENDANCE.Program, ATTENDANCE.AttendedGroup FROM (TERMINATION INNER JOIN ATTENDANCE ON TERMINATION.PatientID = ATTENDANCE.PatientID)
INNER JOIN PATIENTS ON TERMINATION.PatientID = PATIENTS.PatientID
As you can see I need to insert the values into BillingSupport. But I would like to convert the values for 3 fields (Diagnostic, Program & AttendDate). The Attendance table stores them as a bit field (1 or 0).
Now, if the field contains a 1, I'd like to write an "X" instead of a 1 to the BillingSupport table. Is there an easy way to do this in SQL 2000. THanks for any help.
Michael Lee

 

[mrdenny]

Yes you can do this. I'm not sure what benifit you will get from doing this however.

You would need to change the character type of the Diagnostic, Program and AttendedGroup fields from bit to varchar(1). Then put a case statement for each field in the select statement so it would look more like this.

INSERT INTO BillingSupport ( PatientId, ServiceDate,  Diagnostic,  Program,  AttendedGroup ) 
SELECT ATTENDANCE.PatientID, 
   ATTENDANCE.AttendDate,  
   case when ATTENDANCE.Diagnostic = 1 then 
      'X'
   end Diagnostic, 
   case when ATTENDANCE.Program = 1 then
      'X'
   end Program, 
   case when ATTENDANCE.AttendedGroup = 1 then
      'X'
   end AttendedGroup
FROM TERMINATION 
INNER JOIN ATTENDANCE ON TERMINATION.PatientID = ATTENDANCE.PatientID
INNER JOIN PATIENTS ON TERMINATION.PatientID = PATIENTS.PatientID

Again, I'm not sure what benefit you are trying to get here. If it's a formatting thing on the front end, you could keep it as a bit field, just do the formatting when displaying the data.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] BillingSupport 
   [Gray]([/Gray] PatientId[Gray],[/Gray] ServiceDate[Gray],[/Gray]  Diagnostic[Gray],[/Gray]  
     Program[Gray],[/Gray]  AttendedGroup [Gray])[/Gray] 
   [Blue]SELECT[/Blue] A.PatientID[Gray],[/Gray] A.AttendDate[Gray],[/Gray]  
          [Blue]CASE[/Blue] A.Diagnostic [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] [red]'x'[/red] [Blue]ELSE[/Blue] [red]''[/red] [Blue]END[/Blue][Gray],[/Gray]
          [Blue]CASE[/Blue] A.Program [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] [red]'x'[/red] [Blue]ELSE[/Blue] [red]''[/red] [Blue]END[/Blue][Gray],[/Gray]
          [Blue]CASE[/Blue] A.AttendedGroup [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] [red]'x'[/red] [Blue]ELSE[/Blue] [red]''[/red] [Blue]END[/Blue]
   [Blue]FROM[/Blue] TERMINATION T [Blue]INNER[/Blue] [Gray]JOIN[/Gray] ATTENDANCE A 
      [Blue]ON[/Blue] T.PatientID [Gray]=[/Gray] A.PatientID 
   [Blue]INNER[/Blue] [Gray]JOIN[/Gray] PATIENTS P 
      [Blue]ON[/Blue] T.PatientID [Gray]=[/Gray] P.PatientID

I assume that if the value is 0 then you want an empty string.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]