I have three tables, one contains information regarding people (table called People):
ID, First Name, Last Name, Location, etc.
The other contains information about these people on a specific date (table called People_History):
ID, RegDate, Score, etc.
... and the third contains required date ranges for these people:
Via a nightly SQL job, new people get added to the first table. If a new person is added to the People table, then it is also added to the People_History table with today's date. For example:
New entry in People table:
Corresponding entry in People_History table:
Now, I have a query that selects the difference in score between the start and end dates as specified in the third table. The problem is, if a previous date in which a record does not exist, then the result of total is NULL because you're subtracting a valid value (41) from a NULL value.
We could assume that if no record exists, then the total is 0. However, I'm not sure how to do this or if I should have query as part of my job that inserts all of the missing records for each date so that no possible NULL values exist. The first possible date in the query is about 5 months back so it would have to insert 150 missing records each time there is a new person.
Thanks in advance!