sum of derived value?

[Lokean]

Hi foks!

I am trying to get the sum of labor per worker.

I have gotten as far as what I have below, with the start time being deducted from the end time, but now I want the total of time spent on the job within a given range of time.

Can I do it within this statement, or do I need to pass this as a function and called by another one?


SELECT WOrder.Worker AS Worker_ID, WOrder.StartDate, WOrder.Date, [LaborTab.StartTime] AS Time1, LaborTab.EndTime, ([EndTime]-[LaborTab.StartTime])*24 AS Time2, LaborTab.WorkCode, dbo_WorkOrderProd.ProdNum, LaborTab.WorkOrdID_ID, WOrder.OrgName, WOrder.WOType, Workers.LastName AS Time200, Workers.FullName
FROM DATE_RANGE, (Workers INNER JOIN (LaborTab INNER JOIN WOrder ON LaborTab.WorkOrdID_ID = WOrder.WorkOrdID_ID) ON Workers.Worker_ID = WOrder.Worker) INNER JOIN dbo_WorkOrderProd ON LaborTab.WorkOrderProd_ID = dbo_WorkOrderProd.WorkOrderProd_ID
WHERE (((WOrder.StartDate) Between [START DATE] And [END DATE]) AND ((([EndTime]-[LaborTab.StartTime])*24)<>'') AND ((LaborTab.WorkCode) Like "AP*") AND ((WOrder.OrgName) Not In ("Test1","Test2")))
ORDER BY LaborTab.WrkLabID, LaborTab.WorkCode DESC;

[iburyak]

Assuming your calculation is correct. I don't see a data sample to make any judgment of it.

[PHP]([EndTime]-[LaborTab.StartTime])* 24[/PHP]

Try this:

[PHP]SELECT WOrder.Worker AS Worker_ID, WOrder.StartDate, WOrder.Date,
[LaborTab.StartTime] AS Time1, LaborTab.EndTime,
sum(([EndTime]-[LaborTab.StartTime])* 24) AS Time2,
LaborTab.WorkCode, dbo_WorkOrderProd.ProdNum,
LaborTab.WorkOrdID_ID, WOrder.OrgName,
WOrder.WOType, Workers.LastName AS Time200, Workers.FullName
FROM DATE_RANGE, (Workers INNER JOIN (LaborTab INNER JOIN WOrder ON LaborTab.WorkOrdID_ID = WOrder.WorkOrdID_ID) ON Workers.Worker_ID = WOrder.Worker) INNER JOIN dbo_WorkOrderProd ON LaborTab.WorkOrderProd_ID = dbo_WorkOrderProd.WorkOrderProd_ID
WHERE (((WOrder.StartDate) Between [START DATE] And [END DATE])
AND ((([EndTime]-[LaborTab.StartTime])*24)<>'')
AND ((LaborTab.WorkCode) Like "AP*")
AND ((WOrder.OrgName) Not In ("Test1","Test2")))
GROUP BY WOrder.Worker, WOrder.StartDate, WOrder.Date,
[LaborTab.StartTime], LaborTab.EndTime,
LaborTab.WorkCode, dbo_WorkOrderProd.ProdNum,
LaborTab.WorkOrdID_ID, WOrder.OrgName,
WOrder.WOType, Workers.LastName, Workers.FullName
ORDER BY LaborTab.WrkLabID, LaborTab.WorkCode DESC;[/PHP]

Good Luck.