If......Else

[Arpan]

A SQL Server 7.0 database table named Table1 has 3 fields - ID (which
is unique & the primary key), Quantity & Amount. There is another
database table named Table2 which has 2 fields - ID & Taxable (records
under the Taxable column can have only 2 values - Yes or No). Table1 &
Table2 can be JOINed using the primary key ID. Now I have to display
these records in a web page in a HTML table in this way:

--------------------------------------
ID Charge Taxable
---------------------------------------
1 500.00 No
2 400.00 Yes
3 300.00 No
4 200.00 Yes
----------------------------------------


Charge is the product of Table1.Quantity*Table1.Amount. The SQL query
to retrieve the records is this:

SELECT Table1.ID,(Table1.Quantity*Table1.Amount) AS
'Charge',Table2.Taxable FROM Table1 INNER JOIN Table2 ON
Table1.ID=Table2.ID

Apart from the HTML table displayed above, the web page should also
show the sum of charges which is taxable & also the sum of charges
which is not taxable. For e.g. using the above 4 records, the sum of
charges which is non-taxable is 500+300=800 (since Taxable is No for
the charges 500 & 300) where as the sum of charges which is taxable is
400+200=600 (since Taxable is Yes for the charges 400 & 200). Now how
do I find the 2 sums i.e. sum of charges which is taxable & sum of
charges which is non-taxable?

Thanks,

Arpan

[jason kennedy]

there are a few ways of accomplishing what you ask

you could create a couple more recordsets to return the totals you require,
or you could add some code to your page, as below

there are lots of clever folks on here, so someone will most likely provide
a better solution than this,
but here's a basic method (ASP/VB), i've added the HTML of a table to
indicate where the code would be located.
hope this helps

<table>
<%
not_tax = 0
tax = 0

while not recordset.eof

if recordset("taxable") = "No" then
not_tax = not_tax + recordset("charge")
else
tax = tax + recordset("charge")
end if %>
<tr><td>...</td></tr>

<% recordset.movenext
wend %>
</table>

<% response.write "non-taxable = " & not_tax
response.write "taxable = " & tax %>

Jason

"Arpan" <arpan_de@hotmail.com> wrote in message
news:74df0ab.0307181446.591882d3@posting.google.co m...

> A SQL Server 7.0 database table named Table1 has 3 fields - ID (which
> is unique & the primary key), Quantity & Amount. There is another
> database table named Table2 which has 2 fields - ID & Taxable (records
> under the Taxable column can have only 2 values - Yes or No). Table1 &
> Table2 can be JOINed using the primary key ID. Now I have to display
> these records in a web page in a HTML table in this way:
>
> --------------------------------------
> ID Charge Taxable
> ---------------------------------------
> 1 500.00 No
> 2 400.00 Yes
> 3 300.00 No
> 4 200.00 Yes
> ----------------------------------------
>
>
> Charge is the product of Table1.Quantity*Table1.Amount. The SQL query
> to retrieve the records is this:
>
> SELECT Table1.ID,(Table1.Quantity*Table1.Amount) AS
> 'Charge',Table2.Taxable FROM Table1 INNER JOIN Table2 ON
> Table1.ID=Table2.ID
>
> Apart from the HTML table displayed above, the web page should also
> show the sum of charges which is taxable & also the sum of charges
> which is not taxable. For e.g. using the above 4 records, the sum of
> charges which is non-taxable is 500+300=800 (since Taxable is No for
> the charges 500 & 300) where as the sum of charges which is taxable is
> 400+200=600 (since Taxable is Yes for the charges 400 & 200). Now how
> do I find the 2 sums i.e. sum of charges which is taxable & sum of
> charges which is non-taxable?
>
> Thanks,
>
> Arpan