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

dplowmanAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
'Period ' + cast((fiscalmonth % 100) as char(2))
0
 
sdstuberCommented:
use mod (fiscalmonth , 100)  where you have fiscalmonth
0
 
dplowmanAuthor Commented:
getting; Msg 195, Level 15, State 10, Line 1
'mod' is not a recognized built-in function name.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
sorry, I thought I had it fixed fast enough,  try this

(fiscalmonth % 100)
0
 
dplowmanAuthor Commented:
Looks good! Anyway to add Period to the results, such as Period 1, Period 2, etc.
0
 
dplowmanAuthor Commented:
Great work! Points have been awarded!
0
 
dplowmanAuthor Commented:
One more thing, when I order the results it looks like this:

Period 1
Period 10
Period 11
Period 2
Period 3

Any idea of how to fix this?
0
 
sdstuberCommented:
don't order by the result string,  order by original column  

   order by fiscalmonth

or order by just the month portion  

order by (fiscalmonth % 100)
0
 
dplowmanAuthor Commented:
Thanks, that makes sense!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.