Know how many occurences something has happened over a particular period of time

Transact-SQL
1

I have a transactional table with data that contains the columns:

quantity
item
referenceid
appenddate

This table is used for a transactional log of our inventory in a warehouse. At any given point, you can know how much inventory you have onhand by running:

SELECT item, sum(quantity)
FROM table
GROUP BY item

What I would like to do (and I obviously have no idea how to do it) is to use this same data set to answer the question: Within the last 90 days, how many of those days did I have a positive inventory balance. Obviously if I snapshot each day, then I can build this data set, but I'm wondering if someone has had any experience with doing this programmatically.

I can write the results to a table that gets updated based on the data too if that is helpful.

Any suggestions would be appreciated.

Thanks in advance.

2

Which version of SQL Server: 2005, 2008, 2012+?

3

I am on 2008.

4

I wish you had posted DDL, which is minimal Netiquette :frowning:

This is a basic history table skeleton. See if you helps. Remember 85-95% of the work in SQL is in the DDL.

CREATE TABLE PriceHistory
(ean CHAR(13) NOT NULL -- industry standard barcode
REFERENCES Inventory(ean),
price_prev_date DATE NOT NULL,
price_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
price_end_date DATE, -- NULL means current price
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.
-- now we add constraints

--uniqueness
PRIMARY KEY (ean, price_start_date),
UNIQUE (ean, price_end_date),
UNIQUE (ean, prev_end_date),

--ordering of events
CHECK (price_start_date <= price_end_date),
-- Optionally, CHECK (price_start_date < price_end_date
CHECK (prev_end_date <= price_start_date),
-- Optionally, CHECK (price_start_date = price_prev_date + INTERVAL ‘1’ DAY), -- prevents gaps

-- self-referencing constraint
CONSTRAINT ean_prev_end_date
FOREIGN KEY (ean, prev_end_date)
REFERENCES PriceHistory (ean, price_end_date)
);

The first three uniqueness constraints are fairly obvious. The EAN and the start of a price have to be unique and not NULL-able so we have a natural primary key. The ending dates, current and previous, might have NULLs, so we need to use a UNIQUE constraint.

The next constraints give an ordering to each event, namely, price_prev_date is on or before price_start_date, which is on or before price_end_date. The reason for not putting this into a single BETWEEN predicate is that each constraint will have a name in production code that will show up in error messages so we want to be exact.

The self-referencing constraint is a trick from Alex Kuznetsov. It says that all the previous price ending dates were really ending dates for some time period. You will want to play with options to get them to fit your own business rules.

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE price_end_date IS NULL
OR price_end_date >= CURRENT_TIMESTAMP;

You use a BETWEEN predicate to get the appropriate price for a given order on a particular date.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.price_start_date
AND COALESCE (price_end_date, CURRENT_TIMESTAMP); -- or other known data as needed

5

In SQL 2008, LAG is not available, nor other windowing options.

In this case, probably easiest is to load a temp table with each day's results, then use a final query to get rolling totals for each day:

DECLARE @starting_date datetime
-- can adjust to start at any date
SET @starting_date = DATEADD(DAY, -90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
--SET @starting_date = '20151107'

IF OBJECT_ID('tempdb.dbo.#daily_totals') IS NOT NULL
    DROP TABLE #daily_totals
CREATE TABLE #daily_totals (
    item varchar(30),
    appenddate datetime,
    quantity int    
    )
CREATE CLUSTERED INDEX daily_totals__CL ON #daily_totals ( item, appenddate ) WITH ( FILLFACTOR = 100 );

INSERT INTO #daily_totals ( item, appenddate, quantity )
SELECT 
    item, 
    CASE WHEN appenddate <= @starting_date THEN @starting_date ELSE DATEADD(DAY, DATEDIFF(DAY, 0, appenddate), 0) END AS appenddate,
    sum(quantity) AS quantity
FROM table_name
GROUP BY item, CASE WHEN appenddate <= @starting_date THEN @starting_date ELSE DATEADD(DAY, DATEDIFF(DAY, 0, appenddate), 0) END
ORDER BY item, appenddate

--Uncomment the outer SELECT when ready to limit results to a positive inventory balance
--SELECT *
--FROM (
    SELECT
        item, 
        appenddate,
        (SELECT SUM(quantity) FROM #daily_totals dt2 WHERE dt2.item = dt.item AND dt2.appenddate <= dt.appenddate) AS date_net_quantity
    FROM #daily_totals dt
--) AS derived
--WHERE date_net_quantity > 0
--ORDER BY item, appenddate