Translating SQL Query with Subqueries into Appian QueryRecordType for Data Aggregation

Hi Appian community! [emoticon:b7bcfd5903f14440addb24344907da6e]

I’m a recently certified Associate Developer, and I’ve been working on a scenario that I’d love your thoughts on. It involves translating SQL logic into Appian to make the most of a!queryRecordType.

Here’s the use case:

I have two tables:

1. Main Table (CASE): Contains the case ID (IDCase) and its current Status.
2. Secondary Table (HistoryCase): Logs all historical movements (LogText, CreatedDT) for each case.

Goal:

1. Find the last "Update" (LogText = 'Updated') for each case.
2. Exclude cases with Status = 'Close'.
3. Only include updates within a specific date range (e.g., 2024-11-01 to 2024-11-30).
4. Order results by IDCase.

Here’s the original SQL query I’m trying to translate:

SELECT *
FROM
(
    SELECT IDCase, CONVERT(Date, MAX(CreatedDT)) as 'CreatedDT'
    FROM
    (
        SELECT A.IDCase, A.CreatedDT, A.LogText, A.IDHISTORY, B.Status
        FROM [dbo].[HistoryCase] A
        JOIN [dbo].[CASE] B ON A.IDCase = B.IDCase
        WHERE A.IDCase IS NOT NULL 
          AND A.LogText LIKE 'Updated%' 
          AND B.Status <> 'Close'
    ) AS Result
    GROUP BY IDCase
) AS FinalResult
WHERE CreatedDT >= CONVERT(datetime, '2024-11-01') 
  AND CreatedDT <= CONVERT(datetime, '2024-11-30')
ORDER BY IDCase;