How to Build a Patient Transaction Running Total in T-SQL for DP-800?
HOTSPOT
Case Study
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
Existing Environment
Azure Environment
Fabrikam has a single Azure subscription in the East US 2 Azure region. The subscription contains an Azure SQL database named DB1. DB1 contains the following tables:
• Patients
• Employees
• Procedures
• Transactions
• UsefulPrompts
• ProcedureDocuments
You store a column master key as a secret in Azure Key Vault.
You have an on-premises application named TransactionProcessing that uses a hard-coded username and password in a connection string to access DB1.
Problem Statements
Users report that after executing a long-running stored procedure named sp_UpdateProcedureForPatient, updates to the underlying data are sometimes inconsistent.
Requirements
Planned Changes
Fabrikam plans to manage all changes to Azure SQL Database objects by using source control in GitHub. Every pull request submitted to production will be validated before it can be merged. Deployments must use the Release configuration.
Security Requirements
Fabrikam identifies the following security requirements:
• The TransactionProcessing application must use a passwordless connection to DB1.
• The Employees table contains two columns named TaxID and Salary that must be encrypted at rest.
• Auditors must have a tamper-evident history of transactions with cryptographic proof of changes to the employee data.
Database Performance Requirements
Records accessed by using sp_UpdateProcedureForPatient must NOT be changed by other transactions while the stored procedure runs.
AI Search, Embeddings, and Vector Indexing
Fabrikam identifies the following AI-related requirements:
• Queries to the ProcedureDocuments table must use Reciprocal Rank Fusion (RRF).
• Users must be able to query the data in DB1 by using prompts in Copilot in Microsoft Fabric.
• The UsefulPrompts table will store prompts that doctors can use to help diagnose patient illness by connecting to an Azure OpenAI endpoint.
Development Requirements
Fabrikam identifies the following development requirements:
• Provide the functionality to retrieve all the transactions of a given patient between two dates, showing a running total.
• Expose a Data API builder (DAB) configuration file to enable Azure services to perform the following operations over a REST API:
o Read data from the procedures table without authentication.
o Read and insert data into the Transactions table once authenticated.
o Execute the sp_UpdateProcedurePatient stored procedure.
• Provide the functionality to retrieve a list of the names of patients who underwent medical procedures during the last 30 days.
• Information for each medical procedure will be stored in a table. The table will be used with a large language model (LLM) for user querying and will have the following structure.
DAB
You create a DAB configuration file that meets the development requirements for DB1 and includes the following entities.
You need to create a solution that meets the development requirements for retrieving the patient transaction data. The solution must ensure that database developers use the resulting data to join to other tables.
How should you complete the Transact-SQL code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Community Insight
Tests whether you can express a deterministic running total with the windowed SUM plus an inline TVF wrapper, and the trap is relying on the default RANGE frame or omitting the tie-breaker, which silently repeats the same total for same-day transactions.
This DP-800 hotspot asks you to complete Transact-SQL that returns every transaction of a given patient between two dates with a running total, in a form other developers can join to. The correct completion is an inline table-valued function whose SUM() OVER uses PARTITION BY PatientId, ORDER BY TransactionDate plus TransactionId, and ROWS UNBOUNDED PRECEDING with a WHERE date filter.
Many candidates select a multi-statement table-valued function or a stored procedure, or write SUM() OVER (PARTITION BY PatientId ORDER BY TransactionDate) with no ROWS clause — the default RANGE frame sums every tied row, so two transactions on the same date produce identical (wrong) running totals and the output cannot be joined cleanly.
Community Discussion (1 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
The requirement says the patient transaction data must be returned so that "database developers use the resulting data to join to other tables," which points to an inline table-valued function: CREATE FUNCTION... RETURNS TABLE AS RETURN (SELECT...). An inline TVF is fully composable in a JOIN or APPLY, is inlined by the optimizer, and accepts the patient and date-range parameters needed for the "between two dates" filter. The running total itself must be a windowed aggregate — SUM(t.Amount) OVER (PARTITION BY t.PatientId ORDER BY t.TransactionDate, t.TransactionId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — because the PARTITION restarts the accumulator per patient, the ORDER BY defines the sequence, and the explicit ROWS frame guarantees each row adds exactly one preceding value. TransactionId is included as a tie-breaker so same-day transactions still accumulate one at a time and the result is deterministic. The WHERE clause on TransactionDate confines the output to the requested interval while the window still computes over the ordered rows returned.Why Other Candidate Answers Are Wrong
A stored procedure (CREATE PROCEDURE) cannot be used in a FROM/JOIN clause, so it fails the explicit joining requirement even though it can return a result set. A scalar function returns one value and cannot project the full transaction row set, and a multi-statement TVF with a table variable is a poorer choice than an inline TVF because it hides cardinality from the optimizer and cannot be inlined. Omitting the ROWS clause leaves the default RANGE UNBOUNDED PRECEDING AND CURRENT ROW frame, which aggregates all peer rows with the same TransactionDate into one value — the classic "running total jumps" defect. Dropping PARTITION BY (or partitioning by the wrong key) makes the running total continue across patients, and missing ORDER BY means the frame has no defined sequence at all.Community Comment Notes
As gangreja noted, the answer starts with "CREATE FUNCTION" and places "PARTITION BY" inside the OVER clause, with the second element of OVER being the date order because "A running total needs a time order: each row adds to the ones before it." The same comment stresses using TransactionId "as a tie-breaker" so that two transactions on the same date get a fixed order, making the total increase one row at a time and the result deterministic. Note that the comment refers to CustomerId, while this case study's schema names the key PatientId — the logic is identical, only the column name differs because the requirement is per patient.Official Reference
Exam Strategy
Read the phrase "join to other tables" as a hard signal for a table-valued function, and always prefer inline over multi-statement. Whenever a hotspot shows OVER (PARTITION BY ... ORDER BY ...), check whether a ROWS frame and a unique tie-breaker column are present before selecting.
Frequently Asked Questions
Why must the running total use ROWS UNBOUNDED PRECEDING instead of the default frame?
The default RANGE frame aggregates all rows tied on TransactionDate, so same-day transactions show the same total. ROWS UNBOUNDED PRECEDING adds one row at a time for a true running total.
Why use an inline table-valued function rather than a stored procedure for patient transactions?
An inline TVF returns a rowset that can be joined or CROSS APPLY-ed and is inlined by the optimizer. A stored procedure cannot be referenced in a FROM clause, so it fails the join requirement.