TSQL Query Calculating number of days and number of occurrences

Question

I'm having some issues writing a query summarizing some stocking information from a query.

I've been trying a few CTE (Common table Expressions ) grouping and subqueries and LAG function to try to summarize the data. Oddly I've gotten stuck on this problem the last few days.

Here is example of the data that I'm dealing with.

--Optional create table
--Drop table testdata
--Create table testdata ( Part int, StockDate date, OutOfStock bit);

with inventorydata(Part, StockDate, OutOfStock) as
(
  select 1000, '1/1/2019',1
  union 
  select 1000,'1/2/2019',1
  union 
  select 1000, '1/3/2019',1
  union
  select 1000, '1/4/2019',0
  union
  select 1000, '1/5/2019',1
  union 
  select 1005, '1/1/2019',0
  union 
  select 1005,'1/2/2019',1
  union 
  select 1005, '1/3/2019',1
  union
  select 1005, '1/4/2019',1
  union
  select 1005, '1/5/2019',0
)

--Insert into testdata ( Part,StockDate,OutOfStock)
Select Part,StockDate,OutOfStock from inventorydata
--Select * from testdata

Output

Part        StockDate OutOfStock
----------- --------- -----------
1000        1/1/2019  1
1000        1/2/2019  1
1000        1/3/2019  1
1000        1/4/2019  0
1000        1/5/2019  1
1005        1/1/2019  0
1005        1/2/2019  1
1005        1/3/2019  1
1005        1/4/2019  1
1005        1/5/2019  0

I'm trying get the desired output.

Part        StockDate BackInStock Occurance
----------- --------- ----------- -----------
1000        1/1/2019  1/3/2019    1
1000        1/5/2019  1/5/2019    1
1005        1/2/2019  1/4/2019    1

Any help is much appreciated. Thank you.

Can you explain the process by which you get from input to (desired) output? Having us guess isn't very efficient. — HABO, Oct 29 '19 at 21:19

score 0 · Accepted Answer · answered Oct 30 '19 at 00:03

Without more detail, it's hard to be sure of exactly what you are going for. Your desired output is missing some detail that would be helpful in giving a better answer. With that said, hopefully this will help you out.

My assumptions:

You are looking to find the periods where parts are out of stock, and note the date they first were out of stock and the date they came back into stock
I'm interpreting "back in stock" date to be the date that an item first had a record noting it was not out of stock. For example, part 1000 went out of stock on 1/1/2019 and would be back in stock starting 1/4/2019, per the data, rather than 1/3/2019 as in the example
If the item is not back in stock, then we don't want a "back in stock" date

I'm going to put your example data into a temp table to make life a little easier for demonstrating:

with inventorydata(Part, StockDate, OutOfStock) as
(
  select 1000, '1/1/2019',1
  union 
  select 1000,'1/2/2019',1
  union 
  select 1000, '1/3/2019',1
  union
  select 1000, '1/4/2019',0
  union
  select 1000, '1/5/2019',1
  union 
  select 1005, '1/1/2019',0
  union 
  select 1005,'1/2/2019',1
  union 
  select 1005, '1/3/2019',1
  union
  select 1005, '1/4/2019',1
  union
  select 1005, '1/5/2019',0
)

Select Part,StockDate,OutOfStock 
INTO #Test
from inventorydata

Consider the following:

SELECT 
    Part,
    StockDate,
    BackInStockDate
FROM
(
    SELECT 
        Part,
        StockDate,
        OutOfStock,
        LEAD(StockDate, 1) OVER (PARTITION BY Part ORDER BY StockDate) AS BackInStockDate
    FROM
    (
        SELECT 
            Part,
            StockDate,
            OutOfStock,
            LAG(OutOfStock, 1) OVER (PARTITION BY Part ORDER BY StockDate) AS PrevOutOfStock
        FROM #Test
    ) AS InnerData
    WHERE 
        OutOfStock <> PrevOutOfStock
        OR PrevOutOfStock IS NULL
) AS OuterData
WHERE
    OutOfStock = 1

The InnerDataquery pulls each record along with the OutOfStock value for the previous row. We use that to find only those rows that represent a date where the status of the stock changed from in-stock to out-of-stock or visa-versa. This is determine by OutOfStock <> PrevOutOfStock and PrevOutOfStock IS NULL.

Once we have just the rows that represent changes we look at the next row to get the date we saw the state of the part change from the state represented in the current row.'

Finally, we filter out rows where OutOfStock = 0, as those would represent in-stock periods, which we are ignoring. This gives us the following result:

Part    StockDate   BackInStockDate
------- ----------- ----------------
1000    1/1/2019    1/4/2019
1000    1/5/2019    NULL
1005    1/2/2019    1/5/2019

You can modify the structure to get a different BackInStockDate if this isn't the value you are wanting, and add some counting to get whatever Occurances is supposed to be.

Thank you so much, correct the occurrences is what they suppose to be, I was going to use that with another subquery. Also I'm using the wrong term BackInStock, that should be the last day of the Stock out. I've adjusted for that, and now I can use this in a another sub query to build the report. Thank you for you help, Next time I'll remember what the Lead command does. — user12433, Oct 30 '19 at 13:52

TSQL Query Calculating number of days and number of occurrences

1 Answers1