Calculating Yield

I have a database that will have a date and a Judgement in it. There 2
possible judgements, GOOD and BAD.
I can filter the data to include just 1 month data, for example, Feb's data
from 02/01/06 to 02/28/06.
I would like to get output that looks like the following:
DATE YIELD
1 .90
2 .98
3 .80
4 .79
5 .60
6 1.00
...
The yield is calculated for each day as Total of (GOOD+BAD) / BAD
I'm looking to use a Stored Procedure that I pass the start and end date to
it.
Whats the best way to go about get the result set back?
BartDo you mean yield is GOOD / (GOOD + BAD)? (GOOD+BAD) / BAD will always
result in yields > 1.
I think this will provide the result you requested:
create table #Judgement(JudgementDate datetime, Judgement varchar(4))
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('01 Feb 2006', 'BAD')
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('02 Feb 2006', 'GOOD')
insert #Judgement values ('02 Feb 2006', 'BAD')
insert #Judgement values ('02 Feb 2006', 'BAD')
insert #Judgement values ('03 Feb 2006', 'GOOD')
insert #Judgement values ('03 Feb 2006', 'GOOD')
insert #Judgement values ('03 Feb 2006', 'BAD')
insert #Judgement values ('03 Feb 2006', 'BAD')
insert #Judgement values ('04 Feb 2006', 'BAD')
go
create procedure getYield
@.start datetime,
@.end datetime
as
select JudgementDate, convert(decimal(9,2), count(*)) /
convert(decimal(9,2),sum(case when Judgement = 'BAD' then 1 else 0
end))
from #Judgement
where JudgementDate between @.start and @.end
group by JudgementDate
go
exec getYield '01 Feb 2006', '03 Feb 2006'|||You are correct, i was thinking the correct equation, but I typed the wrong
one.
Anyway, I was looking over your sugestion and I undertand now. However, if
there was
no data for a particular day, wouldn't that result in a 'Divide By Zero"
error?
Bart
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142032601.550114.125100@.i39g2000cwa.googlegroups.com...
> Do you mean yield is GOOD / (GOOD + BAD)? (GOOD+BAD) / BAD will always
> result in yields > 1.
> I think this will provide the result you requested:
> create table #Judgement(JudgementDate datetime, Judgement varchar(4))
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('01 Feb 2006', 'BAD')
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('02 Feb 2006', 'GOOD')
> insert #Judgement values ('02 Feb 2006', 'BAD')
> insert #Judgement values ('02 Feb 2006', 'BAD')
> insert #Judgement values ('03 Feb 2006', 'GOOD')
> insert #Judgement values ('03 Feb 2006', 'GOOD')
> insert #Judgement values ('03 Feb 2006', 'BAD')
> insert #Judgement values ('03 Feb 2006', 'BAD')
> insert #Judgement values ('04 Feb 2006', 'BAD')
> go
> create procedure getYield
> @.start datetime,
> @.end datetime
> as
> select JudgementDate, convert(decimal(9,2), count(*)) /
> convert(decimal(9,2),sum(case when Judgement = 'BAD' then 1 else 0
> end))
> from #Judgement
> where JudgementDate between @.start and @.end
> group by JudgementDate
> go
> exec getYield '01 Feb 2006', '03 Feb 2006'
>|||Sorry, I didn't mean when the Date was missing, I meant when the denominator
was 0.
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142032601.550114.125100@.i39g2000cwa.googlegroups.com...
> Do you mean yield is GOOD / (GOOD + BAD)? (GOOD+BAD) / BAD will always
> result in yields > 1.
> I think this will provide the result you requested:
> create table #Judgement(JudgementDate datetime, Judgement varchar(4))
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('01 Feb 2006', 'BAD')
> insert #Judgement values ('01 Feb 2006', 'GOOD')
> insert #Judgement values ('02 Feb 2006', 'GOOD')
> insert #Judgement values ('02 Feb 2006', 'BAD')
> insert #Judgement values ('02 Feb 2006', 'BAD')
> insert #Judgement values ('03 Feb 2006', 'GOOD')
> insert #Judgement values ('03 Feb 2006', 'GOOD')
> insert #Judgement values ('03 Feb 2006', 'BAD')
> insert #Judgement values ('03 Feb 2006', 'BAD')
> insert #Judgement values ('04 Feb 2006', 'BAD')
> go
> create procedure getYield
> @.start datetime,
> @.end datetime
> as
> select JudgementDate, convert(decimal(9,2), count(*)) /
> convert(decimal(9,2),sum(case when Judgement = 'BAD' then 1 else 0
> end))
> from #Judgement
> where JudgementDate between @.start and @.end
> group by JudgementDate
> go
> exec getYield '01 Feb 2006', '03 Feb 2006'
>|||Using the "GOOD / (GOOD + BAD)" formula, the denominator will never be
0 because you are only looking in the Judgement table. If you were to
join to some date table in an outer join then you would do some
COALESCE on the calculations. Here is the revised format that joins to
a date table to account for missing dates:
drop table #Judgement
go
create table #Judgement(JudgementDate datetime, Judgement varchar(4))
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('01 Feb 2006', 'BAD')
insert #Judgement values ('01 Feb 2006', 'GOOD')
insert #Judgement values ('02 Feb 2006', 'GOOD')
insert #Judgement values ('02 Feb 2006', 'BAD')
insert #Judgement values ('02 Feb 2006', 'BAD')
insert #Judgement values ('03 Feb 2006', 'GOOD')
insert #Judgement values ('03 Feb 2006', 'GOOD')
insert #Judgement values ('03 Feb 2006', 'BAD')
insert #Judgement values ('03 Feb 2006', 'BAD')
insert #Judgement values ('04 Feb 2006', 'BAD')
insert #Judgement values ('05 Feb 2006', 'GOOD')
go
drop table #Date
go
create table #Date([Date] datetime)
insert #Date values ('01 Feb 2006')
insert #Date values ('02 Feb 2006')
insert #Date values ('03 Feb 2006')
insert #Date values ('03 Feb 2006')
insert #Date values ('04 Feb 2006')
insert #Date values ('05 Feb 2006')
insert #Date values ('06 Feb 2006')
insert #Date values ('07 Feb 2006')
go
drop procedure getYield
go
create procedure getYield
@.start datetime,
@.end datetime
as
select JudgementDate, case when denominator = 0 then 0 else
convert(decimal(9,2), numerator) / convert(decimal(9,2), denominator)
end
from (select dt.[Date] JudgementDate, sum(case when Judgement = 'GOOD'
then 1 else 0 end) numerator, count(*) denominator
from #Date dt
left outer join #Judgement j
on dt.[Date] = j.JudgementDate
where dt.[Date] between @.start and @.end
group by dt.[Date]) x
go
exec getYield '01 Feb 2006', '07 Feb 2006'