Re: Help with SQL-statement !

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 22 Mar 2005 18:57:01 +0100
Message-ID: <42405c1b$0$32198$626a14ce@news.free.fr>

"Ole Hansen" <ohahaahr_at_hotmail.com> a écrit dans le message de news:3d05b5c1.0503140844.6c287453_at_posting.google.com...
| Hi everybody !
|
| I am an SQL-Newbie, who could need some help.
|
| I have a table, "Stocktrans", with the following fields:
|
| DATE QTY
| ---------- ------
| 01-01-2000 20
| 01-01-2000 4
| 01-01-2000 -5
| 01-01-2000 -2
| 10-02-2000 -3
| 20-03-2000 3
| 20-03-2000 -1
| 20-03-2000 -4
|
| QTY>0 means inflow to my stock, and QTY<0 means OUTFLOW of my stock. I
| now want to make a script with 4 columns.
|
| Column 1: Date
| Column 2: Sum of stock INFLOW on that date
| Column 3: Sum of stock OUTFLOW on that date
| Column 4: Stocklevel at the end of the day.
|
|
| The output should look like this:
|
| DATE INFLOW OUTFLOW STOCKLEVEL
| ---------- ------ ------- ----------
| 01-01-2000 24 7 17
| 10-02-2000 3 14
| 20-03-2000 3 5 12
|
| Hopefully someone can help me.
|
| Regards,
| Ole

SQL> create table test (cday date, qty number(10));
SQL> insert into test values(to_date('01-01-2000','DD-MM-YYYY'),20);
SQL> insert into test values(to_date('01-01-2000','DD-MM-YYYY'),4);
SQL> insert into test values(to_date('01-01-2000','DD-MM-YYYY'),-5);
SQL> insert into test values(to_date('01-01-2000','DD-MM-YYYY'),-2);
SQL> insert into test values(to_date('10-02-2000','DD-MM-YYYY'),-3);
SQL> insert into test values(to_date('20-03-2000','DD-MM-YYYY'),3);
SQL> insert into test values(to_date('20-03-2000','DD-MM-YYYY'),-1);
SQL> insert into test values(to_date('20-03-2000','DD-MM-YYYY'),-4);
SQL> commit;
SQL> select to_char(cday,'DD-MM-YYYY') day, qty

  2 from test
  3 order by trunc(cday), qty
  4 /
DAY QTY

---------- ----------
01-01-2000         -5
01-01-2000         -2
01-01-2000          4
01-01-2000         20
10-02-2000         -3
20-03-2000         -4
20-03-2000         -1
20-03-2000          3

8 rows selected.

SQL> select to_char(cday,'DD-MM-YYYY') day,

  2         sum(decode(sign(qty),-1,0,qty)) inflow,
  3         sum(decode(sign(qty),-1,-1*qty,0)) outflow,
  4         stock

5 from (select cday, qty, sum(qty) over (order by cday) stock from test) 6 group by to_char(cday,'DD-MM-YYYY'), stock 7 order by 1
8 /
DAY INFLOW OUTFLOW STOCK

---------- ---------- ---------- ----------
01-01-2000         24          7         17
10-02-2000          0          3         14
20-03-2000          3          5         12

3 rows selected.

Regards
Michel Cadot Received on Tue Mar 22 2005 - 11:57:01 CST