1.00011e+006 fix PATINDEX and POWER summary.sql

It appears that someone loaded an extra from a vendor system into Excel and then loaded it into a SQL database... Means that we ended up with scientific notation in a cost/dollar amount column.  As such, a previously numeric datatype column became varchar and broke some reports...

Here's how I fixed it (so many years of doing SQL and this was the first time I used the POWER function!):

SELECT

PatientID

...

,PriorTotalCostAMT =

CASE WHEN CTE.PRIOR_TOTAL_COSTS is not null

THEN CAST(CAST(PriorTotalCostAMT_1 as decimal(38,6))*Power(10,PriorTotalCostAMT_2) as numeric(28,4))

                  ELSE cast(Costs.PRIOR_TOTAL_COSTS as numeric(28,4)) END

...

FROM CostData Costs

...

LEFT JOIN

(

            SELECT

                PRIOR_TOTAL_COSTS

                ,PriorTotalCostAMT_1 = SUBSTRING(PRIOR_TOTAL_COSTS, 1, (PATINDEX('%[e+]%', PRIOR_TOTAL_COSTS + ' ')-1))

                ,PriorTotalCostAMT_2 = SUBSTRING(PRIOR_TOTAL_COSTS, (PATINDEX('%[e+]%', PRIOR_TOTAL_COSTS + ' ')+2),

LEN(PRIOR_TOTAL_COSTS))

            FROM CostData

            WHERE PRIOR_TOTAL_COSTS like '%e%'

                )  CTE

            ON Costs.PRIOR_TOTAL_COSTS = CTE.PRIOR_TOTAL_COSTS


However, even easier is the string function:  STR(Prior_TOTAL_COSTS)