Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Solved

Posted on 2004-09-07

hi! everybody...well, my problem is the following:

I've got this table:

p monto p q monto q

a 0,995 0 0,005 100.000,00

b 0,998 0 0,002 50.000,00

c 0,991 0 0,009 200.000,00

And need to create another table with the following results

Caso Probabilidad MontoTotal

pa pb pc 0,98407291 0

pa pb qc 0,00893709 200.000,00

pa qb pc 0,00197209 50.000,00

pa qb qc 0,00001791 250.000,00

qa pb pc 0,00494509 100.000,00

qa pb qc 4,491E-05 300.000,00

qa qb pc 0,000010 150.000,00

qa qb qc 9E-08 350.000,00

probability is for example> pa x pb x pc, and MontoTotal monto qa + monto pb + monto pc

I made theses tables in excel...but I would like to do all this job in paradox and quick sql, cause as you may have noticed with only 3 cases the resulting table had 8 rows (2 power 3). Imagine if there are 10 cases (2 power 10) or 1000 (2 power 1000).

so, is there any way to do this in sql??

thanks in advance,

paul.

pd: as I'm using delphi I thought of solving this problem using recursion...what do you think?? would it be faster using sql, if there is any chance to do so??

I've got this table:

p monto p q monto q

a 0,995 0 0,005 100.000,00

b 0,998 0 0,002 50.000,00

c 0,991 0 0,009 200.000,00

And need to create another table with the following results

Caso Probabilidad MontoTotal

pa pb pc 0,98407291 0

pa pb qc 0,00893709 200.000,00

pa qb pc 0,00197209 50.000,00

pa qb qc 0,00001791 250.000,00

qa pb pc 0,00494509 100.000,00

qa pb qc 4,491E-05 300.000,00

qa qb pc 0,000010 150.000,00

qa qb qc 9E-08 350.000,00

probability is for example> pa x pb x pc, and MontoTotal monto qa + monto pb + monto pc

I made theses tables in excel...but I would like to do all this job in paradox and quick sql, cause as you may have noticed with only 3 cases the resulting table had 8 rows (2 power 3). Imagine if there are 10 cases (2 power 10) or 1000 (2 power 1000).

so, is there any way to do this in sql??

thanks in advance,

paul.

pd: as I'm using delphi I thought of solving this problem using recursion...what do you think?? would it be faster using sql, if there is any chance to do so??

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

- Help others & share knowledge
- Earn cash & points
- Learn & ask questions

5 Comments

anyway... seems your option is to do it in Delphi... are you trying to find the permutations of

p and q with all the rows (a,b,c)???

I already solved the problem using recursion in delphi...but this code is really slow....

how would you solve it using a stored procedure??? any implementation would be usefull....

thanks!

paul.

because its a crosstable-issue, which is not

supported by most databases, even not by paradox

>how would you solve it using a stored procedure???

paradox do not have storedProcedures

try to use a reporting-system, which can build up a crosstab, like

TDecissionCube, QuickReport, Reportbuilder, etc.

meikl ;-)

If the first row is number 0, look at p as binary 0, look at q as binary 1,

on the row number 5 we have

qa pb qc or binary 1 0 1 or decimal 5

We can use this to find if we hawe q or p on certain position:

uses math;

{$R *.dfm}

var

p: array[0..2,0..1] of double = ((0.995,0.005),(0.998,0.00

s: array[0..2,0..1] of double = ((0.0,100000.00),(0.0,5000

r: array[0..1,0..7] of double;

procedure TForm1.Button1Click(Sender

var i,j :integer;

pp,ss: double;

n,m: word;

begin

for i := 0 to 7 do begin

pp := 1.0; ss := 0.0;

n := i;

for j := 2 downto 0 do begin

divmod(n,2,n,m); //-- Here we are finding q or p

pp := pp*p[j,m];

ss := ss+s[j,m];

end;

r[0,i] := pp;

r[1,i] := ss;

end;

for i := 0 to 7 do

Memo1.Lines.Add(FloatToStr

end;

Regards,

mottor

First make the table like this:

name=tab(fields t,i,p,m)

t=a i=p p=0,995 m=0

t=a i=q p=0,005 m=100

t=b i=p p=0,998 m=0

t=b i=q p=0,002 m=50

t=c i=p p=0,995 m=0

t=c i=q p=0,001 m=200

select

a.i + a.t + ' ' + b.i + b.t + ' ' + c.i + c.t as Caso,

a.p * b.p * c.p as Probabilidad,

a.m + b.m + c.m as MontoTotal

from

tab a cross join tab b cross join tab c

where

a.t = 'a' and b.t = 'b' and c.t = 'c'

This should work with SQL Server for example

Regards Jacco

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video.
If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

- Document Management
- Document Imaging
- Printers and Scanners
- Software-Other
- Images and Photos
- Photos / Graphics Software, OCR, *tiff, *PaperPort, *PDF

Course of the Month10 days, 11 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.