Conditional Select Statement

[getjbb]

Hello,

YR PROD NO BAL TYPE BAL AMOUNT
2005 42015 1 53665201
2005 42015 19 1060606.25
2005 42015 15 8096017.47
2005 42015 23 53665201
2005 42016 1 164342737
2005 42016 15 19758687.9
2005 42016 23 164340488
2005 42016 19 3146217.26

I need to create the following resultset from the above data and I am not quite sure how to do it:

The Select should be as follow:

select YR,
PROD NO,
(Subtract BAL AMOUNT of BAL TYPE 1 from BAL AMOUNT
of BAL TYPE 23),
((Add BAL AMOUNT of BAL TYPE 15 to BAL AMOUNT of
BAL TYPE 19) Subtract (BAL AMOUNT of BAL TYPE 1))
from PRODUCT

The resultset needs to be sorted by YR and PROD NO.
The resultset needs to be group by PROD NO and YR

2005 is the only YR I will be looking at.

Could someone please help.

svc

 

[getjbb]

I need to make a correction:

The YR can be different for a PROD NUM:

YR PROD NO BAL TYPE BAL AMOUNT
2004 42015 1 53665201
2004 42015 19 1060606.25
2004 42015 15 8096017.47
2004 42015 23 53665201
2005 42016 1 164342737
2005 42016 15 19758687.9
2005 42016 23 164340488
2005 42016 19 3146217.26

 

[getjbb]

Hello,

I need to revise my first email. The revision is in red:

YR PROD NO BAL TYPE BAL AMOUNT
2005 42015 1 53665201
2005 42015 19 1060606.25
2005 42015 15 8096017.47
2005 42015 23 53665201
2005 42016 1 164342737
2005 42016 15 19758687.9
2005 42016 23 164340488
2005 42016 19 3146217.26

I need to create the following resultset from the above data and I am not quite sure how to do it:

The Select should be as follow:

select YR,
PROD NO,
(Subtract BAL AMOUNT of BAL TYPE 1 from BAL AMOUNT
of BAL TYPE 23),
((Add BAL AMOUNT of BAL TYPE 15 to BAL AMOUNT of
BAL TYPE 19) Subtract (BAL AMOUNT of BAL TYPE 1))
from PRODUCT

The select is done for each YR, PROD NO combination. For example for, for the first field,YR 2005 BAL TYPE 1 should be subtracted from YR 2005 BAL TYPE 23. And for the second field, YR 2005 BAL TYPE 1 should be subtracted from the sum of YR 2005 BAL TYPE9(s) 15 and 19.

The resultset needs to be sorted by YR and PROD NO.
The resultset needs to be group by PROD NO and YR

Could someone please help.

 

[ddiamond]

SELECT 
  YR,
  ProdNO,
  SUM(CASE BalType
    WHEN 1  THEN -1*BalAmount 
    WHEN 23 THEN BalAmount
    ELSE 0 END),
  SUM(CASE BalType
    WHEN 1  THEN -1*BalAmount 
    WHEN 15 THEN BalAmount
    WHEN 19 THEN BalAmount
    ELSE 0 END)
FROM PRODUCT 
GROUP BY YR, ProdNO
ORDER BY YR, ProdNO

 

[ddiamond]

opps. Ignore the above code. It is DB2 syntax, not Oracle. I work with both, so sometimes I forget which one I'm working on. You need to use a decode statement instead of a case statement.

 

[ddiamond]

Here is the oracle version:

SELECT 
  YR,
  ProdNO,
  SUM(DECODE(BalType,
    1, -1*BalAmount, 
    23, BalAmount
    0)),
  SUM(DECODE(BalType,
    1, -1*BalAmount 
    15, BalAmount
    19, BalAmount
    0))
FROM PRODUCT 
GROUP BY YR, ProdNO
ORDER BY YR, ProdNO

 

[getjbb]

ddiamond,

Thank you. It worked perfectly

getjbb

 

[SantaMufasa]

Getjbb, Remember, our currency here on Tek-Tips is Purple Stars...If you like what you see, and it "works perfectly," then "Thank ddiamond for this valuable post!"

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.