SELECT SUM(CASE WHEN Right(Trn_code,1)='0' THEN 1 ELSE -1 END) AS Trans, SUM(amount * CASE WHEN Right(Trn_code,1)='0' THEN 1 ELSE -1 END) AS purchases ...HTH
>Select MerchantId, > sum(Iif(Right(Trn_code,1) = "0", 1, -1)) As Trans, ; > sum(Iif(Right(Trn_code,1) = "0", amount, -amount)) As Purchases ; >from History ; >where filedate Between dDate1 And dDate2 ; > and (Trn_code = "P0" or Trn_code = "P1") ; > country_cd = lcCtry_Cd ; >group By MerchantId ; >into Cursor Temp1 >>Codes used are many but we are interested in only P0 and P1. There is a lookup table that tells us if a code is a "+" or a "-" for a proper SUM(). The data cannot be changed. The amounts are always positive in the underlying data. The query has to decide if it is plus or minus for the sum.
>SELECT CASE code >WHEN 0 THEN 'Something' >WHEN 1 THEN 'Another' >WHEN 2 THEN 'Whatever' >ELSE 'N/A' >END >>But I cannot get it to work for my case.
>Select MerchantId, >case Trn_code >when 'P0' then 1 >else -1 >end as Trans >from History >where filedate='04/01/2004' >>this works. But when I start adding SUM() and then also the part on Sum(Amount) it fails.