Re: Help with SQL-statement !

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Tue, 22 Mar 2005 13:07:53 -0800
Message-ID: <1111525465.85299@yasure>

c w wrote:

> "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 

When questions look like homework, or worse finals, please be sure to provide hints ... not solutions. It makes our job as instructors far more difficult.

Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Tue Mar 22 2005 - 15:07:53 CST