CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jay
Jay

Reputation: 465

SQL - Insert data rows for weekends and holidays from previous value

I believe I have the logic for this(?), but not 100% sure on the syntax and if this would work. Looking to fill in weekends and holidays (blanks) with its own row of data with the previous days values. So if I have a set of data that looks like this, already ordered by Date

DATE              VALUE           PERCENT            
12/31/2014        100             .01  
1/2/2015          150             .015
1/5/2015          200             .015
1/6/2015          200             .015
1/7/2015          200             .015
1/8/2015          250             .015
1/9/2015          300             .021
1/12/2015         400             .022
1/13/2015         400             .022

So for this set of data above, I'd like to loop through and insert the following rows:

DATE              VALUE           PERCENT            
1/1/2015          100             .01   (carries over the 12/31 values)
1/3/2015          150             .015  (carries over the 1/2 values)   
1/4/2015          150             .015  (carries over the 1/2 or 1/3 values)
1/10/2015         300             .021  (carries over the 1/9 values) 
1/11/2015         300             .021  (carries over the 1/10 values)

Here is my suggested psuedo code:

DECLARE @CurrDate datetime
DECLARE @VALUE int
DECLARE @PERCENT decimal(20,2)

SET @CurrDate = Min(Date) from table
SET @VALUE = value from table where Date = @CurrDate
SET @PERCENT = percent from table where Date =@CurrDate


For each Date in table

  @CurrDate = CurrDate + 1

    If exists (SELECT Date From table where Date = @CurrDate) then 

       @VALUE    = select value from table where date = @currDate
       @PERCENT  = select percent from table where date = @currDate          

    next

ELSE

    INSERT INTO table (SELECT @CurrDate, @VALUE, @PERCENT)

END

NEXT

Any help would be greatly appreciated!

Upvotes: 0

Views: 1308

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You could repeated do an insert like this:

insert into table(date, value, percent)
    select dateadd(day, 1, date)
    from table
    where not exists (select 1
                      from table t2
                      where t2.date = dateadd(day, 1, t.date)
                     );

You would probably need to do this two or three times, depending on how many consecutive days you are missing.

Upvotes: 2

Related Questions