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
