Solved

Convert Money with no Decimal.

Posted on 2009-06-27
17
735 Views
Last Modified: 2012-05-07
Please note, the SQL is handled dynamically by the SQL Server, therefore some items in my WHERE clause will look odd to you, please disregard this.

I need help on this field:

pva.InsBalance + pva.PatBalance AS [Total Charges] ,

An example of an output in my dataset:

500.00

I need to report back this without the Decimals. Any help is appreciated. Both are MONEY fields.

SET NOCOUNT ON
 
SELECT
	pv.TicketNumber , 
	dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName ,
	FLOOR(DATEDIFF(DAY, pp.birthdate, GETDATE()) / 365.25) AS [Patient Age],
    	LEFT(f.FederalTaxId , 10) AS FacilityId ,
    	LEFT(pp.PatientId , 17) AS PatientControlNumber ,
    	LEFT(pp.MedicalRecordNumber , 17) AS PatientMRN ,
    	CASE WHEN pp.SSN IS NULL THEN '300'
    	     ELSE LEFT(pp.SSN , 9)
    	END AS SSN ,
    	LEFT(pp.Zip , 5) AS Zip ,
    	REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS DOB ,
    	pp.Sex ,
    	REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationFrom , 1) , '/' , '') AS AdmissionDate ,
	ml.Code AS [Source of Admission] , --  Admission Source (Filing 3 Tab)
	mll.Code AS PatientDischargeStatus ,  -- Patient Status (Filing 3 Tab)
	REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationTo , 1) , '/' , '') AS DischargeDate ,
	LEFT(replace(pvd1.ICD9Code , '.' , '') , 5) AS PrimaryDiag ,
	LEFT(replace(pvd2.ICD9Code , '.' , '') , 5) AS SecondDiag ,
	LEFT(replace(pvd3.ICD9Code , '.' , '') , 5) AS ThirdDiag ,
	LEFT(replace(pvd4.ICD9Code , '.' , '') , 5) AS FourthDiag ,
	LEFT(replace(pvd5.ICD9Code , '.' , '') , 5) AS FifthDiag ,
	LEFT(replace(pvd6.ICD9Code , '.' , '') , 5) AS SixthDiag ,
	LEFT(replace(pvd7.ICD9Code , '.' , '') , 5) AS SeventhDiag ,
	LEFT(replace(pvd8.ICD9Code , '.' , '') , 5) AS EighthDiag ,
	LEFT(replace(pvd9.ICD9Code , '.' , '') , 5) AS NinthDiag ,
	LEFT(pvp.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml4.Code), '  ') + ISNULL(CONVERT(varchar(4), ml5.Code), ' ') AS PrincipleCode , 
	LEFT(pvp2.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml6.Code), '  ') + ISNULL(CONVERT(varchar(4), ml7.Code), ' ') AS SecondCode ,  
	LEFT(pvp3.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml8.Code), '  ') + ISNULL(CONVERT(varchar(4), ml9.Code), ' ') AS ThirdCode ,
	LEFT(pvp4.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml10.Code), '  ') + ISNULL(CONVERT(varchar(4), ml11.Code), ' ') AS FourthCode ,
	LEFT(pvp5.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml12.Code), '  ') + ISNULL(CONVERT(varchar(4), ml13.Code), ' ') AS FifthCode ,
	LEFT(pvp6.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml14.Code), '  ') + ISNULL(CONVERT(varchar(4), ml15.Code), ' ') AS SixthCode ,
	REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') AS [Primary CPT Date] ,
	'4' as [Coding Method Used] ,
	REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') + REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceTo , 1) , '/' , '')  AS [Statement Covers Period] ,
	pva.InsBalance + pva.PatBalance AS [Total Charges] ,
	LEFT(ic.ListName,25) AS [Primary Payer] , 
	LEFT(ic2.ListName,25) AS [Secondary Payer] ,
	LEFT(ic3.ListName,25) AS [Third Payer] ,
	LEFT(od.StateLicenseNo,12) AS [Performing Physician ID] ,
	LEFT(oth.StateLicenseNo,12) AS [Other Physician ID] ,
	'999' AS [Type of Bill]
INTO #Tmp
 
FROM
    	PatientVisit pv
    	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
	LEFT OUTER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
	LEFT JOIN PatientVisitFiling pvf ON pv.PatientVisitId = pvf.PatientVisitId
    	LEFT JOIN MedLists ml ON pvf.AdmissionSourceMId = ml.MedListsId
    	LEFT JOIN Medlists mll ON pvf.PatientStatusMId = mll.MedListsId 
    	LEFT JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
    	LEFT JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
    	LEFT JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
	LEFT JOIN DoctorFacility od ON pv.OperatingDoctorId = od.DoctorFacilityId
	LEFT JOIN DoctorFacility oth ON pv.OtherDoctorId = oth.DoctorFacilityId
	LEFT JOIN PatientVisitInsurance pvi ON pv.PatientVisitID = pvi.PatientVisitID AND pvi.OrderForClaims = 1
	LEFT JOIN PatientInsurance pi ON pvi.PatientInsuranceID = pi.PatientInsuranceID
        	LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersID
	LEFT JOIN PatientVisitInsurance pvi2 ON pv.PatientVisitID = pvi2.PatientVisitID AND pvi2.OrderForClaims = 2
	LEFT JOIN PatientInsurance pi2 ON pvi2.PatientInsuranceID = pi2.PatientInsuranceID
        	LEFT JOIN InsuranceCarriers ic2 ON pi2.InsuranceCarriersID = ic2.InsuranceCarriersID
        	LEFT JOIN PatientVisitInsurance pvi3 ON pv.PatientVisitID = pvi3.PatientVisitID AND pvi3.OrderForClaims = 3
	LEFT JOIN PatientInsurance pi3 ON pvi3.PatientInsuranceID = pi3.PatientInsuranceID
        	LEFT JOIN InsuranceCarriers ic3 ON pi3.InsuranceCarriersID = ic3.InsuranceCarriersID
    	LEFT JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId and pvd1.listorder = 1
    	LEFT JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId and pvd2.listorder = 2
    	LEFT JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId and pvd3.listorder = 3
    	LEFT JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId and pvd4.listorder = 4
    	LEFT JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId and pvd5.listorder = 5
    	LEFT JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId and pvd6.listorder = 6
    	LEFT JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId and pvd7.listorder = 7
    	LEFT JOIN PatientVisitDiags pvd8 ON pv.PatientVisitId = pvd8.PatientVisitId and pvd8.listorder = 8
    	LEFT JOIN PatientVisitDiags pvd9 ON pv.PatientVisitId = pvd9.PatientVisitId and pvd9.listorder = 9
	LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId AND pvp.ListOrder = 1
	LEFT JOIN PatientVisitProcs pvp2 ON pv.PatientVisitId = pvp2.PatientVisitId AND pvp2.Listorder = 2 
	LEFT JOIN PatientVisitProcs pvp3 ON pv.PatientVisitId = pvp3.PatientVisitId AND pvp3.Listorder = 3 
	LEFT JOIN PatientVisitProcs pvp4 ON pv.PatientVisitId = pvp4.PatientVisitId AND pvp4.ListOrder = 4 
	LEFT JOIN PatientVisitProcs pvp5 ON pv.PatientVisitId = pvp5.PatientVisitId AND pvp5.ListOrder = 5
	LEFT JOIN PatientVisitProcs pvp6 ON pv.PatientVisitId = pvp6.PatientVisitId AND pvp6.ListOrder = 6
	LEFT OUTER JOIN MedLists ml4 ON pvp.Modifier1MId = ml4.MedListsId 
	LEFT OUTER JOIN MedLists ml5 ON pvp.Modifier2MId = ml5.MedListsId 
	LEFT OUTER JOIN MedLists ml6 ON pvp2.Modifier1MId = ml6.MedListsId 
	LEFT OUTER JOIN MedLists ml7 ON pvp2.Modifier2MId = ml7.MedListsId 
	LEFT OUTER JOIN MedLists ml8 ON pvp3.Modifier1MId = ml8.MedListsId 
	LEFT OUTER JOIN MedLists ml9 ON pvp3.Modifier2MId = ml9.MedListsId 
	LEFT OUTER JOIN MedLists ml10 ON pvp4.Modifier1MId = ml10.MedListsId 
	LEFT OUTER JOIN MedLists ml11 ON pvp4.Modifier2MId = ml11.MedListsId 
	LEFT OUTER JOIN MedLists ml12 ON pvp5.Modifier1MId = ml12.MedListsId 
	LEFT OUTER JOIN MedLists ml13 ON pvp5.Modifier2MId = ml13.MedListsId 
	LEFT OUTER JOIN MedLists ml14 ON pvp6.Modifier1MId = ml14.MedListsId 
	LEFT OUTER JOIN MedLists ml15 ON pvp6.Modifier2MId = ml15.MedListsId
 
WHERE
	 --Filter on Date
	(
	(1 = 1 AND (pv.Entered >= ISNULL(NULL, '1/1/1900') AND pv.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))  OR
	(1 = 2 AND (pvp.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND pvp.DateOfServiceFrom < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))
	)
	AND --Filter on Doctor
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on Operating Doctor
	(
	(NULL IS NOT NULL AND pv.OperatingDoctorId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on Company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on Facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on CPT Code
	(
	(NULL IS NOT NULL AND pvp.ProceduresId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on Diagnosis
	(
	(NULL IS NOT NULL AND pvd1.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd2.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd3.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd4.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd5.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd6.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd7.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd8.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND pvd9.DiagnosisId IN (NULL)) OR 
	(NULL IS NULL)
	)
	AND --Filter on Patient
	(
	(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on Patient Sex
	(
	(Null IS NOT NULL AND pp.sex in (Null)) OR
	(Null IS NULL)
	)
	AND --Filter on Insurance Carrier
	(
	(NULL IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
 
SELECT * FROM #Tmp
WHERE --Filter on Age
	(
	([Patient Age] >= ('-1') AND [Patient Age] <= ('125'))	
	)
 
DROP TABLE #Tmp

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24729924
Not sure what you are looking for , if it is returning nulls , put an isnull

ISNULL(pva.InsBalance,0)  + ISNULL(pva.PatBalance,0)

if its a formating question

cast (pva.InsBalance + pva.PatBalance as decimal (18,2) ) AS [Total Charges]
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729927
Try casting the value as an INT.
DECLARE @n AS MONEY
SET @n = 500.50
 
SELECT @n -- has decimals
, CAST(@n AS INT) -- no decimals
, CAST(CEIL(@n) AS INT) -- no decimals, drop fraction totally

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729928
I miss typed CEILING function, but meant FLOOR anyway.

e.g. CAST(FLOOR(@n) AS INT)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Jeff S
ID: 24729933
aneesh -
I tried yours and I still got the decimals. I am trying to drop the decimals all together.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24729939
cast them as Integer


cast (pva.InsBalance + pva.PatBalance as Integer ) AS [Total Charges]
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729942

CAST(pva.InsBalance + pva.PatBalance AS INT) AS [Total Charges]
 
-- OR take the whole number part regardless of decimal value
CAST(FLOOR(pva.InsBalance + pva.PatBalance) AS INT) AS [Total Charges]
 
-- OR round according to value of decimals
CAST(ROUND(pva.InsBalance + pva.PatBalance) AS INT) AS [Total Charges]

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24729956
aneesh or mwvisa1 -
I tried casting it as an Int and for the value that was 500.00 now reads 500. How can I get the cents back, so it would read "50000"? Its for a federal report and they want dollar values without decimals. Its crazy i know, but your help is very appreciated.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729957
Multiply by 100.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729958

CAST((pva.InsBalance + pva.PatBalance)*100 AS INT) AS [Total Charges]
 

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 24729959
you can multiply that with 100

(  pva.InsBalance + pva.PatBalance ) *100 AS [Total Charges]

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24729962
if you want  500.50 as 50050 use my code otherwise what u need is 50000 use mwvisa1's code
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729964
Or treat this as String and replace the '.'.
DECLARE @n AS MONEY
SET @n = 500.50
SELECT REPLACE(@n, '.', '');

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729967
Mine yield 50050 as well.  To get 50000, you can use my previous suggestion of FLOOR with multiplying by 100.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 24729969
Hopefully you understand, if the starting value is 500.00 it will yield 50000.  We are discussing how you want values with actual decimals (other than zero) to be handled in case this is getting confusing as your case is 500.00 but code examples are using 500.50.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24729985
@Aneesh,

BTW, this suggestion - http:#24729959 - will still return decimal in result as the data type is money, so 500.00 will become 50000.00.  Still have to get rid of the decimal places.

Best regards,
Kevin
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31597626
Split points to be fair, both helped and both solutions worked.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24729989
It should be like, i forgot to put the cast statement


CAST ( (  pva.InsBalance + pva.PatBalance ) *100 AS Bigint )  [Total Charges]
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question