Re: Help with SQL-statement !

From: c w <c.wyke_at_sympatico.ca>
Date: Tue, 22 Mar 2005 12:14:38 -0500
Message-ID: <1oY%d.6457$JK1.389898@news20.bellglobal.com>

"Ole Hansen" <ohahaahr_at_hotmail.com> wrote in message 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

Try this it might help haven't tested it, I am a newbie also.

select date, sum(s1.qty) INFLOW, sum(s2.qty) OUTFLOW, (sum(s1.qty)) - sum(s2.qty)) STOCKLEVEL
from(select date, qty, 0

        from Stocktrans s1
        where qty > 0
        union
        select date, 0,qty
        from Stocktrans s2
        where qty < 0)

group by date Received on Tue Mar 22 2005 - 11:14:38 CST