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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.Get Access
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.