Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Brain Twister- How to get this information
Fri, Dec 15 2006 1:53 AMPermanent Link

"Adam H."
Hi,

I've just had a request from a user who's running on of my DBISam databases
for inventory.

The current picture: They currently warehouse items of other clients, and
track stock not only on a product basis, but a product basis per client.
Stock comes in, and out at a very frequent basis.

I track this information in a table called StockTrans. (Stock Transactions).
The table has a structure similar to the following:

CLIENT
PRODUCT
QTY                (Positive figure for inwards, negative for outloads)
DATETIME    (Stamp to show when transaction occurred).

I simply add a new entry for the client, insert the product and QTY
(negative figure for outloads), along with the date to keep track of stock
movements. To get a current stocktake report - I simply to a sum of the
total stock (which has positives for receivals, and negatives for outloads)
to show me the current figure. (To get a previous days stock balance, I
simply put a where clause in for the date/time field).

The crunch: They have asked for a report that shows them a list of client /
product combinations, and a date/time showing when clients last had a zero
balance of any particular stock items. (As stock may go out in the morning,
and more stock arrive in the afternoon - it needs to be taken to a date/time
stamp, and not just a date).

Does anyone know how (if possible) I could achieve a query to give me the
date/times of when the stock would have equaled zero from this list? I've
been pondering it over, but can't think of anything decent.

Thanks & Regards

Adam.

Fri, Dec 15 2006 8:46 AMPermanent Link

I'd be surprised if such a script was possible, but I presume you could
find out the current level, and then step back adding the values until you
get to zero and then the datetime of that record is the one that matters.

/Matthew Jones/
Fri, Dec 15 2006 10:18 AMPermanent Link

Sean McCall
Adam,

I would walk through the table in code and generate a second in-memory
"balance" table with your results. Off the cuff:

BalanceTable:

Client, Product, Qty, DateTime, Counter

{open StockTable, indexed on Client, Product, DateTime}
{build BalanceTable, indexed on Client, Product, Counter }
ALastClient := StockTable.Client; (.AsSomething)
ALastProduct := StockTable.Product;
ACounter := 1;
while not(StockTable.EOF) do begin
  if (ALastClient <> StockTable.Client) or
      (ALastProduct <> StockTable.Product) then begin   
    ALastClient := StockTable.Client; (.AsSomething)
    ALastProduct := StockTable.Product;
    Inc(ACounter);
  end; {if change in product or client}
  if BalanceTable.FindKey([
   ALastClient, ALastProduct, ACounter]) then  begin
    BalanceTable.Edit;
    BalanceTable.Qty.AsInteger := BalanceTable.Qty.AsInteger +        
StockTable.Qty.AsInteger;
    end {if exists}
   else begin
    BalanceTable.Append;
    BalanceTable.Qty.AsInteger := StockTable.Qty.AsInteger;
    BalanceTable.Counter.AsInteger := ACounter;
   end; {if new balance record}
   if BalanceTable.Qty.AsInteger = 0 then begin
     Inc(ACounter)
   end; {if balance hit zero}
   BalanceTable.DateTime.AsDateTime := StockTable.DateTime.AsDateTime;
   BalanceTable.Post;
   StockTable.Next;
 end; {while stock table records}
 SQL... (Delete From BalanceTable where Qty <> 0);


Assuming I didn't mess up my pseudo code (note I have no idea what type
to use for you client or product codes), this will give you a balance
table that has a unique record for every time the balance when to zero.
Datetime has the time it went to zero. Hope this is helpful. Sorry..
have no idea how you would do this in pure SQL, but parts of the above
could be done in SQL if you wanted.

Sean







Adam H. wrote:
> Hi,
>
> I've just had a request from a user who's running on of my DBISam databases
> for inventory.
>
> The current picture: They currently warehouse items of other clients, and
> track stock not only on a product basis, but a product basis per client.
> Stock comes in, and out at a very frequent basis.
>
> I track this information in a table called StockTrans. (Stock Transactions).
> The table has a structure similar to the following:
>
> CLIENT
> PRODUCT
> QTY                (Positive figure for inwards, negative for outloads)
> DATETIME    (Stamp to show when transaction occurred).
>
> I simply add a new entry for the client, insert the product and QTY
> (negative figure for outloads), along with the date to keep track of stock
> movements. To get a current stocktake report - I simply to a sum of the
> total stock (which has positives for receivals, and negatives for outloads)
> to show me the current figure. (To get a previous days stock balance, I
> simply put a where clause in for the date/time field).
>
> The crunch: They have asked for a report that shows them a list of client /
> product combinations, and a date/time showing when clients last had a zero
> balance of any particular stock items. (As stock may go out in the morning,
> and more stock arrive in the afternoon - it needs to be taken to a date/time
> stamp, and not just a date).
>
> Does anyone know how (if possible) I could achieve a query to give me the
> date/times of when the stock would have equaled zero from this list? I've
> been pondering it over, but can't think of anything decent.
>
> Thanks & Regards
>
> Adam.
>
>
Sun, Dec 17 2006 6:24 PMPermanent Link

"Adam H."
Hi Sean & Matt,

Thanks for your replies.

Yeah - pretty much what I thought - not going to be possible with SQL. Going
to be somethign that will take quite a considerable amount of cpu cycles I'd
say (with the number of transactions in the table, and the number of
'variations' or products and customers.)

However Sean, your post gave me a different idea to what I originally had
planned, and I believe that it could work (as long as the user's willing to
wait Smiley

Thanks for your help!

Cheers

Adam.

Mon, Dec 18 2006 9:28 AMPermanent Link

Sean McCall
Glad to help. BTW, if the user only cares about more recent drops to
zero, you can maintain a balance file when you post the inventory
changes so that you always have the current customer/product balance. To
generate the report rapidly, work backwards from this current balance.

Sean



Adam H. wrote:
> Hi Sean & Matt,
>
> Thanks for your replies.
>
> Yeah - pretty much what I thought - not going to be possible with SQL. Going
> to be somethign that will take quite a considerable amount of cpu cycles I'd
> say (with the number of transactions in the table, and the number of
> 'variations' or products and customers.)
>
> However Sean, your post gave me a different idea to what I originally had
> planned, and I believe that it could work (as long as the user's willing to
> wait Smiley
>
> Thanks for your help!
>
> Cheers
>
> Adam.
>
>
Mon, Dec 18 2006 10:36 AMPermanent Link

Actually, a running balance, if possible, allows an instant solution.
You'd just "SELECT ItemDate WHERE (LineBalance <= 0)". And if you only
want the last one, then TOP would work.

/Matthew Jones/
Image