Hi all

I have set of queries that I think I could merge into one using left join, or a nested query, but I don't have enough experience of either to be able to construct it.

The long way round works fine but the MS SQL server is remote so I want the SQL server to do the work all in one go.

In addition, I run this set of queries multiple times, once for each $sid from a different, local database.

Here's the set; I start with three parameters - $sid, $from and $to - then get one result out - $total.

$querya = "SELECT opID FROM RB WHERE Status = '0'";
$resulta = mssql_query($querya);
while (list($opID) = mssql_fetch_array($resulta)){

$queryb  = "SELECT Sum(Amount) AS clTotal FROM PCLines WHERE
            sID = '$sid' AND pID = '$opID' AND
            cDate BETWEEN '$from' AND '$to'";
$resultb = mssql_query($queryb) or die('Error getting data');

if ($r = mssql_fetch_array($resultb))
{
extract($r);
}

$queryc =  "SELECT Sum(Amount) AS slTotal FROM PSLines WHERE
            sID = '$sid' AND pID = '$opID' AND
            lDate = '$from'";
$resultc = mssql_query($queryc) or die('Error getting data');

if ($r = mssql_fetch_array($resultc))
{
extract($r);
}	

$total = $total + $clTotal + $slTotal;	
}

What I really want to do is pass the SQL Server all the different $sid values at once (there are 20) with the same $from and $to values, and get 20 totals back, one for each $sid, and do it all in one transaction - if that's even possible.

One query from the 3 above would be great though!

Many thanks.

    Write a Reply...