Databases
StackOverflow2013
Postsdbo
POFor each SQL query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFor each SQL query
    primarykey
    data
    text
    <p>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).</p> <p>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.</p> <p>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.</p> <p>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!</p> <p>Essentially what I want to do in pseudocode is:</p> <pre><code>For Each Project Code in the hostorical data table, archived on a date, select all time entrys on or before that date. </code></pre> <p><strong>Sample tables</strong></p> <pre><code>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 </code></pre> <p>So I would like to end up with a query that shows</p> <pre><code>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 </code></pre> <p>So for example, the first record in the query shows all hours booked to project 1234, on or before the 01/09/2009</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USSam Cogan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFor each SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFor each SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIgnore people saying cursors are bad. They just don't get it. If they try to hammer a nail into a wall using a drill, it's not the fault of the drill.
      singulars
      1. POFor each SQL query
      1. USJohn Smithers
    2. COWhile I wouldn't say 'never use a cursor unless absolutely necessary' it is quite important to get in the habit of set-based logic and thinking. It's a mistake of many to always go straight to loops, cursors and procedural thinking when a very simple set-based query can solve the problem in an easy, simple and fast manner.
      singulars
      1. POFor each SQL query
      1. USChris Cameron-Mills
    3. COThis has exactly what to do with Access?
      singulars
      1. POFor each SQL query
      1. USDavid-W-Fenton
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload