For each SQL query

Question

We have a database that contains information about time booked to projects. We have a live record for a project, that holds the correct time information (imported from SAP once a week).

We also have archived records showing the time information at a point in time, these are taken once a week, essentially they are snap shots. It is possible for users to later put in a request to adjust the time on project. If this happens, the live record will reflect the correct time for the project, however the snapshots will not. I need to write a query that goes through and updates these snapshots with the correct time for the project, at that point in time.

We have a table of SAP data, that has records for each time entry for a project, showing the project code it was for, the date it was for, and the amount of time. The Historical data table contains the code for the project and the date the snap shot was taken. So I need to write a SQL query that will go through the historical data table, and for each row update the time spent by looking at the SAP table and getting all time entrys, for that project code, before the date that snapshot was taken.

I'm having real difficulty doing this in one SQL query, the main problem being I really need to run a query for each record in the historical data table. I keep coming up with pages suggesting using cursors, but I don't know much about them and keep seeing articles saying they are bad. Any suggestions on what sort of query I can use to get on the way with this would be great!

Essentially what I want to do in pseudocode is:

For Each Project Code in the hostorical data table, archived on a date, select all time entrys on or before that date.

Sample tables

Historical Project Data                          SAP Data
-----------------------                        ----------------
Project Code | run date                        Project Code | Time | Date
1234         | 01/09/2009                      1234         | 2    | 29/08/2009
9876         | 01/09/2009                      1234         | 5    | 29/08/2009
1234         | 07/09/2009                      9876         | 10   | 02/09/2009
9876         | 07/09/2009                      1234         | 2    | 03/09/2009

So I would like to end up with a query that shows

Project Code | run date   | time
1234         | 01/09/2009 | 7                      
9876         | 01/09/2009 | 0                      
1234         | 07/09/2009 | 9                      
9876         | 07/09/2009 | 10

So for example, the first record in the query shows all hours booked to project 1234, on or before the 01/09/2009

Answer 1

That's just a SELECT statement (with a non-equi join). I recommend learning basic SQL concepts before trying to do database development.

Answer 2

This appears to work based on the data you have provided:

create table #historical_project
(project_code int
,run_date datetime
)

create table #sap
(project_code int
,time_val int
,date datetime
)

insert #historical_project
      select 1234,'20090901'
union select 9876,'20090901'
union select 1234,'20090907'
union select 9876,'20090907'

insert #sap
      select  1234,2 , '20090829'
union select  1234,5 , '20090829'
union select  9876,10, '20090902'
union select  1234,2 , '20090903'


SELECT  *
        ,(SELECT ISNULL(SUM(time_val),0)
           FROM #sap                AS sp
           WHERE hp.project_code  = sp.project_code
           AND   hp.run_date     >= sp.DATE
          ) AS time
FROM #historical_project AS hp
ORDER BY run_date
         ,project_code