knackje
Posts: 30
|
| Posted: 01/10/2009, 2:14 AM |
|
No never heard about elaborate.. maybe you can help me with it.. if it is the solution. First below my sql statement.
SELECT
finan_id,
persoon_financien.Kampkrant AS persoon_financien_Kampkrant,
KG1,
Bet1,
KG2,
Bet2,
KG3,
Bet3,
Toko,
persoon_financien.Jaar AS persoon_financien_Jaar,
fk_persoon,
PersoonNr,
Achternaam,
Roepnaam,
KampGroep,
Papieren_krant,
Collecte,
GGD,
initiatie.*,
`E-mail`
FROM (persoon_financien INNER JOIN persoon_gegevens ON
persoon_financien.fk_persoon = persoon_gegevens.PersoonNr) INNER JOIN initiatie ON
persoon_financien.jaar = initiatie.jaar
WHERE persoon_gegevens.Achternaam LIKE '%{s_Achternaam}%'
AND ( persoon_financien.jaar in (select max(jaar) from initiatie) )
AND persoon_gegevens.Actief = {CheckBox1}
AND persoon_gegevens.KampGroep <> '{Expr0}'
AND persoon_gegevens.KampGroep <> '{Expr1}'
ORDER BY Achternaam
To calculated the cost of the value KK
$res = mysql_query("SELECT max(Kampkrant) FROM `initiatie`")
or die(mysql_error());
$kosten = mysql_result($res, 0, 0);
if($persoon_financien_persoon1->Krant->GetValue() == 1){
$persoon_financien_persoon1->KK->SetValue($kosten);
//($Tasks->Expended_Time->GetValue()*100)/$Tasks->Estimated_Time->GetValue());
}
else{ $persoon_financien_persoon1->KK->SetValue(0);
}
To calculated the cost of the value 'DG'
$res = mysql_query("SELECT kampkosten_oud, kampkosten_jong, kampkosten_leiding FROM `initiatie` where jaar = (select max(jaar) from `initiatie`)")
or die(mysql_error());
$kosten_oud = mysql_result($res, 0, 0);
$kosten_jong = mysql_result($res, 0, 1);
$kosten_lei = mysql_result($res, 0, 2);
if($persoon_financien_persoon1->Kampgroep->GetValue() == 'TGJ')
{
$persoon_financien_persoon1->DG->SetValue($kosten_oud);
//($Tasks->Expended_Time->GetValue()*100)/$Tasks->Estimated_Time->GetValue());
}
elseif( $persoon_financien_persoon1->Kampgroep->GetValue() == 'LEIDING') {
$persoon_financien_persoon1->DG->SetValue($kosten_lei);
}
elseif( $persoon_financien_persoon1->Kampgroep->GetValue() == 'Staf') {
$persoon_financien_persoon1->DG->SetValue($kosten_lei);
}
elseif($persoon_financien_persoon1->Kampgroep->GetValue() == 'TGM')
{
$persoon_financien_persoon1->DG->SetValue($kosten_oud);
}
elseif($persoon_financien_persoon1->Kampgroep->GetValue() == 'JGJ')
{
$persoon_financien_persoon1->DG->SetValue($kosten_oud);
}
elseif($persoon_financien_persoon1->Kampgroep->GetValue() == 'JGM')
{
$persoon_financien_persoon1->DG->SetValue($kosten_oud);
}
else { $persoon_financien_persoon1->DG->SetValue($kosten_jong);
}
The values of the KK and DG values from above are needed to calculated the NTB value from below..
if($persoon_financien_persoon1->DG->GetValue() > 0 ){
$persoon_financien_persoon1->NTB->SetValue($persoon_financien_persoon1->DG->GetValue()
+
$persoon_financien_persoon1->collecte->GetValue()
+
$persoon_financien_persoon1->Toko->GetValue()
+
$persoon_financien_persoon1->KK->GetValue()
-
$persoon_financien_persoon1->Bet1->GetValue()
-
$persoon_financien_persoon1->Bet2->GetValue()
-
$persoon_financien_persoon1->Bet3->GetValue());
}
The NTB value need to be sorted when a user want it.... hope you can help.
|
damian
Posts: 838
|
| Posted: 01/10/2009, 11:04 PM |
|
ok - im not going to attempt to rewite your query
i have run up a sample table
table:sample
id| a| b| c|
1 | 1| 1| 1|
2 | 1| 2| 2|
3 | 1| 3| 3|
4 | 2| 1| 1|
5 | 2| 2| 2|
6 | 2| 3| 3|
7 | 3| 1| 1|
8 | 3| 2| 2|
9 | 3| 3| 3|
my query is:
select a,b,c,
case when a>b
then ((a+b+c)*2)
else
case when a=b
then ((a+b+c)*20)
else ((a+b+C)*200)
end
end as d
from sample
order by d desc
this returns:
a| b| c| d
2| 3| 3| 1600
1| 3| 3| 1400
1| 2| 2| 1000
3| 3| 3| 180
2| 2| 2| 120
1| 1| 1| 60
3| 2| 2| 14
3| 1| 1| 10
2| 1| 1| 8
the value of d is based on a calculation
there are 3 different calculations, depending on whether a is greater than, equal to, less than b
the resultant data is sorted by d in descending order
codecharge will 'see' d and you can add a sorter
you should be able to work out your query based on this
regards
damian
_________________
if you found this post useful take the time to help someone else.... :)
|
