Populating missing date values

Question

So I have a table that shows inventory values by Item ID. I need to find the number of days, and which days, each item was out of stock. My table only adds a new row each time there is a change in the inventory value, so I only have the day that any given item went out of stock, but not any accompanying days it continued to be out of stock. Here's an example of what it looks like//

ItemID  QTY  ADate 
10007   2   2011-07-22
10007   1   2011-07-27
10007   2   2011-09-01
10007   1   2011-09-23 
10007   2   2011-09-29 
10007   0   2011-10-29
10007   2   2011-11-03
10007   1   2011-11-21
23028   2   2011-07-23 
23028   0   2011-08-03 
23028   2   2011-08-10 

What I'd like to have is:

ItemID  QTY  ADate
10007   2   2011-07-22 
10007   2   2011-07-23
10007   2   2011-07-24
10007   2   2011-07-25
10007   2   2011-07-26
10007   1   2011-07-27
10007   1   2011-07-28
10007   1   2011-07-29
10007   1   2011-07-30

etc... `

Then I could grab the days, by item ID, that I was out of stock. I don't have any code to show, because frankly I don't even know where to start. Any idea how to tackle this one? Thanks so much!

Answer 1

A R solution using tidyverse library, Using @Brian's data:

date_seq <- seq(from=min(d$ADate),
            to=max(d$ADate),
            by="day")

library(tidyverse)
d %>%
  right_join(expand.grid(ItemID = unique(.$ItemID),ADate=date_seq)) %>%
  arrange(ItemID) %>%
  group_by(ItemID) %>%
  fill(ItemID,QTY) 

# # A tibble: 40 x 3
# # Groups:   ItemID [2]
#    ItemID   QTY      ADate
#     <int> <int>     <dttm>
#  1  10007     2 2011-07-22
#  2  10007     2 2011-07-23
#  3  10007     2 2011-07-24
#  4  10007     2 2011-07-25
#  5  10007     2 2011-07-26
#  6  10007     1 2011-07-27
#  7  10007     1 2011-07-28
#  8  10007     1 2011-07-29
#  9  10007     1 2011-07-30
# 10  10007     1 2011-07-31
# # ... with 30 more rows

Answer 2

You can generate this date sequence using LEAD (requires sql server 2012+) and a CTE as below

WITH Orders AS 
( 
  SELECT ItemID,QTY,
      LEAD (cADate, 1, NULL) OVER (ORDER BY ItemID,cADate )  AS NExtVal,
      cADate AS  Number  
  FROM Table1
  -- WHERE QTY > 0 -- remove this  if you want 
  UNION ALL 
  SELECT ItemID,QTY,NExtVal,  DATEADD(DAY, 1, Number)
  FROM Orders  
  WHERE  DATEADD(DAY, 1, Number)  < NExtVal
)     
SELECT ItemID,QTY, Number AS DateSequence FROM Orders
ORDER BY ItemID,Number

Here is the Link to the sample code.

Answer 3

Here's another way:

tmp <- split(d, d$ItemID)
f <- function(x) {
  b <- seq(min(x$ADate), max(x$ADate), "day")
  a <- do.call(c, mapply(rep, x$QTY[-nrow(x)], diff(x$ADate)))
  a <- c(a, x$QTY[nrow(x)])
  data.frame(x$ItemID[1], a, b)
}
out <- do.call(rbind, lapply(tmp, f))
rownames(out) <- NULL
colnames(out) <- c("ItemID", "QTY", "ADate")

Data

d <- "ItemID  QTY  ADate 
10007   2   2011-07-22
10007   1   2011-07-27
10007   2   2011-09-01
10007   1   2011-09-23 
10007   2   2011-09-29 
10007   0   2011-10-29
10007   2   2011-11-03
10007   1   2011-11-21
23028   2   2011-07-23 
23028   0   2011-08-03 
23028   2   2011-08-10"
d <- read.table(text = d,header = T)
d$ADate <- as.POSIXct(d$ADate,"UTC")
d <- d[order(d$ItemID,d$ADate),]

Answer 4

Please find this demo using CTE http://rextester.com/live/CDFT20818 and a number of 2012+ functions otherwise substitute.

declare @mytable table (itemid int,qty int,adate date)

insert into @mytable 
values
( 10007,   2,'   2011-07-22'),
( 10007 ,  1,'   2011-07-27'),
( 10008 ,  1,'   2011-07-23'),
( 10008 ,  0,'   2011-07-16'),
( 10008 ,  4,'   2011-07-10')

declare @som date = '2011-07-01';
declare @eom date = eomonth(@som);



;with myCalendar as 
(
   Select  @som startdate
         union all
    Select dateadd(day,1,startdate) startdate
        from myCalendar
        where startdate < @eom
)
,myDistinctList as (
select t.itemid, c.startdate
  from myCalendar c
  cross join 
  (select distinct itemid from @mytable) t
), myFinal as (
select 
l.itemid,
iif(t.qty is null,(select top 1 qty from @mytable x where  x.itemid = l.itemid and x.adate <= l.startdate order by x.adate desc),t.qty) [qty],
l.startdate [adate]
from myDistinctList l
left outer join @mytable t
    on t.itemid = l.itemid
    and t.adate = l.startdate
) 
select * from myFinal 
where qty is not null
order by itemid,adate;

Answer 5

If I understand correctly, you want a row for every consecutive day, not just days when a change occurs. So for example ItemID 10007 has QTY = 2 on 2011-07-22, 2011-07-23, 2011-07-24...up to 2011-07-27, when it changes to 1.

In that case, try this, assuming you have a data frame in R named mydata and the dates are in character format:

library(tidyverse)
mydata %>% 
  mutate(ADate = as.Date(ADate)) %>% 
  complete(ADate = seq.Date(min(ADate), max(ADate), by = "day")) %>%
  fill(ItemID, QTY)

If you add %>% filter(QTY == 0) to the end, you should see:

ADate      ItemID   QTY
   <date>      <int> <int>
 1 2011-08-03  23028     0
 2 2011-08-04  23028     0
 3 2011-08-05  23028     0
 4 2011-08-06  23028     0
 5 2011-08-07  23028     0
 6 2011-08-08  23028     0
 7 2011-08-09  23028     0
 8 2011-10-29  10007     0
 9 2011-10-30  10007     0
10 2011-10-31  10007     0
11 2011-11-01  10007     0
12 2011-11-02  10007     0

Answer 6

You seem to want to fill in days with the most recent value. One method is a recursive CTE. But that is actually a pain. Let me assume you have a numbers table:

select id.itemid, dateadd(day, n.n, t.min_adate) as dte, t.qty
from (select t.itemid, min(adate) as min_adate
      from t
      group by itemid
     ) id join
     numbers n
     on dateadd(day, n.n, t.min_adate) < getdate() outer apply
     (select top 1 t.*
      from t
      where t.itemid = id.itemid and t.date <= dateadd(day, n.n, t.min_adate)
      order by t.date desc
     ) t;