• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Fill in gaps on existing data

I have existing data that has of the week and hours of operation. The existing data uses 1-7 for days of week, but I need to subtract 1 to load into my databse (this is an ETL process).

If the entity is not open on a specific day in the source data, it is not present. However, in my table, I need to create a record for each day of the week. I have an IsClosed indicator if the entity is closed on that day.

Thus, I need to load the following source data:
DAY      START      END
1      0800      2200
2      0800      2200
3      0800      2200
4      0800      2200
5      0800      2300
6      0800      2300

into the following destination structure:
Day      Start      End      IsClosed
0      0800      2200      0
1      0800      2200      0
2      0800      2200      0
3      0800      2200      0
4      0800      2300      0
5      0800      2300      0
6      NULL      NULL      1

Note, I've subtracted 1 from the source Day, and identified Day 7 in the source (converted to 6) as missing, so I've 'created' a record with NULL times and IsClosed = 1.

Please try to avoid a recursive solution and we are dealing with millions of records, and I don't want to bring our server to its knees.
  • 2
  • 2
1 Solution
Guess we miss an entity ID, but I would create a temp table with 7 rows (0-6) holding the day value.
Now the original table can be JOINed using the "-1" formula for the day field and making the temp table mandatory. (A so-called left or right outer join).

Now you'll get for each entity 7 rows and for each missing entity the entity ID can be tested for NULL and use that for making the IsClosed 1 or 0.
dbbishopAuthor Commented:
Would work except (and perhaps I should have shown it in my data, there is also an EntityID that needs to be carried over into the 'missing' data.
Then start with building the temp table based on a
INTO FullTempTable
from YourBaseTable, TempTable

The TempTable with the 0-6 will cause a cartesian product, thus making the complete finally needed number of records for your table. Using this FullTempTable LEFT (or RIGHT) JOINed like described earlier by the DAY and ENTITYID should do the trick.
dbbishopAuthor Commented:
got it. thx.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now