Daniel Hudson February 2016

Filter Query with Conditional Sum without Grouping

Background: I need to modify this query to only output invoice numbers which don't balance to zero (could be +/-). I also need the output to include all items on invoice numbers which don't balance to zero (no grouping).

SO, if the invoice balances, suppress it from output.

Query:

SELECT     invoices.account, invoices.invoice_no, transact.item, transact.date_time, transact.operator, transact.salespoint, transact.extension
FROM         transact INNER JOIN
                  invoices ON transact.invoice_no = invoices.invoice_no
WHERE     (invoices.account = '*GUESTS*') AND (transact.extension <> 0))
ORDER BY invoices.invoice_no DESC

Output:

account invoice_no  item    date_time   operator    salespoint   extension 
Test    1   **TRANS**   1/0/00 12:25 AM SUNNY   RTL2     $(2.69)
Test    1   BT_DIET     1/0/00 12:25 AM SUNNY   RTL2     $2.69 
Test    2   **TRANS**   1/0/00 12:08 AM SUNNY   RTL2     $(14.55)
Test    2   **TRANS**   1/0/00 12:08 AM SUNNY   RTL2     $(1.00)
Test    2   QUICHE      1/0/00 12:08 AM SUNNY   RTL2     $7.01 
Test    2   FRUITSALAD  1/0/00 12:08 AM SUNNY   RTL2     $7.54 
Test    2   **TIPS**    1/0/00 12:08 AM SUNNY   RTL2     $1.00 
Test    3   **TRANS**   1/0/00 12:07 AM SUNNY   RTL2     $(40.67)
Test    3   BURRITO     1/0/00 12:07 AM SUNNY   RTL2     $16.17 
Test    3   ENGMUFFSAU  1/0/00 12:07 AM SUNNY   RTL2     $7.54 
Test    3   DANISH      1/0/00 12:07 AM SUNNY   RTL2     $4.30 
Test    3   SUMPLYJUIC  1/0/00 12:07 AM SUNNY   RTL2     $6.47 
Test    3   SUMPLYJUIC  1/0/00 12:07 AM SUNNY   RTL2     $3.23 
Test    3   COFFEE_CUP  1/0/00 12:07 AM SUNNY   RTL2     $2.96 
Test    4   QUICHE      1/0/00 12:01 AM SUNNY   RTL2     $7.01 
Test    4   DANISH      1/0/00 12:07 AM SUNNY   RTL2     $4.30 

Desired Output:

account invoice_no  item    date_time   operator    salespoint   extension 
Test    4   QUICHE      1/0/00 12:01 AM SU        

Answers


wrschneider February 2016

I would use window/analytic functions, if that's an option. Then you can approach this in two steps:

  • add a column like sum(extension) over (partition by invoice_no) as invoice_balance
  • wrap the whole query in another query to filter where invoice_balance <> 0


FLICKER February 2016

;WITH list AS (
    SELECT     invoices.account, invoices.invoice_no, transact.item, transact.date_time, transact.operator, transact.salespoint, transact.extension
    , SUM(transact.salespoint) OVER (PARTITION BY transact.item ORDER BY transact.item) AS Total
    FROM         transact INNER JOIN
              invoices ON transact.invoice_no = invoices.invoice_no
    WHERE     (invoices.account = '*GUESTS*') AND (transact.extension <> 0)
)
SELECT *
FROM list
WHERE Total > 0
ORDER BY invoice_no DESC


Lajos Arpad February 2016

The problem is that extension is not numeric. You need to write a stored function which converts correctly any value from extension to a numeric value. You also need group by, since the amount to 0 is an aggregated term. So, you need something like:

SELECT     account, invoice_no, item, date_time, operator, salespoint, extension
FROM         transact INNER JOIN
                  invoices ON transact.invoice_no = invoices.invoice_no
group by invoices.account, invoices.invoice_no, transact.item, transact_date_time, transact.operator, transact.salespoint, myfunction(transact.extension) as extension
having     (account = '*GUESTS*') AND (sum(extension) <> 0))
ORDER BY invoice_no DESC

Post Status

Asked in February 2016
Viewed 1,872 times
Voted 4
Answered 3 times

Search




Leave an answer