Running Average Query ? A Challenge

10 Sep 2006 7:38 PM

Luqman

I have an Inventory Application where Receipt and Issued of Quantity are
entered, while Only Receipt Value is entered, how can I show the Average
Value of Issued Item.

Say my Sample Data is :

CREATE TABLE test (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
SNO int IDENTITY PRIMARY KEY,
CHECK (PRAMT=QTY*RATE)
)

INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
INSERT INTO test VALUES ('1111-034',218,1559,339862)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
INSERT INTO test VALUES ('1111-034',200,1600,320000)
INSERT INTO test VALUES ('1111-034',218,1550,337900)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
GO

My output should be :

          Transactions                             Balance
                Qty       Rate      Amount           Qty          Rate
Amount
      200 1494.5 298900 200 1494.5 298900
      218 1559 339862 418 1528.139 638762
      -150   268 1528.139 409541.2
      -5   263 1528.139 401900.5
      -200   63 1528.139 96272.74
      -5   58 1528.139 88632.05
      -50   8 1528.139 12225.11
      200 1600 320000 208 1597.236 332225.1
      218 1550 337900 426 1573.064 670125.1
      -150   276 1573.064 434165.6
      -5   271 1573.064 426300.2

The negative quantity shows Issued.

I have created the above balance report in Excel, but I want to do it with
the query, any idea please ?

I can calculate the Balance quantity with running sum method but I can't
calculate the Average Rate of Issued Quantity.

Best Regards,

Luqman

10 Sep 2006 11:09 PM

Erland Sommarskog

Luqman (pearls***@cyber.net.pk) writes:
Show quote

> I have an Inventory Application where Receipt and Issued of Quantity are
> entered, while Only Receipt Value is entered, how can I show the Average
> Value of Issued Item.
>...
> My output should be :
>
>           Transactions                             Balance
>                 Qty       Rate      Amount           Qty          Rate
> Amount
>       200 1494.5 298900 200 1494.5 298900
>       218 1559 339862 418 1528.139 638762
>       -150   268 1528.139 409541.2
>       -5   263 1528.139 401900.5
>       -200   63 1528.139 96272.74
>       -5   58 1528.139 88632.05
>       -50   8 1528.139 12225.11
>       200 1600 320000 208 1597.236 332225.1
>       218 1550 337900 426 1573.064 670125.1
>       -150   276 1573.064 434165.6
>       -5   271 1573.064 426300.2
>
>
> The negative quantity shows Issued.

The query below does not give exactly the output you are asking for. There
is a deviation in the average rate from the eighth row where you add
another 200 to the inventory. I suspect that there is something in
the business rules that I don't understand. But maybe you can use the
query as a starting point? Else, please more details on how to compute
the average rate.

Oh, one more thing, the performance on large datasets will be horrible.
Unfortunately, not even SQL 2005 has support for running aggregations.
But if you are on SQL 2005, there may be a chance to some improvments.
This query assumes SQL 2000 only:

SELECT QTY, PRAMT, RATE, accQTY, avgRate, accQTY * avgRate
FROM   (SELECT a.SNO, a.QTY, a.PRAMT, a.RATE,
            accQTY = (SELECT SUM(b.QTY) FROM test b WHERE b.SNO <= a.SNO),
            avgRate = (SELECT SUM(CASE WHEN b.QTY > 0
                              THEN b.QTY * b.RATE
                       END) /
                       SUM(CASE WHEN b.QTY > 0 THEN b.QTY END)
        FROM   test b
        WHERE b.SNO <= a.SNO)
        FROM   test a) AS e
ORDER BY SNO

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

11 Sep 2006 12:27 AM

Steve Kass

Luqman,

Here's a cursor-based solution that yields the results you want.
It may be faster than a single query, because, as Erland
notes, SQL Server does not provide good support for
running aggregates. Note that I coded this to handle PRITC
values the way I think you might want, but I did not
test it on any data with more than one PRITC value.

Thanks for providing the details of your tables and your desired
solution!

-- Steve Kass
-- Drew University
-- http://www.stevekass.com

DECLARE @results TABLE (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
accQTY numeric(10,3) NOT NULL,
accRATE numeric(18,4) NOT NULL,
accAMT numeric(18,4) NOT NULL,
SNO int IDENTITY(1,1) PRIMARY KEY
)

DECLARE C CURSOR FOR
SELECT PRITC, QTY, RATE, PRAMT
FROM test
ORDER BY SNO

DECLARE
@PRITC varchar(10),
@QTY numeric(10,3),
@RATE numeric(18,4),
@PRAMT numeric(18,4),
@accQTY numeric(10,3),
@accRATE numeric(18,4),
@accAMT numeric(18,4)

SET @accQTY = 0
SET @accAMT = 0

OPEN C
FETCH NEXT FROM C INTO
@PRITC, @QTY, @RATE, @PRAMT

WHILE @@FETCH_STATUS = 0 BEGIN
SET @accQTY = @accQTY + @QTY
IF @QTY > 0
    SET @accAMT = @accAMT + @PRAMT
ELSE
    SET @accAMT = @accAMT + @QTY*@accRATE
SET @accRATE = @accAMT / @accQTY
INSERT INTO @results
SELECT @PRITC, @QTY, @RATE, @PRAMT, @accQTY, @accRATE, @accAMT
FETCH NEXT FROM C INTO
    @PRITC, @QTY, @RATE, @PRAMT
IF @PRITC <> (
    SELECT TOP 1 PRITC
    FROM @results
    ORDER BY SNO DESC
) BEGIN
      SET @accQTY = 0
      SET @accAMT = 0
    END
END
SELECT
PRITC, QTY, RATE, PRAMT, accQTY, accRATE, accAMT
FROM @results
ORDER BY SNO

CLOSE C
DEALLOCATE C
GO

Luqman wrote:

Show quote

>I have an Inventory Application where Receipt and Issued of Quantity are
>entered, while Only Receipt Value is entered, how can I show the Average
>Value of Issued Item.
>
>Say my Sample Data is :
>
>CREATE TABLE test (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> SNO int IDENTITY PRIMARY KEY,
> CHECK (PRAMT=QTY*RATE)
> )
>
>
> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
> INSERT INTO test VALUES ('1111-034',218,1559,339862)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
> INSERT INTO test VALUES ('1111-034',200,1600,320000)
> INSERT INTO test VALUES ('1111-034',218,1550,337900)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
>GO
>
>
>My output should be :
>
>          Transactions                             Balance
>                Qty       Rate      Amount           Qty          Rate
>Amount
>      200 1494.5 298900 200 1494.5 298900
>      218 1559 339862 418 1528.139 638762
>      -150   268 1528.139 409541.2
>      -5   263 1528.139 401900.5
>      -200   63 1528.139 96272.74
>      -5   58 1528.139 88632.05
>      -50   8 1528.139 12225.11
>      200 1600 320000 208 1597.236 332225.1
>      218 1550 337900 426 1573.064 670125.1
>      -150   276 1573.064 434165.6
>      -5   271 1573.064 426300.2
>
>
>The negative quantity shows Issued.
>
>I have created the above balance report in Excel, but I want to do it with
>the query, any idea please ?
>
>I can calculate the Balance quantity with running sum method but I can't
>calculate the Average Rate of Issued Quantity.
>
>Best Regards,
>
>Luqman
>
>
>
>
>

11 Sep 2006 12:41 AM

Steve Kass

One correction to the handling of PRITC (again, I'm
just guessing). You probably need to declare the cursor
as

DECLARE C CURSOR FOR
SELECT PRITC, QTY, RATE, PRAMT
FROM test
ORDER BY PRITC, SNO

to keep all the matching PRITC rows together.
You might also want to capture the actual SNO values
in an additional column of @results, so that you can use
it to order the final result set. (Don't put the actual SNO
values into @results.SNO, or you'll mess up the TOP 1
query that catches the changes in PRITC.)

SK

Steve Kass wrote:

Show quote

> Luqman,
>
> Here's a cursor-based solution that yields the results you want.
> It may be faster than a single query, because, as Erland
> notes, SQL Server does not provide good support for
> running aggregates. Note that I coded this to handle PRITC
> values the way I think you might want, but I did not
> test it on any data with more than one PRITC value.
>
> Thanks for providing the details of your tables and your desired
> solution!
>
> -- Steve Kass
> -- Drew University
> -- http://www.stevekass.com
>
> DECLARE @results TABLE (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> accQTY numeric(10,3) NOT NULL,
> accRATE numeric(18,4) NOT NULL,
> accAMT numeric(18,4) NOT NULL,
> SNO int IDENTITY(1,1) PRIMARY KEY
> )
>
> DECLARE C CURSOR FOR
> SELECT PRITC, QTY, RATE, PRAMT
> FROM test
> ORDER BY SNO
>
> DECLARE
> @PRITC varchar(10),
> @QTY numeric(10,3),
> @RATE numeric(18,4),
> @PRAMT numeric(18,4),
> @accQTY numeric(10,3),
> @accRATE numeric(18,4),
> @accAMT numeric(18,4)
>
> SET @accQTY = 0
> SET @accAMT = 0
>
> OPEN C
> FETCH NEXT FROM C INTO
> @PRITC, @QTY, @RATE, @PRAMT
>
> WHILE @@FETCH_STATUS = 0 BEGIN
> SET @accQTY = @accQTY + @QTY
> IF @QTY > 0
>    SET @accAMT = @accAMT + @PRAMT
> ELSE
>    SET @accAMT = @accAMT + @QTY*@accRATE
> SET @accRATE = @accAMT / @accQTY
> INSERT INTO @results
> SELECT @PRITC, @QTY, @RATE, @PRAMT, @accQTY, @accRATE, @accAMT
> FETCH NEXT FROM C INTO
>    @PRITC, @QTY, @RATE, @PRAMT
> IF @PRITC <> (
>    SELECT TOP 1 PRITC
>    FROM @results
>    ORDER BY SNO DESC
> ) BEGIN
>      SET @accQTY = 0
>      SET @accAMT = 0
>    END
> END
> SELECT
> PRITC, QTY, RATE, PRAMT, accQTY, accRATE, accAMT
> FROM @results
> ORDER BY SNO
>
> CLOSE C
> DEALLOCATE C
> GO
>
>
>
> Luqman wrote:
>
>> I have an Inventory Application where Receipt and Issued of Quantity are
>> entered, while Only Receipt Value is entered, how can I show the Average
>> Value of Issued Item.
>>
>> Say my Sample Data is :
>>
>> CREATE TABLE test (
>> PRITC varchar(10) NOT NULL,
>> QTY numeric(10,3) NOT NULL,
>> RATE numeric(18,4) NULL,
>> PRAMT numeric(18,4) NULL,
>> SNO int IDENTITY PRIMARY KEY,
>> CHECK (PRAMT=QTY*RATE)
>> )
>>
>>
>> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
>> INSERT INTO test VALUES ('1111-034',218,1559,339862)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
>> INSERT INTO test VALUES ('1111-034',200,1600,320000)
>> INSERT INTO test VALUES ('1111-034',218,1550,337900)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
>> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
>> GO
>>
>>
>> My output should be :
>>
>>          Transactions                             Balance
>>                Qty       Rate      Amount           Qty          Rate
>> Amount
>>      200 1494.5 298900 200 1494.5 298900
>>      218 1559 339862 418 1528.139 638762
>>      -150   268 1528.139 409541.2
>>      -5   263 1528.139 401900.5
>>      -200   63 1528.139 96272.74
>>      -5   58 1528.139 88632.05
>>      -50   8 1528.139 12225.11
>>      200 1600 320000 208 1597.236 332225.1
>>      218 1550 337900 426 1573.064 670125.1
>>      -150   276 1573.064 434165.6
>>      -5   271 1573.064 426300.2
>>
>>
>> The negative quantity shows Issued.
>>
>> I have created the above balance report in Excel, but I want to do it
>> with
>> the query, any idea please ?
>>
>> I can calculate the Balance quantity with running sum method but I can't
>> calculate the Average Rate of Issued Quantity.
>>
>> Best Regards,
>>
>> Luqman
>>
>>
>>
>>
>>