Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 6 of 6 total |
Brain Twister- How to get this information |
Fri, Dec 15 2006 1:53 AM | Permanent 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 AM | Permanent 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 AM | Permanent 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 PM | Permanent 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 Thanks for your help! Cheers Adam. |
Mon, Dec 18 2006 9:28 AM | Permanent 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 > > Thanks for your help! > > Cheers > > Adam. > > |
Mon, Dec 18 2006 10:36 AM | Permanent 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/ |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |