Formatting help ... I think 2

[jeffsturgeon2002]

I want to take the below field:

pva.PatBalance AS [Visit Pat Balance],

and make a new field 140% of Visit Pat Balance. So if the balance is $100.00 on Visit Pat Balance, I want this field to take the Visit Pat Balance at 140% and report out $140.00.

I tried:

'140PatBal' = pva.PatBalance * 140,

but it didnt format the money correct. I think my issue is the display not the formula ... right??

SELECT DISTINCT
	CONVERT(VARCHAR,pv.visit,101) AS DOS,
 	pv.TicketNumber, 
 	ISNULL(pp.first,'')+ ' '+ ISNULL(pp.last,'')AS [Patient Name], 
 	ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.Address2,'')+ ' '+ ISNULL(pp.City,'')+ ' '+ ISNULL(pp.State,'')+ ' '+ ISNULL(pp.Zip,'') AS [Patient Address],
 	IsNull(CONVERT(varchar,pp.Birthdate,101), '') AS Birthdate, 
 	pp.PatientId, 
 	ISNULL(ic.ListName,'') AS [Current Carrier], 
 	ISNULL(pi.InsuredId,'') AS [Insured ID], 
 	df.ListName AS Doctor,
 	df1.ListName AS Facility,
	df2.ListName AS Company, 
 	pva.PatBalance AS [Visit Pat Balance], 
	pva.InsBalance AS [Visit Ins Balance], 
 	ppa.PatBalance AS [Total PatBalance],
 	ppa.InsBalance AS [Total InsBalance], 
 	ISNULL(CONVERT(VARCHAR,g.LastStatement,101),'')AS [Last Statement],
	(SELECT TOP 1 description 
    	FROM PatientCorrespondence 
   	WHERE PatientProfileID = pp.PatientProfileID AND PatientVisitID = pv.PatientVisitID 
    	ORDER BY Created DESC) AS Notes	
		
FROM  	PatientVisit pv 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId 
	LEFT OUTER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN PatientInsurance pi ON pv.CurrentPICarrierId = pi.PatientInsuranceId 
	LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId AND pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId 
	LEFT JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 
	LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId 
	LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId  
	LEFT OUTER JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN [DoctorFacility] AS df2 ON pv.[CompanyId]= df2.[DoctorFacilityId]
	LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
	
WHERE pv.PatientVisitId = ('75')

Jeff

SELECT * FROM users WHERE clue > 0

 

[SQLDenis]

'140PatBal' = pva.PatBalance * 1.4

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

Denis wrote a very nice (and simple) FAQ about this.

faq183-6157

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jeffsturgeon2002]

SQLDenis and George,

Thank you both for helping this newbie get his answer! Its coming together alot better now and I appreciate your fast help to my questions.

Jeff

SELECT * FROM users WHERE clue > 0