424,294 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

joining queries together

P: 40
i have two queries I want to join so i can get the percentages from each department of completed work orders but not sure how to go about it. I know i want a join and a crosstab query so i can display the results in a report.
The first query calculates the numerator,

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder, 
  2.        MaximoReport.[Assigned Owner Group] 
  3. FROM MaximoReport
  4. WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
  5. AND ((MaximoReport.Status) Like "*COMP") 
  6. AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) 
  7. And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) 
  8. AND ((MaximoReport.ActualLaborHours)<>"00:00") 
  9. AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) 
  10. And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])))
  11. GROUP BY MaximoReport.[Assigned Owner Group];
while the second query calculates the denominator:

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(MaximoReport.WorkOrder) AS CountOfWorkOrder, 
  2.        MaximoReport.[Assigned Owner Group]
  3. FROM MaximoReport
  4. WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
  5. AND ((MaximoReport.Status)<>"CAN") 
  6. AND ((MaximoReport.[Target Start])>=DateAdd("h",-11.8,[Enter the start date]) 
  7. And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])))
  8. GROUP BY MaximoReport.[Assigned Owner Group];
please advise how i cann join the two queries to get the percentages of the departments and then do a crosstab query.
If there a better way of doing this please also let me know.

Thought of running the query this way but I get errors.

Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(iif((MaximoReport.Status Like "*COMP"),iif((MaximoReport.ActualLaborHours<>"00:00"),1,0),0)) AS CountCompleted,
  2.        SUM(iif((MaximoReport.Status) <> "CAN",1,0)) AS CountTotal, 
  3.        Round([CountCompleted]/[CountTotal]*100,1) AS PercentCompleted, 
  4.        MaximoReport.[Assigned Owner Group]
  5. FROM MaximoReport
  6. WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
  7. AND ((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) 
  8. AND (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) 
  9. AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) 
  10. AND (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])))
  11. GROUP BY MaximoReport.[Assigned Owner Group];
2 Weeks Ago #1
Share this Question
Share on Google+
8 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,894
Similar to yours, but try this (I can't vouch for my parentheses):

Expand|Select|Wrap|Line Numbers
  1. SELECT MaximoReport.[Assigned Owner Group], 
  2.        Sum(IIf([MaximoReport].[Status] Like "*COMP", 1, 0)) AS CompleteWorkOrders,
  3.        Sum(IIf([MaximoReport].[Status]<>"CAN", 1, 0)) AS TotalWorkOrders, 
  4.        Round(CompleteWorkOrders/CompleteWorkOrders*100, 1) AS PercentCompleted 
  5. FROM MaximoReport
  6. WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) 
  7. AND (((MaximoReport.[Target Start])>=DateAdd("h",-1,[Enter the start date]) 
  8. And (MaximoReport.[Target Start])<DateAdd("h",23,[Enter the end date])) 
  9. AND ((MaximoReport.ActualLaborHours)<>"00:00") 
  10. AND ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,[Enter the start date]) 
  11. And (MaximoReport.ActualStartDate)<DateAdd("h",23,[Enter the end date])))
  12. GROUP BY MaximoReport.[Assigned Owner Group];
2 Weeks Ago #2

P: 40
when i do that it doesnt get the right results for department presentages
2 Weeks Ago #3

P: 40
The numbers are not coming out is there a better way to do what im trying if i run the department queries seperate they work but when i try to do it with one query the numbers dont match.
2 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 2,894
I trust that your response is correct. However, how do you know that the values are incorrect? What indicators do you have that the total work orders and the total complete work orders are not being counted correctly.

This is what troubleshooting is all about.

I.e., which records are being missed that should be included or which records are included that should be excluded? Then, after you look at those records, we determine how to include/exclude them....
2 Weeks Ago #5

P: 40
Because i took the cheese department and calculated the cheese department which is 159/184 and i 86.41 but when i run the queries with all the drpartments i get 95.2
2 Weeks Ago #6

twinnyfo
Expert Mod 2.5K+
P: 2,894
And what are those same values with the new query (not just the percentage)? Which records are missing and why. This is what you need to figure out.

Keep in mind that from this thread and previous threads concerning this group of queries. I am still confused by the ActualLaborHours<>“00:00”. I guess, either it is a work order or it’s not. BUT—I don’t really understand what it represents....
2 Weeks Ago #7

P: 40
I need to count the work orders for each department that is comp ,fcomp with actual labor hours that the work order was complete not in 00:00 and between the the start date and end date.
2 Weeks Ago #8

twinnyfo
Expert Mod 2.5K+
P: 2,894
However, you’re still not telling us which records are being included or excluded, based upon the query.

When asked a specific question, please provide an answer.

Based upon the queries you provided, it “appears” the the only differences between the queries is the actual start/stop dates and the ActualLaborHours.... It would seem logical that you would want to compare apples to apples and not apples to oranges.
2 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.