Subqueries and AVG() on a subtraction

Working on a query to return the average time from when an employee begins his/her shift and then arrives at the first home (this DB assumes they are salesmen).

What I have:

SELECT l.OFFICE_NAME, crew.EMPLOYEE_NAME, //avg(first arrival time)
FROM LOCAL_OFFICE l, CREW_WORK_SCHEDULE crew,
WHERE l.LOCAL_OFFICE_ID = crew1.LOCAL_OFFICE_ID

You can see the AVG() command is commented out, because I know the time that they arrive at work, and the time they get to the first house, and can find the value using this:

(SELECT MIN(c.ARRIVE)
 FROM O开发者_运维知识库RDER_STATUS c
 WHERE c.USER_ID = crew.CREW_ID)
 -(SELECT START_TIME
 FROM CREW_SHIFT_CODES
 WHERE WORK_SHIFT_CODE = crew.WORK_SHIFT_CODE)

Would the best way be to simply put the above into the the AVG() parentheses? Just trying to learn the best methods to create queries. If you want more info on any of the tables, etc. just ask, but hopefully they're all named so you know what they're returning.

As per my comment, the example you gave would only return one record to the AVG function, and so not do very much.

If the sub-query was returning multiple records, however, your suggestion of placing the sub-query inside the AVG() would work...

SELECT
  AVG((SELECT MIN(sub.val) FROM sub WHERE sub.id = main.id GROUP BY sub.group))
FROM
  main
GROUP BY
  main.group

(Averaging a set of minima, and so requiring two levels of GROUP BY.)

In many cases this gives good performance, and is maintainable. But sometimes the sub-query grows large, and it can be better to reformat it using an inline view...

SELECT
  main.group,
  AVG(sub_query.val)
FROM
  main
INNER JOIN
(
  SELECT
    sub.id,
    sub.group,
    MIN(sub.val) AS val
  FROM
    sub
  GROUP BY
    sub.id
    sub.group
)
  AS sub_query
    ON sub_query.id = main.id
GROUP BY
  main.group

Note: Although this looks as though the inline view will calculate a lod of values that are not needed (and so be inefficient), most RDBMS optimise this so only the required records get processes. (The optimiser knows how the inner query is being used by the outer query, and builds the execution plan accordingly.)

Don't think of subqueries: they're often quite slow. In effect, they are row by row (RBAR) operations rather than set based

1. join all the table together
2. I've used a derived table to calculate the 1st arrival time
3. Aggregate

Soemthing like

SELECT
    l.OFFICE_NAME, crew.EMPLOYEE_NAME,
    AVG(os.minARRIVE - cs.START_TIME)
FROM
    LOCAL_OFFICE l
    JOIN
    CREW_WORK_SCHEDULE crew On l.LOCAL_OFFICE_ID = crew1.LOCAL_OFFICE_ID
    JOIN
    CREW_SHIFT_CODES cs ON cs.WORK_SHIFT_CODE = crew.WORK_SHIFT_CODE
    JOIN
    (SELECT MIN(ARRIVE) AS minARRIVE, USER_ID
     FROM ORDER_STATUS
     GROUP BY USER_ID
    ) os ON oc.USER_ID = crew.CREW_ID
GROUP B
    l.OFFICE_NAME, crew.EMPLOYEE_NAME

This probably won't give correct data because of the minARRIVE grouping: there isn't enough info from ORDER_STATUS to show "which day" or "which shift". It's simply "first arrival for that user for all time"

Edit:

This will give you average minutes

You can add this back to minARRIVE using DATEADD, or change to hh:mm with some %60 (modul0) and /60 (integer divide

AVG(
    DATEDIFF(minute, os.minARRIVE, os.minARRIVE)
    )