working out totals value of a field

[fife]

Hi all

 

I have a query which echos all parts used in a job from a database.  The table in question has a qty for amount used and a status of 'I' to say it has been used.

 

Parts can also have a status of 'N'.  This means they were initially sent out but were not used at the job and returned. Unfortunately due to database design which can not be changed as its a MS SQL program the returned part creates a new row instead of just being knocked of the total used. It gets the status of N

 

this means that when I echo my parts qty with the query below its not taking into account that some parts were sent back.

 

Now before I show the query let me explain what I have tried.  I have tried using array_diff() to compare the arrays but realized that this was wrong.  I have tried running the second query for the returns within the loop of the first query and then manipulating the results.  This too was wrong.  I tried using WHERE NOT EXISTS on the querys but this was removing duplicates not minus-ing  the qty value.  That leaves me to where i am now. completely stuck.  Here is the parts used query.  Please note this is a mssql query

 

//the query for parts used in the header
 $query_rs_parts = sprintf("SELECT Call_Parts_Used.Part_No, Calls.Order_No, Calls.Call_Ref, Call_Parts_Used.Qty, Call_Parts_Used.Date, Call_Parts_Used.Description,    LU_Call_Part_Status.Part_Status_DescFROM    { oj (Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.Call_Parts_Used Call_Parts_Used ON        Calls.Call_Ref = Call_Parts_Used.Link_to_Call)     INNER JOIN Siclops_Dispense.dbo.LU_Call_Part_Status LU_Call_Part_Status ON        Call_Parts_Used.Part_Status = LU_Call_Part_Status.Part_Status}WHERE  Calls.Call_Ref = '$colname_rs_call' AND Call_Parts_Used.Part_Status = 'I' ORDER BY Call_Parts_Used.Description ASC");$rs_parts = odbc_exec($conn, $query_rs_parts);$row_rs_parts = odbc_fetch_array($rs_parts);$totalRows_rs_parts = odbc_num_rows($rs_parts);  //on the page I have the loop<?php if ($totalRows_rs_parts > 0){?>                <h3>Parts Used</h3><table width="600" border="0" cellpadding="5">  <tr>    <td width="494"><strong>Description</strong></td>    <td width="80" align="right"><strong>Qty</strong></td>    </tr>  <?php do { ?>  <tr>    <td> <?php echo $row_rs_parts['Description'];?></td>    <td align="right"><?php echo number_format($row_rs_parts['Qty']); ?></td>    </tr>  <?php } while($row_rs_parts = odbc_fetch_array($rs_parts)); ?></table> <?php } else{echo "No parts have been used!";}  ?>

[fife]

So the question is I need to show the total of the parts used minus the total of parts returned!  (realized I didnt actually ask the question).  Is this possible?

[Psycho]

Um, why are you using sprintf() and putting the variables IN the string??? Either don't use sprintf() or take the variables out of the string and use sprintf() as it is meant to be.

 

Anyway, I'm not understanding your query completely, but the solution should be simple. Just query ALL the parts associated with the job (those with I and those with N). Then, in the SELECT statement do a calculation of the quantity based on the I or N to make qty value positive or negative. And, finally, add a GROUP BY on the part ID/No with a sum of the part QTY.

 

Try this

 

$query = "SELECT SUM(IF(Call_Parts_Used.Part_Status='I', Call_Parts_Used.Qty, Call_Parts_Used.Qty * -1)) as qty_used,
                 Call_Parts_Used.Part_No, Call_Parts_Used.Date, Call_Parts_Used.Description,
                 Calls.Order_No, Calls.Call_Ref,
                 LU_Call_Part_Status.Part_Status_Desc
          FROM  { oj (Siclops_Dispense.dbo.Calls Calls
                  INNER JOIN Siclops_Dispense.dbo.Call_Parts_Used Call_Parts_Used
                    ON  Calls.Call_Ref = Call_Parts_Used.Link_to_Call)
                  INNER JOIN Siclops_Dispense.dbo.LU_Call_Part_Status LU_Call_Part_Status
                    ON Call_Parts_Used.Part_Status = LU_Call_Part_Status.Part_Status}
          WHERE  Calls.Call_Ref = '$colname_rs_call'

          GROUP BY Call_Parts_Used.Part_No

          ORDER BY Call_Parts_Used.Description ASC";

[fife]

Hi Psycho and all.

 

Thank you for your reply and sorry for my late one.  Ok I have tried this and fixed my query to stop using sprintf but it just shows no parts if I put this in.  Psycho you seem to be on the right path but for some reason the query still fails.

 

At the minute I have the query for just parts used as....

 

 
$query = "SELECT Call_Parts_Used.Part_No, Calls.Order_No, Calls.Call_Ref, Call_Parts_Used.Qty, Call_Parts_Used.Date, Call_Parts_Used.Description,    LU_Call_Part_Status.Part_Status_DescFROM    { oj (Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.Call_Parts_Used Call_Parts_Used ON        Calls.Call_Ref = Call_Parts_Used.Link_to_Call)     INNER JOIN Siclops_Dispense.dbo.LU_Call_Part_Status LU_Call_Part_Status ON        Call_Parts_Used.Part_Status = LU_Call_Part_Status.Part_Status}WHERE
    Calls.Call_Ref = '$colname_rs_call' AND Call_Parts_Used.Part_Status = 'I' "

 

This works fine.  If we add GROUP BY Call_Parts_Used.Part_No to this query all of a sudden even this query comes back with no parts.  Makes me think there is a deeper routed problem.

 

 

Anyway .  Basically I want to show all parts used "status I" minus the parts returned "status N"  Each part has a qty field to state how many times it has been used or returned (depending on its status obviously). That seems to be it.  I thought it would be simple but its causing major problems.

 

 

[Barand]

Using your spec

part       |  status  |  qty used

-----------|----------|-----------

widget     |     I    |     1

gixmo      |     I    |     1

whatsit    |     N    |    -1

----------------------|-----------

Total                 |     1       ???

I would have thought this makes more sense

part       |  status  |  qty used

-----------|----------|-----------

widget     |     I    |     1

gixmo      |     I    |     1

whatsit    |     N    |    

----------------------|-----------

Total                 |     2

[fife]

Hi Barand.  thanks for your reply.  yes this database is a very old application and as such has weird table structures that we cant change or mess with.  The table you wrote though actually looks like this

 

part       |  status  |  qty used

-----------|----------|-----------

widget     |     I    |     1

gixmo      |     I    |     1

widget     |     N    |    1

----------------------|-----------
 

so the gixmo was used but the widget was returned and not used