Solved

SQL Problem

Posted on 2004-09-07
5
155 Views
Last Modified: 2010-04-05
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??
0
Comment
Question by:pin_plunder
5 Comments
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 11998995
I think you could do this in SQL, but not in a Paradox table, since it wouldn't be a simple SQL query, but you could (probably) do it in a Stored Procedure
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)???
0
 

Author Comment

by:pin_plunder
ID: 12002918
Yes, that's right, I want to find all the permutation of p and q with all rows and get two fields for each one: Probabilidad, which is the result of the multiplication of each p or q, and MontoTotal which is the sum of the amount corresponding to each p or q (ie. if it's pa the amount is 0, if it's qa the amount is 5000, so I want to sum all these for each p or q that in the previous field I multiplied)

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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12003481
usual it is not solveable with a sql-statement,
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 ;-)
0
 
LVL 4

Accepted Solution

by:
mottor earned 250 total points
ID: 12004200
Lets look at the problem binary.
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.002),(0.991,0.009));
 s: array[0..2,0..1] of double = ((0.0,100000.00),(0.0,50000.00),(0.0,200000.00));
 r: array[0..1,0..7] of double;
procedure TForm1.Button1Click(Sender: TObject);
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(FloatToStrF(r[0,i],ffFixed,12,9)+'          '+FloatToStr(r[1,i]));
end;


Regards,
mottor
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12010362
With a good SQL database you could use the cross join option.

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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now