CODEWITHSUNDEEP
sqlsql-serverdatabasems-access
Matt
Matt

Reputation: 27

Access or T-SQL Query for Counting Values Returned Based on Running Totals

I'm working with an Inventory Ledger stored as a report/view in our database. When exported, it looks like this: 

Tran Type         Txn Date     Item     Quantity
Opening Balance   6/30/12      Item1     4
Opening Balance   6/30/12      Item2     7
Shipping          7/14/12      Item2    -1
Opening Balance   6/30/12      Item3     3
Shipping          7/2/12       Item3    -1
Opening Balance   6/30/12      Item4     5
Shipping          7/3/12       Item4    -1
Shipping          7/3/12       Item4    -1
Shipping          7/3/12       Item4    -1
Shipping          7/5/12       Item4    -1
Shipping          7/5/12       Item4    -1
Receiving         7/9/12       Item4    10

Before the export, it doesn't contain those "Opening Balance" entries, which are generated based on the export period.

We are trying to keep track of how many days each item has been out of stock within a given period. The first stage of this is to create running totals by item, which I had previously been doing through Pivot Tables in Excel. From there, I need to keep a flexible, dynamic count of the number of days each item is at 0 qty for stock (based on month, quarter, or year). Initially, I had hoped to factor this count through pivot tables as well, and I turned to superuser's help for that. Unfortunately, it seems it may be counter-intuitive to do this type of work through excel, so I'm bringing my issue here.

I have only novice experience in Access and SQL Server. I had some light formal training in Oracle and PL/SQL, but it was a number of years ago. Possibly adding another complication to this, I can't write or permanently save anything to our system backend (even though I have permissions for it).

So far, I've only figured out a T-SQL statement to create running totals by item:

Select 
 TxnDate,
    Quantity,
        Item,
    (SELECT Sum(Quantity)
    FROM DATABASE.dbo.InventoryLedger as D1
    WHERE D1.TxnDate <= D0.TxnDate AND D1.Item = D0.Item) as balance
    FROM DATABASE.dbo.InventoryLedger as D0
    where Item = 'Item4' and TxnDate >= 2012-07-01

I'm lost after this. I don't know whether I should do some kind of count, or find a way to subtract dates between an Item hitting 0 qty and then being received again later. I'm not sure how to add another aggregated or calculated column based on the already calculated balance column, and I'm not even sure how to translate this query so it will function with my Access file of the same data. I'd accept a solution in either Access or T-SQL format; I'm eager to actually learn T-SQL, while being able to perform the count in Access would let me link the database and make it easier for me to produce direct reports.

I'm also curious if there'd be an easy way to query stock levels for every item on every day? I suppose this would just be calculated using the same exact data, so it would probably just be a waste of time and space, right?

I appreciate any help, folks. I've been trying to come up with a solution to measure Out-of-Stock for months now, and this is the closest I've been. Thanks.

Upvotes: 1

Views: 916

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This is much easier with the right set of windows/analytic functions. But I have an idea, which might work.

First, you can get the cumulative sum by using a self-join.

Then, also do a self join on this to get the next non-zero cum, and use a group by to get the min date of the next record:

with tcum as (
     select t.txnDate, t.Item, t.quantity, sum(tprev.quantity) as cumq
     from t left outer join
          t tprev
          on t.item = tprev.item and
             t.txnDate >= tprev.txnDate
     group by t.txnDate, t.Item, t.quantity
    )
 select tcum.item, tcum.txndate, MIN(tcumnext.txndate) as nextfilldate
 from tcum left outer join
      tcum tcumnext
      on tcum.item = tcumnext.item and
         tcum.date < tcumnext.date and
         tcumnext.cumq > 0 and
         tcum.cumq = 0
group by tcum.item, tcum.txndate

Upvotes: 1

Related Questions