Databases
StackOverflow2013
Postsdbo
POReturn data entered in column order by row
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturn data entered in column order by row
    text
    copied!<p>I am working on a simple timesheet module for a larger production system and need to display a table of information to the user. I have the following tables to work with:</p> <pre><code>TimeRecords ID WorkerID AssyLineID Station Sequence NbrHours DateSubmitted Workers ID Name AssyLines Name </code></pre> <p>During data entry, time is entered by AssyLine for each worker. A given worker may work on 2 or more different stations during the course of the day. The Sequence value is assigned based on the order of names as entered during data entry.</p> <p>Now I want to return this data for all assembly lines and all workers in the following format:</p> <pre><code>ResultSet Worker.ID Worker.Name AssyLine.Name - group returned rows by assembly line, in alphabetical order Sequence - within each assembly line, group by sequence NbrHours - total hours for worker for this assembly line, all stations TotalHours - total hours for this worker across all assembly lines and stations </code></pre> <p>Other caveats: 1) The rows for a given worker should be grouped together, starting with the assembly line where they logged the most hours, in the sequence for that assembly line. I plan to consolidate all entries for a given worker into one row for display to the user and this is much easier if all rows for one user are grouped together. If that can't be done I will have to group and sort the row data in code... </p> <p>Here is the query I have come up with so far:</p> <pre><code> SELECT w.ID ,w.Name ,a.Name ,tr.NbrHours ,tr.Seq FROM TimeRecords tr INNER JOIN Workers w ON w.ID = tr.WorkerId INNER JOIN AssyLines a ON a.AssyLineID = tr.AssyLineId WHERE tr.DateSubmitted &lt; '2000-01-01' ORDER BY w.Name ,a.Name ,tr.Seq ,NbrHours DESC </code></pre> <p>Obviously this leaves a lot to be desired. The worker entries are not grouped together and there is no overall total for the worker.</p> <p>Can anyone help me get this right? I'm thinking I will need to do this with a Stored Proc rather than a view...</p> <p>Thanks, Dave</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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