Solved

Query or Loop to Insert Data - SQL 2005

Posted on 2008-10-20
7
244 Views
Last Modified: 2013-11-30
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!
0
Comment
Question by:adrian78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22760942
Please show sample data illustrating the problem.  with that ... I bet one of us can write you a query that solves it.
0
 
LVL 19

Assisted Solution

by:folderol
folderol earned 250 total points
ID: 22761302
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22767343
if it is otherwise null then simply do isnull(score,0) when selecting rows...

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:adrian78
ID: 22769788
Hi Guys... Thanks for the response.  I haven't had a chance to look at this yet but I will soon.
0
 

Author Comment

by:adrian78
ID: 22844327
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22844430
Classic time-bracket issue.  Please try:

where startdate >= Oct  27 and enddate <= Nov 2
0
 

Accepted Solution

by:
adrian78 earned 0 total points
ID: 22954243
I solved the problem using UNION to combine queries with different W|HERE clauses.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question