Hello, I must apologize for not coming up with a more descriptive subject line.
I am struggling with the following query and I am not even sure whether what I want to achieve is possible at all:
The problem in real-world terms: The DB stores TRANSAKTIONS - which are either sales or refunds: each TRANSAKTION has n ITEMS related to it, which contain their RETAIL_PRICE and DISCOUNT. At the end of day, a total is run up, which should show the sum of refunds, sales and discounts.
Tables:
TRANSAKTION
-----------
KIND ('R' or 'S' for refund or sale)
TRANSAKTION_PK
PAYMENT_METHOD (cheque, cash, CC)ITEM ---- TRANSAKTION_FK ITEM_PK RETAIL_PRICE DISCOUNT
Desired result set:
PAYMENT_METHOD | category | SUBTOTAL ------------------------------------ Cash | sales | 103,55 Cash | discounts| -0,53 Cash | refunds | -20,99 CC | sales | 203,55 CC | discounts| -5,53 CC | refunds | -25,99
where sales amount is the sum of RETAIL_PRICE discount amount is the sum of DISCOUNT refunds is the sum of (RETAIL_PRICE-DISCOUNT)
I've had a stab at it but my sales amount is short of the RETAIL_PRICEs of all discounted ITEMs:
select PAYMENT_METHOD, case when KIND='R' then 'R' when KIND='S' and DISCOUNT is not null then 'D' when KIND='S' and DISCOUNT is null then 'S' end as CATEGORY,
sum(case when KIND=2 then -(RETAIL_PRICE-coalesce(DISCOUNT,0)) when KIND=1 and DISCOUNT is not null then -DISCOUNT when KIND=1 and DISCOUNT is null then RETAIL_PRICE end) as SUBTOTAL,
from ITEM inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK
where ...
group by PAYMENT_METHOD,CATEGORY order by PAYMENT_METHOD,CATEGORY
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
