SQL fill next date (month) with loop-CodePudding

I have input table, and need to add missing dates, but not to max, but up to next available month. so I need to use loop.

SET @mindate = '2021.01'
SET @maxdate = CAST( GETDATE() AS Date ) --date today as max date
while 
begin
if @mindate => @maxdate 
begin 
break
end
set @mindate = @mindate   1
end

then i can get 1 .. but it does not stop to 7 month

so i totally got stuck with writing loop.

Data table :

could anybody help on code? as most examples are with joins, to data tables, or to one max value.

CodePudding user response：

Paul, I'm assuming that you forgot to specify the month in your mock data.

I hope the code below may help you understand how non-trivial is what you are trying to accomplish :-) Kudos for your will to get rid of loops.

To make it better, I propose a denormalization (CAUTION!):

create another column price_valid_until
the latest prices records will have price_valid_until = '21000101' (aka, far away in the future)
when registering a new price, update the previous with new price_valid_from - 1 day

Here's the solution, with a pretty complex, but efficient query (http://sqlfiddle.com/#!18/4ab23/4)

create table price_history(
  SKU varchar(255),
  price_valid_from date,
  price decimal(16, 2)
)

insert into price_history
values
  ('a', '20210101', 10),
  ('a', '20210107', 12),
  ('b', '20210102', 4),
  ('b', '20210110', 2),
  ('b', '20210214', 5);

-- This fiddler won't let me initialize and reference:
-- 
-- declare
--   @from_date date,
--   @to_date   date;
-- 
-- select
--   @from_date = min(date_from),
--   @to_date   = max(date_from)
-- from price_history

with
  date_range as(
    select
      min(price_valid_from) as from_date,
      --
      eomonth(
        max(price_valid_from)
      ) as to_date
    from price_history
  ),
  --
  all_dates as(
    select from_date as date_in_range
    from date_range
    -- ----------
    union all
    -- ----------
    select dateadd(day, 1, date_in_range)
    from all_dates
    where
      date_in_range < (
        select to_date
        from date_range
      )
  ),
  --
  price_history_boundaries as(
    select
      ph.SKU,
      ph.price,
      --
      ph.price_valid_from,
      -- The latest price, so far, is valid until 01/01/2100
      coalesce(
        dateadd(
          day,
          -1,
          min(ph_next.price_valid_from)
        ),
        '21000101'
      ) as price_valid_until
    from
      price_history ph
        left outer join price_history ph_next
        on(
              ph_next.SKU              = ph.SKU
          and ph_next.price_valid_from > ph.price_valid_from
        )
    group by ph.SKU, ph.price_valid_from, ph.price
  )
select
  phb.SKU,
  ad.date_in_range,
  phb.price
from
  all_dates ad
    inner join price_history_boundaries phb
    on(
          phb.price_valid_from  <= ad.date_in_range
      and phb.price_valid_until >= ad.date_in_range
    )
order by phb.SKU, ad.date_in_range

CodePudding user response：

You can easily achieve your desired result by creating list of dates from which to join to. Here I've used a recursive CTE to create a range of dates, adding 1 month per iteration up to the current date.

It's then a simple matter of joining to your source data, here lead() is handy for limiting the joined rows. Also, assuming SQL Server from the usage of Getdate:

declare @start date=(select Min([date]) from sourcetable);

with m as (
    select 1 num, @start [Date]
    union all
    select num 1 , DateAdd(month,1,m.[date])
    from m
    where DateAdd(month,1,m.[date]) <= GetDate()
), t as (
    select *, Lead([date],1,GetDate()) over (order by [date]) NextDate
    from sourcetable
)
select m.[Date], t.sku, t.price
from m
join t on m.[date] >= t.[date] and m.[date] < t.nextdate

See Working Fiddle