I have a work order table. I need to know work orders scheduled, open and complete.
Query 1 All work orders with work type PM with a TargCompDate between 2 dates returns a count of work orders scheduled.
Query 2 - All work orders with a work type of PM and a work status of In Progress.
Query 3 - All work orders with a completion date between 2 dates and a status of Comp.
Need Query 4 to tie all these together I guess grouped on work order location then work order crew id.
I have tried structuring these queries multiple ways to get this output below but no good.
Location Crew WO Scheduled WO Open WO Complete
Bldg1 HVAC 10 5 5
Bldg 1 PLUM 9 3 6
Bldg 2 HVAC 20 9 11
Bldg 2 PLUM 5 2 3
Quote from: KLJernigan600 on 08 Dec 2022 06:09:54 PM
I have a work order table. I need to know work orders scheduled, open and complete.
Query 1 All work orders with work type PM with a TargCompDate between 2 dates returns a count of work orders scheduled.
Query 2 - All work orders with a work type of PM and a work status of In Progress.
Query 3 - All work orders with a completion date between 2 dates and a status of Comp.
Need Query 4 to tie all these together I guess grouped on work order location then work order crew id.
I have tried structuring these queries multiple ways to get this output below but no good.
Location Crew WO Scheduled WO Open WO Complete
Bldg1 HVAC 10 5 5
Bldg 1 PLUM 9 3 6
Bldg 2 HVAC 20 9 11
Bldg 2 PLUM 5 2 3
Hi,
Maybe just do this in one query, with the WO Scheduled, WO Open and WO Complete values defined as calculations?
eg
First calculation - WO Scheduled
if ([Work Type] = 'PM' and [TargCompDate] between <your start date> and <your end date>) then (1) else (0)
Second calculation - WO Open
if ([Work Type] = 'PM' and [Work Status] = 'In Progress') then (1) else (0)
Third calculation - WO Complete
if ([Completion Date] between <your start date> and <your end date> and [Work Status] = 'Comp') then (1) else (0)
You will probably need to bring in the items from the package rather than the query, so you'll see [Your Package].[Your Query Subject].[Work Type] in your expression rather than just [Work Type].
You will probably also need to change the Detail Aggregation and Summary Aggregation properties of your calculations to be Total, also.
Does this work for you?
Cheers!
MF.
Worked great, thank you very much.
KJ