Query or Loop to Insert Data - SQL 2005

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:

ID,RegDateStart,RegDateEnd,etc.

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:
32434,Joe,Smith,Seattle

Corresponding entry in People_History table:
32434,'2008-10-20 00:00:00.000',41

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!
adrian78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
Please show sample data illustrating the problem.  with that ... I bet one of us can write you a query that solves it.
0
folderolCommented:
Do you want the null score to be zero, or the difference to be zero if only one score exists?  The snippet gives two formulas for Scorediff, so remove the one that doesn't apply....
select
People.ID, People.First Name, People.Last Name, People.Location,
RegDateStart, PH_start.Score, RegDateEnd, PH_end.Score, 
isnull(PH_end.Score,0) - isnull(PH_start.Score,0) as Scorediff
-- or alternate to give zero result if only one score exists
case when PH_end.Score - PH_start.Score is null then 0 else PH_end.Score - PH_start.Score as Scorediff
from
People
join third_table on People.ID = third_table.ID
left outer join PeopleHistory PH_start on People.ID = PH_start.ID  and third_table.RegDateStart = PH_start.RegDate
left outer join PeopleHistory PH_end on People.ID = PH_end.ID  and third_table.RegDateEnd = PH_start.RegDate

Open in new window

0
Mark WillsTopic AdvisorCommented:
if it is otherwise null then simply do isnull(score,0) when selecting rows...

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

adrian78Author Commented:
Hi Guys... Thanks for the response.  I haven't had a chance to look at this yet but I will soon.
0
adrian78Author Commented:
Hi everyone.  That works well but I'm still stuck in the situation in which I want to get all rows for a specific week.  Now the problem lies when the RegDateStart and/or the RegEndDate is within the week.  For example, I want the week Monday through Sunday which is Oct  27 - Nov 2:

ID,RegDateStart,RegDateEnd,etc.
32435, '2008-10-15 00:00:00.000', '2008-11-15 00:00:00.000' <-- entire range covers the required week

32439, '2008-10-20 00:00:00.000', '2008-10-28 00:00:00.000'  <--- only partial week is required since enddate is before the required enddate

32440, '2008-10-28 00:00:00.000', '2008-11-15 00:00:00.000'  <--- only partial week is required since startdate is after the required startdate

32442, '2008-10-28 00:00:00.000', '2008-11-30 00:00:00.000'  <--- only partial week is required (3 days) since startdate and enddate are within the queried week

Understand my dilemma?  I can say where startdate <= Oct  27 and enddate >= Nov 2 BUT then that eliminates the 3 last ones.

Thanks in advance!
0
Daniel WilsonCommented:
Classic time-bracket issue.  Please try:

where startdate >= Oct  27 and enddate <= Nov 2
0
adrian78Author Commented:
I solved the problem using UNION to combine queries with different W|HERE clauses.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.