Quick Stats Query.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Quick Stats Query....

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-12-07 : 11:23:42

I am writing a program for my upcoming wedding, and wanted to write a query for some stats.

In my table I have the following info:

Wedding_Guest
-------------
ID - ID
GuestName - VarChar
Probability - Int (0-100)

What I would like to do is put together some sort of query that will give me a rough count of how many people will be coming. Something like:

100% Probability 25 Guests Attending
90% Probability 50 Guests Attending
80% Probability 75 Guests Attending
40% Probability 5 Guests Attending

Or something like that.... Any one have any ideas or suggestions? I'm kind of lost....

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-07 : 20:17:22

Rough counting may be done like this:

create table #t (gid int, p float(8), u float(8))
insert into #t (gid, p)
select 1, 0.75 union all
select 2, 0.80 union all
select 3, 0.85 union all
select 4, 0.90 union all
select 5, 0.55

declare @q float select @q=avg(p) from #t
declare @m int select @m=max(gid) from #t
declare @c int set @c=@m
declare @i int set @i=1

while @i<=@m
begin
update #t set u=@c*power(@q,gid)*power(1-@q,@m-gid) where gid=@i
set @i=@i+1
set @c=@c*(@m-@i+1)/@i
end

select gid, 100*p p, 100*u u from #t
drop table #t

Result:


gid         p            u                        
----------- ------------ ------------------------ 
1           75.0         1.0773878
2           80.0         7.2138138 << probability to get 2 guests
3           85.0         24.150595 << probability to get 3 guests
4           90.0         40.425999 << probability to get 4 guests
5           55.0         27.067842

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-12-07 : 23:18:51

Thats awsome, exactly what I was looking for. The one thing that confuses me though is this part:
select 1, 0.75 union all
select 2, 0.80 union all
select 3, 0.85 union all
select 4, 0.90 union all
select 5, 0.55

'select 4, 0.90' would mean I am inserting 4 Guests with a probability of .90 into the temp table #t?

and what does 'union all' do?

Thanks Again!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-07 : 23:45:04

The union is inserting sample data into #t

Nice solution Stoad..

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-08 : 02:52:42

interclubs,
you can replace this part:
create table #t (gid int, p float(8), u float(8))
insert into #t (gid, p)
select 1, 0.75 union all
select 2, 0.80 union all
select 3, 0.85 union all
select 4, 0.90 union all
select 5, 0.55
with
create table #t (gid int, p float(8), u float(8))
insert into #t (gid, p) select ID, Probability/100.00 from Wedding_Guest

Here gid is GuestID (I hope there are no gaps in them and they start from 1).
And, just in case, exact meaning of the following result row:

4 ________ 90.0 ________ 40.425999 ___ << probability to get 4 guests

is:
probability to get on the wedding exactly (and only!!) 4 (ANY!!) guests = 40.4
(I used GuestID also as Indicator of Number of Attending Guests just
for convenience).

Jay,
not nice at all..
More precisely: less deviation of guest probabilities is - more reliable result we get.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-08 : 17:00:05

.. and this is a precise solution (but still not quite nice I think).
Note the begin/commit tran "brackets" - useful trick for
to boost performance.

begin tran
create table #t (gid int, p float, u float default 0)
insert into #t (gid, p)
select 1, 0.75 union all
select 2, 0.80 union all
select 3, 0.85 union all
select 4, 0.90 union all
select 5, 0.55
create table #tt (n int)

declare @m int, @i int, @j int, @k int, @kk int, @u float
select @m=max(gid) from #t set @i=1 set @k=1
while @i<@m begin set @k=@k+power(2,@i) set @i=@i+1 end
set @kk=1

while @kk<=@k
begin
set @u=1 set @j=0
while @j<@m
begin
if @kk & power(2,@j)=power(2,@j) insert into #tt select @j+1 set @j=@j+1
end
select @u=@u*p from #t where gid in (select n from #tt)
select @u=@u*(1-p) from #t where gid not in (select n from #tt)
update #t set u=u+@u where gid=(select count(*) from #tt)
truncate table #tt
set @kk=@kk+1
end

select gid,
cast(100*p as char(20)) [p%], cast(100*u as char(20)) [u%] from #t
drop table #tt
drop table #t
commit tran

Sample results:


gid         p%                   u%                   
----------- -------------------- -------------------- 
1           75                   0.7725              
2           80                   6.485               
3           85                   24.8225             
4           90                   42.6412             
5           55                   25.245

gid         p%                   u%                   
----------- -------------------- -------------------- 
1           90                   1.55                
2           90                   14.64               
3           90                   50.22               
4           30                   29.16               
5           20                   4.374

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources