Percentage rounding

Transact-SQL
1

Have a question on my string, looking to move the decimals over 2 places, I know I am getting the numeric value but I am having a difficult time getting percentages from them. Also looking to add a where clause to this to get nothing but greater than 30 percent. Can anyone help?

Declare @StartDate datetime,
@EndDate datetime
set @StartDate= '07/01/2014'
set @EndDate = '10/01/2014'

Select DISTINCT T.ProviderID
,CASE
WHEN AI.InsuranceID IN ('WV-CHIP','WVDHS','WVDHS/CL','WVDHS/COV','WVDHS/FH','WVDHS/HP','WVDHS/UNI') then 'WV Medicaid'
WHEN AI.InsuranceID in ('ODHS','ODHS/ANTH','ODHS/BUCK','ODHS/CARE','ODHS/MOLI','ODHS/OTHER','ODHS/PARA','ODHS/PARA','ODHS/PE','ODHS/UHC')
THEN 'OH Medicaid'
ELSE 'Other'
END AS 'InsType'
,T.ProviderName
,PROV.NationalProviderIdNumber
,CAST(TI.ServiceDateTime AS DATE) AS 'Date'
,P.MriUnitNumber
,TI.Number
,AI.InsuranceID
INTO #Table
From PbrAccountInsuranceOrder AI
JOIN PbrAccountTransactions T ON T.AccountID = AI.AccountID AND T.SourceID = AI.SourceID
JOIN meditech_livendb..DMisProvider PROV ON T.ProviderID=PROV.ProviderID
JOIN PbrTicket AS TI ON (T.AccountID = TI.AccountID)
JOIN PbrPatients AS P ON (T.PatientID = P.PatientID)
join PbrAccountInsuranceCopayTypes AS AT ON AT.AccountID=AI.AccountID AND AT.SourceID=AI.SourceID
WHERE( T.ServiceDateTime between @StartDate and @EndDate)and
( TI.ServiceDateTime between @StartDate and @EndDate)
--T.ProviderID='CARJ'
AND T.Type='C' AND T.Amount>0 AND
AI.InsuranceSetEffectiveDateTime<= TI.ServiceDateTime AND
AI.InsuranceSetEffectiveDateTime=AT.AccountIDDateTime AND
P.MriUnitNumber LIKE 'H%'

Select ProviderID
,COUNT(InsuranceID) AS 'WV Medicaid'
INTO #WV
FROM #Table
WHERE InsType = 'WV Medicaid'
Group by ProviderID
order by ProviderID

Select ProviderID
,COUNT(InsuranceID) AS 'OH Medicaid'
INTO #OH
FROM #Table
WHERE InsType = 'OH Medicaid'
Group by ProviderID
order by ProviderID

Select ProviderID
,COUNT(InsuranceID) AS 'OTHER Medicaid'
INTO #Other
FROM #Table
WHERE InsType = 'Other'
Group by ProviderID
order by ProviderID

SELECT distinct t.ProviderID
,wv.[WV Medicaid]
,oh.[OH Medicaid]
,o.[OTHER Medicaid]
,wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid] AS 'TotalInsurances'
,CONVERT(numeric,wv.[WV Medicaid]) / CONVERT(numeric,(wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid])) AS 'WVPercentage'
,CONVERT(numeric,oh.[OH Medicaid]) / CONVERT(numeric,(wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid])) AS 'OHPercentage'
,(CONVERT(numeric,oh.[OH Medicaid]) + CONVERT(numeric,wv.[WV Medicaid])) / CONVERT(numeric,(wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid])) AS 'TotalPercentage'

FROM
#Table t
inner join #WV wv
on t.ProviderID = wv.ProviderID
inner join #OH oh
on t.ProviderID = oh.ProviderID
inner join #Other o
on t.ProviderID = o.ProviderID

drop table #OH
drop table #Other
drop table #WV
DROP Table #Table

2

Reformatting with poorsql.com and using code hilighting with </> from tools:

DECLARE @StartDate DATETIME
      ,@EndDate DATETIME

SET @StartDate = '07/01/2014'
SET @EndDate = '10/01/2014'

SELECT DISTINCT T.ProviderID
      ,CASE 
            WHEN AI.InsuranceID IN (
                        'WV-CHIP'
                        ,'WVDHS'
                        ,'WVDHS/CL'
                        ,'WVDHS/COV'
                        ,'WVDHS/FH'
                        ,'WVDHS/HP'
                        ,'WVDHS/UNI'
                        )
                  THEN 'WV Medicaid'
            WHEN AI.InsuranceID IN (
                        'ODHS'
                        ,'ODHS/ANTH'
                        ,'ODHS/BUCK'
                        ,'ODHS/CARE'
                        ,'ODHS/MOLI'
                        ,'ODHS/OTHER'
                        ,'ODHS/PARA'
                        ,'ODHS/PARA'
                        ,'ODHS/PE'
                        ,'ODHS/UHC'
                        )
                  THEN 'OH Medicaid'
            ELSE 'Other'
            END AS 'InsType'
      ,T.ProviderName
      ,PROV.NationalProviderIdNumber
      ,CAST(TI.ServiceDateTime AS DATE) AS 'Date'
      ,P.MriUnitNumber
      ,TI.Number
      ,AI.InsuranceID
INTO #Table
FROM PbrAccountInsuranceOrder AI
INNER JOIN PbrAccountTransactions T
      ON T.AccountID = AI.AccountID
            AND T.SourceID = AI.SourceID
INNER JOIN meditech_livendb..DMisProvider PROV
      ON T.ProviderID = PROV.ProviderID
INNER JOIN PbrTicket AS TI
      ON (T.AccountID = TI.AccountID)
INNER JOIN PbrPatients AS P
      ON (T.PatientID = P.PatientID)
INNER JOIN PbrAccountInsuranceCopayTypes AS AT
      ON AT.AccountID = AI.AccountID
            AND AT.SourceID = AI.SourceID
WHERE (
            T.ServiceDateTime BETWEEN @StartDate
                  AND @EndDate
            )
      AND (
            TI.ServiceDateTime BETWEEN @StartDate
                  AND @EndDate
            )
      --T.ProviderID='CARJ' 
      AND T.Type = 'C'
      AND T.Amount > 0
      AND AI.InsuranceSetEffectiveDateTime <= TI.ServiceDateTime
      AND AI.InsuranceSetEffectiveDateTime = AT.AccountIDDateTime
      AND P.MriUnitNumber LIKE 'H%'

SELECT ProviderID
      ,COUNT(InsuranceID) AS 'WV Medicaid'
INTO #WV
FROM #Table
WHERE InsType = 'WV Medicaid'
GROUP BY ProviderID
ORDER BY ProviderID

SELECT ProviderID
      ,COUNT(InsuranceID) AS 'OH Medicaid'
INTO #OH
FROM #Table
WHERE InsType = 'OH Medicaid'
GROUP BY ProviderID
ORDER BY ProviderID

SELECT ProviderID
      ,COUNT(InsuranceID) AS 'OTHER Medicaid'
INTO #Other
FROM #Table
WHERE InsType = 'Other'
GROUP BY ProviderID
ORDER BY ProviderID

SELECT DISTINCT t.ProviderID
      ,wv.[WV Medicaid]
      ,oh.[OH Medicaid]
      ,o.[OTHER Medicaid]
      ,wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid] AS 'TotalInsurances'
      ,CONVERT(NUMERIC, wv.[WV Medicaid]) / CONVERT(NUMERIC, (wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid])) AS 'WVPercentage'
      ,CONVERT(NUMERIC, oh.[OH Medicaid]) / CONVERT(NUMERIC, (wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid])) AS 'OHPercentage'
      ,(CONVERT(NUMERIC, oh.[OH Medicaid]) + CONVERT(NUMERIC, wv.[WV Medicaid])) / CONVERT(NUMERIC, (wv.[WV Medicaid] + oh.[OH Medicaid] + o.[OTHER Medicaid])) AS 'TotalPercentage'
FROM TABLE t
INNER JOIN #WV wv
      ON t.ProviderID = wv.ProviderID
INNER JOIN #OH oh
      ON t.ProviderID = oh.ProviderID
INNER JOIN #Other o
      ON t.ProviderID = o.ProviderID

DROP TABLE #OH

DROP TABLE #Other

DROP TABLE #WV

DROP TABLE #Table
1 Like
3

You haven't specified a precision or scale for your numeric datatypes so you're data has no decimal places before division. Consider the difference between:

select cast(3.14159 as numeric)/CAST(2.71828 as numeric) RETURN;

 --versus

select cast(3.14159 as numeric(5,2))/CAST(2.71828  as numeric(5,2)) RETURN;
2 Likes
4

To get the > 30% restriction, wrap the whole query as a subquery:

select ...
from (original query) q
where q.WVPercentage > 30

as an example

1 Like
5

thank you so much :slight_smile:

6

Remember to like the post that help you :wink: