Dividing counts

I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:

select count(*)[No. of Tickets Overdue],
case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]
from whd.priority_type pt
inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6'
group by pt.due_hours
order by pt.due_hours desc
COMPUTE SUM(count(*))

select count(*)[Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6'

--COMPUTE [No. of Tickets Overdue]/[Count2]

I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated.

Thanks in advance.

We need more info here and better if you can post DDL, sample data (insert statements) and expected result. The help should be mutual, shouldn't it?

Code Snippet

;with cnt1

as

(

select

pt.due_hours,

count(*)[No. of Tickets Overdue],

case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]

from

whd.priority_type pt
inner join

whd.job_ticket j

on pt.priority_type_id = j.priority_type_id

where

j.status_type_id = '1'

and j.deleted = '0'

and not j.priority_type_id = '5'

and not j.priority_type_id = '6'

group by

pt.due_hours

),

cnt2

as

(

select count(*) as [Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'

)

select

a.[No. of Tickets Overdue],

a.[No. of Tickets Overdue] * 1.,

b.Count2,

nullif(b.Count2, 0)

from

cnt1 as a cross join cnt2 as b

order by

a.due_hours desc

COMPUTE SUM(a.[No. of Tickets Overdue]), sum(a.[No. of Tickets Overdue] * 1. / nullif(b.Count2, 0))

AMB

|||

You may have to modify this somewhat, I'm not exactly sure what the pt.due_hours is representing...

Code Snippet

select

(

select

count(*)

from

whd.priority_type pt

inner join

whd.job_ticket j

on

pt.priority_type_id=j.priority_type_id

where

j.status_type='1'

and j.deleted='0'

and j.priority_type_id not in ('5','6')

and datediff(hh,getDate(),j.report_date) >= pt.due_hours

)

/

(

select

count(*)

from

whd.priority_type pt

inner join

whd.job_ticket j

on

pt.priority_type_id=j.priority_type_id

where

j.status_type='1'

and j.deleted='0'

and j.priority_type_id not in ('5','6')

) 'PercentOverdue'

|||Thanks for the reply, and I actually had more with that case statement but I realized I didn't need it and I forgot to consolidate the case into a datediff. Anyways, well I like what you did with the SQL but I get a big fat '0' for my answer which I know isn't right. pt.due_hours is a field in the database that our ticketing system looks at and if the ticket has been open longer than the due hours than it is overdue. I'm trying to get a percentage based on how many tickets are overdue compared to overall open tickets. I tried multiplying the first select statement by 1. but still same result with a decimal and about 8 zeros. Thanks for the help.|||

Got it! Thanks Anthony Martin! Heres the code I just had to add *1. in front of the / and that worked. So, sorry but here's another question how do I round to the .00 instead of 12 numbers after the decimal point. Thanks again and thanks to hunchback I would've never figured out the *1. if I hadn't looked at your example. Thanks.

select
(select count(datediff(hh,j.report_date,getdate()))
from whd.job_ticket j
inner join whd.priority_type pt on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and j.priority_type_id not in('5','6')and datediff(hh,j.report_date, getdate()) >= pt.due_hours
)*1.
/
(
select count(*)[Count2]
from whd.job_ticket j
where j.status_type_id = '1' and j.deleted = '0'
)'% Overdue'

|||

No problem, you could cast the entire thing like this...

select
cast((select count(datediff(hh,j.report_date,getdate()))
from whd.job_ticket j
inner join whd.priority_type pt on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and j.priority_type_id not in('5','6')and datediff(hh,j.report_date, getdate()) >= pt.due_hours
)*1.
/
(
select count(*)[Count2]
from whd.job_ticket j
where j.status_type_id = '1' and j.deleted = '0'
) as numeric(10,2)) '% Overdue'

|||Nice Thanks!