Issue to translate a query from SQL to Appian

Question

Hello,

I have a problem creating a query in Appian, which was previously created in SQL.

Basically, we have 2 tables, the main one with the CASE numbers and the status, and another one where we record the historical movements of those cases:

Main Table : CASE
IDCase	Status
1	Active
2	Active
3	Active
4	Close
5	Active

Secondary Table: HistoryCase
IDHISTORY	IDCASE	LogText	CreatedDT
1	1	Created	11/23/2024 10:34
2	1	Updated	11/23/2024 10:34
3	1	Assigned	11/23/2024 10:34
4	1	Updated	11/23/2024 10:34
5	1	Closed	11/23/2024 10:34
6	2	Created	11/23/2024 10:34
7	2	Updated	11/23/2024 10:34
8	2	Assigned	11/23/2024 10:34
9	2	Updated	11/23/2024 10:34
10	2	Updated	11/23/2024 10:34
11	2	Updated	11/23/2024 10:34
12	2	Closed	11/23/2024 10:34
13	3	Created	11/23/2024 10:34
14	3	Updated	11/23/2024 10:34
15	3	Closed	11/23/2024 10:34

Our SQL query has subqueries, where we look for the last "Update" of each "CASE", so one we have the latest we applied another filter for a range dates:

Select *
from
(
    SELECT IDCase, CONVERT(Date,MAX(CreatedDT)) as 'CreatedDT'
    from
    (
        select A.IDCase as 'Case', A.CreatedDT as 'CreatedDT', A.LogText as 'LogText', A.IDHISTORY as 'IDHISTORY', B.Status as 'Status',
        
        FROM [dbo].[HistoryCase] as A
        join [dbo].[CASE] as 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

This is how we translated in Appian:

a!queryRecordType(
          recordType: 'recordType!HistoryCase',
          fields: a!aggregationFields(
            groupings: {
              a!grouping(
                field: 'recordType!HistoryCase.IDCase',
                alias: "IDCaseGroup"
              )
            },
            measures: {
              a!measure(
                function: "MAX",
                field: 'recordType!HistoryCase.CreatedDT',
                alias: "CreatedDT_max_measure"
              )
            }
          ),
          filters: a!queryLogicalExpression(
          operator: "AND",
          logicalExpressions: a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: 'recordType!HistoryCase.CreatedDT',
                operator: "between",
                value: {
                  todatetime(ri!fromCreatedDT),
                  todatetime(ri!toCreatedDT) + 1
                }
              )
            },
            ignoreFiltersWithEmptyValues: true
          ),
          filters: {
            a!queryFilter(
              field: 'recordType!HistoryCase.LogText',
              operator: "starts with",
              value: "Updated"
            )
          },
          ignoreFiltersWithEmptyValues: true
        )
          pagingInfo: a!pagingInfo(
            startIndex: 1,
            batchSize: cons!CEF_RT_MAX_LIMIT_BATCHSIZE
          )
)

Is there a way to apply the filters in that order in a queryRecordType?
Somehow, sometimes the date range filter is applied first and then the other filters, since we want it to be applied on the MAX Measure result.

or any way to translate the SQL query we use to Appian.

naveenkumar11800 · Answer

ceciliaa9949 let me try and summarize what i understood

You have two tables: 
 
 Main Table (CASE): Contains IDCase and Status for cases. 
 Secondary Table (HistoryCase): Stores the historical movements ( LogText , CreatedDT ) for each case.

The requirement: 
 
 Find the last "Update" ( LogText = 'Updated' ) for each case. 
 Exclude cases with Status = 'Close' . 
 Apply a date range filter on the CreatedDT field (e.g., from 2024-11-01 to 2024-11-30). 
 Order the results by IDCase .

Current SQL solution: 
 
 A subquery identifies the latest update for each case. 
 A date range filter is applied on the CreatedDT field in the final result.

Here&rsquo;s how to translate the SQL query into Appian:

Record Type Setup: Ensure that HistoryCase is a record type with a relationship to the CASE record type.

Appian Query for Aggregation: follows next message <CODE>

Explanation of the Solution: 
 
 Aggregation Fields: Group by IDCase to aggregate data for each case. 
 Measure: Use the MAX function to fetch the latest CreatedDT for each case. 
 Filters: 
 
 Only include rows where LogText starts with "Updated." 
 Exclude cases with Status = 'Close' using a filter on the CASE record type. 
 Filter the results to a specific date range using CreatedDT .

Paging: Adjust the batchSize as needed.

naveenkumar11800 · Answer

a!queryRecordType(
 recordType: 'recordType!HistoryCase', /* Replace with your record type */
 fields: {
 a!aggregationFields(
 groupings: {
 a!grouping(
 field: 'recordType!HistoryCase.IDCase',
 alias: "IDCaseGroup"
 )
 },
 measures: {
 a!measure(
 function: "MAX",
 field: 'recordType!HistoryCase.CreatedDT',
 alias: "LatestUpdate"
 )
 }
 )
 },
 filters: a!queryLogicalExpression(
 operator: "AND",
 filters: {
 a!queryFilter(
 field: 'recordType!HistoryCase.LogText',
 operator: "starts with",
 value: "Updated"
 ),
 a!queryFilter(
 field: 'recordType!CASE.Status',
 operator: "<>",
 value: "Close"
 ),
 a!queryFilter(
 field: 'recordType!HistoryCase.CreatedDT',
 operator: "between",
 value: {
 todatetime(ri!fromDate),
 todatetime(ri!toDate) + 1 /* Include the end date */
 }
 )
 },
 ignoreFiltersWithEmptyValues: true
 ),
 pagingInfo: a!pagingInfo(
 startIndex: 1,
 batchSize: cons!DEFAULT_BATCH_SIZE
 )
)