[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

SQL Query Count ID ending with 0-6 and 7-9

Hello,

I am trying to create a query to only count application numbers that end between 0-6, and compare them to applications that end between 7-9. For example application nubmer 1126 would be in the 0-6 field, and 1118 would be in the 7-9 field. Any suggestions?
select FiscalMonth, FiscalWeek, COUNT(app_no) as '0-6', COUNT(app_no) as '7-9'
from USOnlineReporting.dbo.BO_IN_APP_QUEUE bia
inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = DATEADD(dd,0,datediff(dd,0,app_date))
inner join USOnlineReporting.dbo.CA_SS_STORE css on css.ST_CODE = bia.ST_CODE
where STATE_ID not in ('IN','IL','NV','OK')
and CUST_TYPE = 'new'
and FiscalMonth >= '201109'
and PROCESSING_STATUS <> 'denied'
and IS_NON_DIRECT_DEP = 'n'
group by FiscalMonth, FiscalWeek
order by fiscalweek

Open in new window

0
dplowman
Asked:
dplowman
  • 5
1 Solution
 
ajcheung78Commented:
Can you provide some sample data and what exactly is the comparison that you are expecting to be performed?
0
 
dplowmanAuthor Commented:
basically i just need to count loan numbers ending from 0-6 and compare then with the count of loan numbers ending from 7-9. So if there are 100 applications, 0-6 may represent 70, whereas 7-9 may be 30. I am trying this syntax but getting the same result for each column.
select FiscalMonth, FiscalWeek, 
count(case when RIGHT(app_no,1) <= 6 then 1 else 0 end) as '0-6',
count(case when RIGHT(app_no,1) > 6 then 1 else 0 end) as '7-9'
from USOnlineReporting.dbo.BO_IN_APP_QUEUE bia
inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = DATEADD(dd,0,datediff(dd,0,app_date))
inner join USOnlineReporting.dbo.CA_SS_STORE css on css.ST_CODE = bia.ST_CODE
where STATE_ID not in ('IN','IL','NV','OK')
and CUST_TYPE = 'new'
and FiscalMonth >= '201109'
and PROCESSING_STATUS <> 'denied'
and IS_NON_DIRECT_DEP = 'n'
group by FiscalMonth, FiscalWeek
order by fiscalweek

Open in new window

0
 
dplowmanAuthor Commented:
Figured it out, I was using count rather than sum!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dplowmanAuthor Commented:

select FiscalMonth, FiscalWeek, 
sum(case when RIGHT(app_no,1) <= 6 then 1 else 0 end) as '0-6',
sum(case when RIGHT(app_no,1) > 6 then 1 else 0 end) as '7-9'
from USOnlineReporting.dbo.BO_IN_APP_QUEUE bia
inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = DATEADD(dd,0,datediff(dd,0,app_date))
inner join USOnlineReporting.dbo.CA_SS_STORE css on css.ST_CODE = bia.ST_CODE
where STATE_ID not in ('IN','IL','NV','OK')
and CUST_TYPE = 'new'
and FiscalMonth >= '201109'
and PROCESSING_STATUS <> 'denied'
and IS_NON_DIRECT_DEP = 'n'
group by FiscalMonth, FiscalWeek
order by fiscalweek

Open in new window

0
 
dplowmanAuthor Commented:
Posted the question too quick, figured it out on my own!
0
 
dplowmanAuthor Commented:
Figured this out on my own.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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