• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

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

0
dplowman
Asked:
dplowman
  • 5
  • 4
1 Solution
 
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
 
sdstuberCommented:
sorry, I thought I had it fixed fast enough,  try this

(fiscalmonth % 100)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dplowmanAuthor Commented:
Looks good! Anyway to add Period to the results, such as Period 1, Period 2, etc.
0
 
sdstuberCommented:
'Period ' + cast((fiscalmonth % 100) as char(2))
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now