For each SQL query
We have a database that contains information about times booked for projects. We have a live recording for the project that contains the correct timing information (imported from SAP once a week).
We also have archival records showing information about time at a specific point in time, they are taken once a week, in fact, they are snapshots. Users can later add a request to adjust the project time. If this happens, live recording will display the correct time for the project, but there will be no snapshots. 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 SAP datasheet that has entries for each time entry for a project, indicating the project code for which it was specified, the date it was specified for, and the amount of time. The Historical Data table contains the project code and the date the snapshot 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 taken to look at the SAP table and get all temporary records for that project code up to the date the snapshot was taken.
I am having a real problem with this in one SQL query, the main problem is that I actually need to run the query for every record in the historical data table. I keep coming up with pages suggesting the use of cursors, but I don't know much about them, and I keep seeing articles saying they are bad. Any suggestions on what kind of query I can use to handle 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.
Examples of 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 get a query showing
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 entry in the request shows all hours booked for project 1234 before or before 09/01/2009
source to share
This seems to work based on the data you 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
source to share