Case, Select conditional requirments

Question

Operating in an Excel query, I need a conditional statement that will read one field, and based on that value, set another field to a minus (or not).

My SQl code is as follows:

SELECT "_bvSTTransactionsFull".txdate, 
       SUM("_bvSTTransactionsFull".debit)            AS 'TOTALDebit', 
       SUM("_bvSTTransactionsFull".credit)           AS 'TOTALCredit', 
       SUM("_bvSTTransactionsFull".tax_amount)       AS 'TOTALTax_Amount', 
       SUM("_bvSTTransactionsFull".VALUE)            AS 'TOTALValue', 
       SUM("_bvSTTransactionsFull".actualvalue)      AS 'TOTALActualValue', 
       SUM("_bvSTTransactionsFull".actualsalesvalue) AS 'TOTALActualSalesValue', 
       SUM("_bvSTTransactionsFull".profit)           AS 'TOTALProfit' 
FROM   sqlschema.dbo."_bvSTTransactionsFull" "_bvSTTransactionsFull" 
WHERE  ( "_bvSTTransactionsFull".txdate >=? 
         AND "_bvSTTransactionsFull".txdate <=? ) 
GROUP  BY "_bvSTTransactionsFull".txdate, 
          "_bvSTTransactionsFull".description 
HAVING ( "_bvSTTransactionsFull".description LIKE 'POS Sale' ) 
        OR ( "_bvSTTransactionsFull".description LIKE 'POS Return' ) 
ORDER  BY "_bvSTTransactionsFull".txdate

I need the select query to look at a field named "ActualQuantity" (in the table "_bvSTTransactionsFull") and if this field is <0 , then Tax_Amount = -(Tax_Amount), or if ActualQuantity >=0, then Tax_Amount = Tax_Amount.

Please note the query is "summed", so I assume this conditional aspect needs to be handled before summation takes place. The query summates approximately 100 000 records into daily totals.

score 0 · Answer 1 · edited May 23 '17 at 12:11

SELECT "_bvSTTransactionsFull".txdate, 
       SUM("_bvSTTransactionsFull".debit)            AS 'TOTALDebit', 
       SUM("_bvSTTransactionsFull".credit)           AS 'TOTALCredit', 
       SUM(case when "_bvSTTransactionsFull".ActualQuantity >= 0 
           then "_bvSTTransactionsFull".tax_amount
           else - "_bvSTTransactionsFull".tax_amount
           end)                                      AS 'TOTALTax_Amount', 
       SUM("_bvSTTransactionsFull".VALUE)            AS 'TOTALValue', 
       SUM("_bvSTTransactionsFull".actualvalue)      AS 'TOTALActualValue', 
       SUM("_bvSTTransactionsFull".actualsalesvalue) AS 'TOTALActualSalesValue', 
       SUM("_bvSTTransactionsFull".profit)           AS 'TOTALProfit' 
FROM   sqlschema.dbo."_bvSTTransactionsFull" "_bvSTTransactionsFull" 
WHERE  ( "_bvSTTransactionsFull".txdate >=? 
         AND "_bvSTTransactionsFull".txdate <=? ) 
GROUP  BY "_bvSTTransactionsFull".txdate, 
          "_bvSTTransactionsFull".description 
HAVING ( "_bvSTTransactionsFull".description LIKE 'POS Sale' ) 
        OR ( "_bvSTTransactionsFull".description LIKE 'POS Return' ) 
ORDER  BY "_bvSTTransactionsFull".txdate

If ActualQuantity might be zero but taxAmount in the same row is zero too you can use sign function to change sign of tax_amount:

sign(ActualQuantity) * tax_amount

UPDATE:

So I gather that MS Query has problems using parameters in a query that cannot be displayed graphically. Workaround is to replace parameters with some constants, save workbook as XML and change constants to "?". There is a link showing VBA code which does replacement on-site, but you will have to figure out how it works as I don't know VBA that much.

UPDATE 2:

On the other hand the easiest way out is to create view in your database.

Thanks Nikola, but not working in Excel 2007 query. Standard error — Brian Ellse, Mar 29 '12 at 13:55
I' ve tried case ... end statement in MS Query. It works. How do you execute this query? And what is the error? — Nikola Markovinović, Mar 29 '12 at 14:08
Nikola, I open the SQL dialog in the MS Query and replace the Sum statement for the TAX_Amount as: Sum("_bvSTTransactionsFull".Credit) AS 'TOTALCredit', Sum(case when "_bvSTTransactionsFull".ActualQuantity >= 0 then "_bvSTTransactionsFull".tax_amount else -"_bvSTTransactionsFull".tax_amount end) AS TOTALTax_Amount', The error is " Parameters are not allowed in queries that cannot be displayed graphically" — Brian Ellse, Mar 29 '12 at 14:28
Thanks Nikola for your efforts, I will keep searching. I only have read access to the database, it being our accounting system, purchased off-the-shelf for a huge US$ fee :-( — Brian Ellse, Mar 29 '12 at 15:16
If you have access to server you might create another database there and put the views there. If not, [there is option to use linked servers](http://msdn.microsoft.com/en-us/library/ms188279.aspx). — Nikola Markovinović, Mar 29 '12 at 19:22

Case, Select conditional requirments

1 Answers1