What I need to do now is add the sum of estimated_hours contained in the LineItem table for each job included in each day's count. It seems to me that I need to do a sub-select of the sum of hours for each order (via the job), but I don't know where or how to insert it. I tried the following to try to get the sum of hours for each day, but it doesn't work:
SELECT COUNT(pk_order) AS order_count, datepart(dw,date_scheduled) AS DayOfWeek FROM Order WHERE date_scheduled >= '8/24/2014' AND date_scheduled <= '8/30/2014' GROUP BY datepart(dw,date_scheduled)
What I'm looking for is the following:
SELECT SUM(LineItem.quantity) AS total_hours, datepart(dw,Order.date_scheduled) AS DayOfWeek FROM LineItem INNER JOIN Job ON LineItem.fk_job = Job.pk_job INNER JOIN Order ON Job.fk_order = Order.pk_order WHERE LineItem.type = 'L' AND Order.date_scheduled >= '8/24/2014' AND Order.date_scheduled <= '8/30/2014' GROUP BY datepart(dw,Order.date_scheduled)
Can anyone point me in the right direction?
ro_count total_hours DayOfWeek 3 7.4 2 5 11.6 3 5 13.0 4 etc.
From novice to tech pro — start learning today.