LEARN. PRACTICE. GROW.1. Define the decision before designing the query
A fictional APS committee wants to decide whether one support engineer can move to a migration project. The dashboard shows tickets by owner, but nobody recorded whether values represent today or the previous month’s close. Before adjusting filters, write the question: which items were open, for which services, under whose responsibility and at what instant? Record which states the local process considers open rather than assuming every project uses the same names. Identify exclusions such as cancelled requests or another team’s work. Then ask whether a count is sufficient for the decision. Two tickets can require very different effort and skills. The report should support a discussion about capacity, deadlines and operational coverage. It does not automatically turn item counts into productivity or a measure of removable staff.
2. Distinguish current state, historical state and past assignment
Consider an item assigned to Rita in early September, Luís at closing and Rita again in October. Current state answers a different question from state at closing. A search for having been assigned to Rita can also be true without her owning it at the requested instant. In WIQL, ASOF applies conditions to historical values; EVER identifies a past occurrence of the searched value. They are not interchangeable. Draw the example timeline before writing the expression. A present-day ChangedDate filter also does not reconstruct an old revision: it may simply exclude items that have since changed. During handover, explain that an item changed after closing can still belong to the historical population. Its later change is not sufficient reason to remove it from that analysis.
3. Separate ID selection from field retrieval
The exporter has two responsibilities. First, it finds items that meet the question. Then, it retrieves fields that will appear in the report. Query By Wiql returns work-item references and metadata, including columns; listing Title in SELECT does not mean receiving every item title in that response. Work Items List can retrieve fields for selected IDs and accepts asOf in UTC. For a historical close, keep the same instant for retrieval and selection. Retain the query response’s asOf and check its consistency with the request. Compare requested IDs with those actually retrieved. Partial failure must not silently become a reduction in workload. The exercise assumes accessible revisions exist; retrieval errors require investigation and an explicit limitation in the report before its totals support a decision.
4. Fix the instant and identity across an international team
The expression 01/10/2026 without a convention can be interpreted differently. Even an ISO date without a zone does not necessarily identify the same instant on two machines. In the exercise, the committee approves 2026-09-30T23:00:00Z as its reference. Retain that value, generation date and executing identity in separate fields. A macro such as @Me refers to the query executor rather than the person who saved it. If Ana and Bruno have equal access but search their own assignments, different lists can be correct. For a report about Ana, express that scope explicitly. For a personal view, retain the macro and identify its purpose. Do not remove every parameter for convenience: make the ones affecting comparison meaning visible and validate the context of the client being used.
5. Choose a query shape that supports the question
An item list and a relationship list do not represent the same object. To find a Feature linked to a Bug, distinguish source criteria, target criteria and link type or direction. A flat condition selecting Features or Bugs does not establish that they are related. Conversely, a tree query with MODE(Recursive) does not support combination with ASOF or ORDER BY. If you need hierarchy at a past instant, removing ASOF merely to obtain a response changes the requirement. Define another historical evidence strategy and validate it before presenting results. This lesson does not implement historical relationship reconstruction. In a real project, that limitation belongs in reporting solution planning, with an owner, proof of concept and acceptance criteria, rather than remaining hidden in an apparently convincing chart.
6. Separate reading, editing and access administration
In this lesson’s private-project example, users have Basic access and item permissions have already been confirmed. Someone who can see the dashboard may still lack Read on the underlying query; opening the page does not by itself make the widget accessible. To allow editing queries in a folder, consider Contribute. Managing that folder’s permissions is a separate Manage Permissions capability. A request to correct a filter does not imply granting access administration. Examine effective permissions and any denies before applying changes through the approved process. During transition to RUN, document who maintains the definition, who approves scope changes and who can resolve access failures. An authorized static export can support a meeting, but identify it as a snapshot with a date and limitations rather than as a repaired widget.
7. Rehearse the error with a small inspectable history
Run this lesson’s Python model without credentials. The data is invented and the function selects the latest supplied revision up to the specified instant. Two items satisfy Active at closing. Later, one changes state and owner. Observe that reusing the same IDs with current fields preserves row count but changes the report’s distribution and meaning. There is also an item created after closing and another whose change occurs one second later; both help check the time boundary. Change a revision and predict the result before rerunning. The model does not interpret WIQL or apply Azure permissions. It demonstrates temporal consistency in a complete local dataset. A real integration still needs authentication, API limits, error handling, omitted data and service behavior to be addressed and validated separately.
8. Hand over a report another team can explain
Prepare a small reporting contract: question, population, states, exclusions, UTC instant, identity, query definition, retrieved fields and missing-data rule. For a query chart on a dashboard, confirm a flat source stored in Shared Queries; check scope after converting a tree. Validate viewing with a representative recipient through authorized access. In the committee, distinguish facts, limitations and the proposed decision. If the historical basis is wrong, correct it or present the limitation and defer the decision that depends on it. In the handover summary, record who maintains the report and how to recognize incomplete results. The central conclusion is simple: a credible count requires knowing which items were selected and the time represented by displayed values, together with understanding what the count does not measure.
# Fictional local revision model. Not a WIQL parser or an Azure permission/API test.
from datetime import datetime
from collections import Counter
def instant(value):
if not value.endswith("Z"):
raise ValueError("This exercise requires an explicit UTC Z timestamp")
return datetime.fromisoformat(value.replace("Z", "+00:00"))
history = {
501: [("2026-09-10T09:00:00Z", "Active", "Rita"),
("2026-10-02T09:00:00Z", "Resolved", "Luis")],
502: [("2026-09-12T09:00:00Z", "Active", "Rita")],
503: [("2026-10-01T09:00:00Z", "Active", "Luis")],
504: [("2026-09-10T09:00:00Z", "New", "Luis"),
("2026-09-30T18:00:01Z", "Active", "Luis")],
}
def snapshot(when):
result = {}
for item_id, revisions in history.items:
eligible = [r for r in revisions if instant(r[0]) <= instant(when)]
if eligible:
revision = max(eligible, key=lambda r: instant(r[0]))
result[item_id] = {"state": revision[1], "owner": revision[2]}
return result
def hydrate(ids, when):
data = snapshot(when)
return {item_id: data[item_id] for item_id in ids} # Missing IDs raise, never silently omitted.
def owners(data):
return Counter(row["owner"] for row in data.values)
cutoff = "2026-09-30T18:00:00Z"
now = "2026-10-05T09:00:00Z"
at_close = snapshot(cutoff)
selected = [i for i, row in at_close.items if row["state"] == "Active"]
correct = hydrate(selected, cutoff)
mixed = hydrate(selected, now)
assert selected == [501, 502]
assert 503 not in at_close
assert at_close[504]["state"] == "New"
assert snapshot("2026-09-30T18:00:01Z")[504]["state"] == "Active"
assert correct[501]["state"] == "Active"
assert mixed[501]["state"] == "Resolved"
assert correct[501]["owner"] == "Rita"
assert mixed[501]["owner"] == "Luis"
assert len(correct) == len(mixed) == 2
assert set(correct) == set(mixed)
assert owners(correct) == {"Rita": 2}
assert owners(mixed) == {"Rita": 1, "Luis": 1}
assert correct!= mixed
assert hydrate(selected, cutoff) == correct
try:
hydrate([503], cutoff)
except KeyError:
missing_rejected = True
else:
missing_rejected = False
assert missing_rejected
try:
instant("2026-09-30T18:00:00")
except ValueError:
ambiguous_rejected = True
else:
ambiguous_rejected = False
assert ambiguous_rejected
print("16 fictional historical-report checks passed")
The model selects two tickets at closing. Retrieving the same IDs in October keeps two rows but displays a different state and owner distribution.
Common pitfalls
Filtering ChangedDate instead of reconstructing state; mixing historical IDs with current fields; confusing @Me with the author; assuming dashboard access resolves query access.
Related topics: Traceability and flow metrics · Permissions and separation of duties · Capacity planning · Transition to operational support
Preserve scope, identity and instant across selection, retrieval and presentation. Reconcile missing rows and explain limitations before using the report for decisions.
Reference: Work Item Query Language syntax reference · AZ-400 objectives 2026-07-27