asked on

Remove and Replace characters from data in a field

I have a field called "fiscalmonth" that is in the format of 201101, 201102, etc with the year followed by the month. When comparing 2010 vs. 2011 data I cannot create a chart since the values are not the same. How can I change these to be like this: 201101 = Period 1, 201102 = Period 2. Then when I compare year over year, the fields will match up based on fiscalyear.

Also the query seems to take about 45-60 seconds to run. If anything stands out it the code that I could change to make faster, please let me know!

select a. fiscalyear, a.fiscalmonth,
coalesce(cast(round(((SUM([Franchise Funded])*1.0)/(SUM([franchise approved])*1.0)),4,0) as decimal (18,4)),0) as 'Franchise Funding',
coalesce(cast(round(((SUM([Franchise New Funded])*1.0)/(SUM([franchise new approved])*1.0)),4,0) as decimal (18,4)),0) as 'Franchise New Funding',
coalesce(cast(round(((SUM([Franchise Returning Funded])*1.0)/(SUM([franchise returning approved])*1.0)),4,0) as decimal (18,4)),0) as 'Franchise Returning Funding',
coalesce(cast(round(((SUM([PDL Funded])*1.0)/(SUM([PDL approved])*1.0)),4,0) as decimal (18,4)),0) as 'PDL Funding',
coalesce(cast(round(((SUM([PDL New Funded])*1.0)/(SUM([PDL New approved])*1.0)),4,0) as decimal (18,4)),0) as 'PDL New Funding',
coalesce(cast(round(((SUM([PDL Returning Funded])*1.0)/(SUM([PDL Returning approved])*1.0)),4,0) as decimal (18,4)),0) as 'PDL Returning Funding',
coalesce(cast(round(((SUM([DILP Funded])*1.0)/(SUM([DILP approved])*1.0)),4,0) as decimal (18,4)),0) as 'DILP Funding',
coalesce(cast(round(((SUM([DILP New Funded])*1.0)/(SUM([DILP New approved])*1.0)),4,0) as decimal (18,4)),0) as 'DILP New Funding',
coalesce(cast(round(((SUM([DILP Returning Funded])*1.0)/(SUM([DILP approved])*1.0)),4,0) as decimal (18,4)),0) as 'DILP Returning Funding'

from

(
SELECT fiscalyear, FiscalMonth, bda.Date,
SUM(case when processing_status = 'originated' then 1 else 0 end) as 'Franchise Funded',
COUNT(distinct(bia.bo_code)) as 'Franchise Approved',
SUM(case when processing_status = 'originated' and cust_type = 'new'then 1 else 0 end) as 'Franchise New Funded',
COUNT(distinct case when cust_type = 'new' then (bia.bo_code) else 0 end) as 'Franchise New Approved',
SUM(case when processing_status = 'originated' and cust_type in ('rtn','ret','ina_rtn','pod_rtn') then 1 else 0 end) as 'Franchise Returning Funded',
COUNT(distinct case when cust_type in ('rtn','ret','ina_rtn','pod_rtn') then (bia.bo_code) else 0 end) as 'Franchise Returning Approved',
SUM(case when processing_status = 'originated' and loan_type = 'pdl' then 1 else 0 end) as 'PDL Funded',
COUNT(distinct case when loan_type = 'pdl' then (bia.bo_code) else 0 end) as 'PDL Approved',
SUM(case when processing_status = 'originated' and loan_type = 'pdl' and cust_type = 'new' then 1 else 0 end) as 'PDL New Funded',
COUNT(distinct case when loan_type = 'pdl' and cust_type = 'new' then (bia.bo_code) else 0 end) as 'PDL New Approved',
SUM(case when processing_status = 'originated' and loan_type = 'pdl' and cust_type in ('rtn','ret','ina_rtn','pod_rtn') then 1 else 0 end) as 'PDL Returning Funded',
COUNT(distinct case when loan_type = 'pdl' and cust_type in ('rtn','ret','ina_rtn','pod_rtn') then (bia.bo_code) else 0 end) as 'PDL Returning Approved',
SUM(case when processing_status = 'originated' and loan_type = 'ilp' then 1 else 0 end) as 'DILP Funded',
COUNT(distinct case when loan_type = 'ilp' then (bia.bo_code) else 0 end) as 'DILP Approved',
SUM(case when processing_status = 'originated' and loan_type = 'ilp' and cust_type = 'new' then 1 else 0 end) as 'DILP New Funded',
COUNT(distinct case when loan_type = 'ilp' and cust_type = 'new' then (bia.bo_code) else 0 end) as 'DILP New Approved',
SUM(case when processing_status = 'originated' and loan_type = 'ilp' and cust_type in ('rtn','ret','ina_rtn','pod_rtn') then 1 else 0 end) as 'DILP Returning Funded',
COUNT(distinct case when loan_type = 'ilp' and cust_type in ('rtn','ret','ina_rtn','pod_rtn') then (bia.bo_code) else 0 end) as 'DILP Returning Approved'
FROM usonlinereporting.dbo.BO_IN_APP_QUEUE AS bia 
INNER JOIN usonlinereporting.dbo.Business_Dates_All AS bda ON DATEADD(dd, 0, DATEDIFF(dd, 0, bia.APP_DATE)) = bda.Date
WHERE bda.Fiscalyear >= '2010'
AND bia.APP_TYPE = 'std'
AND bia.PROCESSING_STATUS NOT IN ('denied') 
AND (bia.REASON_FOR_DENIAL NOT IN ('auto withdrawl during esign', 'Auto Withdrawn During LatitudePayment Return !', 'Alternate Product ILP Accepted', 
'Alternate Product PDL Accepted', 'Fraud', 'DNQ - Direct Deposit', 'DNQ - No Longer Employed', 'DNQ - Income', 'DNQ - Card Account', 
'DNQ - Work Number', 'DNQ - Home/Cell Number', 'Store Collections', 'Auto Withdrawn During ACH Return !','Auto Withdrawn During Payment Return !') 
OR bia.REASON_FOR_DENIAL IS NULL) 
AND (DATEADD(dd, 0, DATEDIFF(dd, 0, bia.APP_DATE)) < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) - 7)
GROUP BY fiscalyear, FiscalMonth, bda.Date
)a

group by fiscalyear, fiscalmonth

Open in new window