sql server - SQL Query (Sybase): Subtract 1 from the sum of one column depending on the value of another -

i have 2 sql queries need combine 1 due sheer size of database (most of retail store locations, fine, have much more transactions)

basically, need number of transactions per employee given day. this, counting distinct number of transaction numbers per employee (each item in transaction gets own row same transaction number, needs distinct). however, there column "txnvoidmod". goal if value of column 0, count transactions normally, if value 1, need subtract off 1 because transaction voided.

here sample data

+------ -+------------+-------------+ |transnum| txnvoidmod | salesprsnid | +--------+------------+-------------+ | 115568 | 0          | 1339        | | 115568 | 0          | 1339        | | 114566 | 0          | 1339        | | 114566 | 0          | 1339        | | 115504 | 0          | 2555        | | 105551 | 0          | 0452        | | 105551 | 0          | 0452        | | 105551 | 0          | 0452        | | 105552 | 1          | 0452        | | 105552 | 1          | 0452        | | 105552 | 1          | 0452        | +--------+------------+-------------+ 

i omitting fields unnecessary example.

here query using

 select txn_pos_transactions.cashiernum salesprsnid,      (count(distinct ms.transnum))as transcnt     txn_merchandise_sale ms       inner join txn_pos_transactions on ms.transnum=txn_pos_transactions.transnum      ms.modtime '2013-06-01 %' , ms.itemdatetime '2013-06-01 %'      , (txnvoidmod=0 or txnvoidmod=1)      group txn_pos_transactions.cashiernum order salesprsnid 

i leaving out fact query querying netsales , units sold, must take account. sample results query (including omitted netsales , units portion quite verbose)

+------ -+------------+-------------+-------------+ |transcnt| unitssold  | salesprsnid | netsalesamt | +--------+------------+-------------+-------------+ | 2      | 5          | 1339        | 98.50       | | 1      | 2          | 2555        | 35.20       | | 2      | 1          | 0452        | 24.00       | +--------+------------+-------------+-------------+ 

from above, employee id "0452" has 2 transaction in results (transcnt), however, need build query reads 0 transactions because 1 transaction has txnvoidmod equal 1 , void of previous transaction txnvoidmod=0. if said "txnvoidmod=0" in clause, "0452" have 1 transaction, still need subtract off transaction txnvoidmod=1

i've tried various things such conditionals , subtracting off value of txnvoidmod, no avail trys group txnvoidmod gives me unnecessary rows. need there 1 row per salesprsnid.

any suggestions great, thanks.

by way, using sap sybase database.. seems part queries same exception of few items missing.

how this:

select    txn_pos_transactions.cashiernum salesprsnid   ,count(distinct case txnvoidmod when 1 ms.transnum end) transcnt txn_merchandise_sale ms     inner join txn_pos_transactions on ms.transnum = txn_pos_transactions.transnum  ms.modtime '2013-06-01 %'    , ms.itemdatetime '2013-06-01 %'    , txnvoidmod in(0, 1)  group txn_pos_transactions.cashiernum  order salesprsnid