Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tjfitz68
Starting Member
2 Posts |
 Posted - 2011-05-27 : 12:42:29
|
| Hello,List below is a query that I need to somehow be able to modify so my results do not double the tblWorkOrder.iWorkOrderID rows. The reason it's happening is because the INNER join for the tblTechnician causes it to double the iWorkOrderID's when there are two or more technicians that are listed on the Workorder. Hopefully I am explaining this correctly. Oh, I didn't create the query below but I have to fix it. If I can't accomplish what I need to by modifying the query, I'm going to have to re-write the complete report this is used for.*************SELECT tblDistrict.iCode, tblSchool.iUserCode, tblSchool.strSchoolName, Sum(tblWorkOrder.dStock) AS SumOfdStock, Sum(tblWorkOrder.dDirect) AS SumOfdDirect, Sum(tblWorkOrder.dLabor) AS SumOfdLabor, Sum(tblWorkOrder.dTotal) AS SumOfdTotal, Sum(tbltechnician.hoursWorked) AS SumOfhoursWorked, tblDistrict.iDistrictID, tblDistrict.strDistrictNameFROM (tblDistrict INNER JOIN (tblSchool INNER JOIN (tblWorkOrder INNER JOIN tblWorkOrderRequest ON tblWorkOrder.iWorkOrderRequestID = tblWorkOrderRequest.iWorkOrderRequestID) ON tblSchool.iSchoolID = tblWorkOrderRequest.iSchoolID) ON tblDistrict.iDistrictID = tblSchool.iDistrictID) INNER JOIN tbltechnician ON tblWorkOrder.iWorkOrderID = tbltechnician.iworkorderIDWHERE (((tblDistrict.IDistrictID)<>0) AND ((tblSchool.iSchoolID)<>0) AND ((tblWorkOrder.iOrderStatusID)=4 Or (tblWorkOrder.iOrderStatusID)=3) AND ((tblWorkOrder.iRequestID)<>2) AND ((tblWorkOrder.dDateCompleted) Between #3/1/2011# And #3/31/2011#))GROUP BY tblDistrict.iCode, tblSchool.iUserCode, tblSchool.strSchoolName, tblDistrict.iDistrictID, tblDistrict.strDistrictName;************Thanks,Tim |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-06-01 : 10:12:04
|
| how do you identify which technician you want when " two or more technicians that are listed on the Workorder"?remember we don't know your business requirements/setup, so you will have to patient and detail things very precisely.In business terms what are you tring to achieve..etc. the sum of hours worked for workorders, for a school - given some work orders have multiple staff assigned? |
 |
|
|
tjfitz68
Starting Member
2 Posts |
Posted - 2011-06-07 : 09:47:52
|
| You know, I'm not really sure. I didn't create the report originally. I haven't had time to do anything with it since posting my question.Thanks for the response. |
|
|
|
|
|
|
|
|
Query Issue