Re: SQL selective qty difference

Anil G wrote:
> Hi,
>
> I have following data structure into table
>
> Name Type Qty
> -------- ------- --------
> N1 D 3 --------|
> N1 F 2 | ----> Difference (D - I) DIDFF
> ------------|
> N1 I 4 --------|
> |----> (S - DIDIFF) as SDIFF
> N1 S 10
> ----------------------------------------------------------------|
> N2 D 2
> ...
> ...
> Any clues about performing above type operations using SQL query rather
> than writing PL/SQL???
>
>
> Thanks in advance,
>
> Anil G

I tried to understand what you are attempting to do here. Best guess: for the N1 Name entries, take the value of the row with "D" as the type, subtract from that the value of the row with "I" as the type, and assign this to a column named DIDFF. Take the value of the row with "S" as the type, subtract from it the result of the previous calculation, and assign this to a column named SDIFF.

If my guess is correct, a SQL statement similar to the following should work:
SELECT
  NAME,
  NVL(MAX(DECODE(TYPE,'D',QTY,0)),0)-NVL(MAX(DECODE(TYPE,'I',QTY,0)),0) DIDFF, NVL(MAX(DECODE(TYPE,'S',QTY,0)),0)-(NVL(MAX(DECODE(TYPE,'D',QTY,0)),0)-NVL(MAX(DECODE(TYPE,'I',QTY,0)),0)) SDIFF
FROM
  MY_TABLE
GROUP BY
  NAME; Basic idea of what is happening in the above: for each change in the NAME column, find the largest QTY where the TYPE column is 'D' (assume that there is only one matching row), then repeat this syntax for the remaining TYPE values of interest and add/subtract the values as needed. This will collapse the multiple rows for each change in the NAME column to a single row, which is required for the calculations.

SQL code to generate the test environment: CREATE TABLE MY_TABLE (
  NAME VARCHAR2(10),
  TYPE CHAR(1),
  QTY NUMBER(10)); INSERT INTO MY_TABLE VALUES (
  'N1',
  'D',
  3);

INSERT INTO MY_TABLE VALUES (
  'N1',
  'F',
  2);

INSERT INTO MY_TABLE VALUES (
  'N1',
  'I',
  4);

INSERT INTO MY_TABLE VALUES (
  'N1',
  'S',
  10);

INSERT INTO MY_TABLE VALUES (
  'N2',
  'D',
  2);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Sep 05 2006 - 19:59:45 CDT