Insert missing date records

[trdandamudi]

Hi,

I did opened a discussion question on this topic but the logic has changed regarding how the data has to get populated. So i thought opening a new discussion is appropriate.

I need help to insert missing date records. The source table is as below:

Date           Rate       Grade  Status

1/10/2016    215914     1         10

1/10/2016    215914      3         13

1/15/2016    315456      4         15

2/2/2016      456234      5         20

1) In the Script you can have a Start_Date and End_Date  <--- I will hard code the date before running the script for the months required. (MM/DD/YYYY)

2) Now every "Rate" should have a record from Start_Date to End_Date.(It should only insert the missing dates)

For example when you run this script with Start_Date = 1/1/2016 and End_Date = 2/29/2016 you should get the below output which is attached in excel. (For the above four records when you run the script you will get 181 records).

Really appreciate if some one can help or guide me in the right direction.

[sunny_talwar · Accepted solution]

Hopefully no more issues now

Table:

LOAD * Inline [

Date,          Rate,      Grade,  Status

12/1/2015,    215914,      3,        13

12/1/2015,    215914,    1,        10

12/5/2015,    215914,    1,        10

1/15/2016,    315456,      4,        15

2/2/2016,      456234,      5,        20

];

LET vMin = Num(MakeDate(2015, 12, 1));

LET vMax = Num(MakeDate(2016, 2, 29));

Temp:

LOAD Date($(vMin) + IterNo() - 1) as Date1,

  Date,

  Rate,

  Grade,

  Status

Resident Table

While $(vMin) + IterNo() - 1 <= $(vMax);

Temp1:

NoConcatenate

LOAD Date1 as Date,

  Rate,

  If(Date = Date1, Grade, 0) as Grade,

  If(Date = Date1, Status, 0) as Status

Resident Temp;

FinalTable:

LOAD *

Where Flag = 0;

LOAD *,

  If(Rate = Peek('Rate') and Date = Peek('Date') and Grade = 0, 1, 0) as Flag

Resident Temp1

Order By Rate, Date, Grade desc;

DROP Tables Table, Temp, Temp1;

[trdandamudi]

It is working... In the mean while I am able to do it in little long way by using joins and even that is working. So I ran your code and also mine to compare the result and see if there is a miss. Both the results are matching. I will still do some more testing to make sure everything is perfect.

Thank you very much for your time.

[sunny_talwar]