Issue to translate a query from SQL to Appian

 ceciliaa9949  let me try and summarize what i understood

1. 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.
     
     

2. 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.
     
     

3. 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’s how to translate the SQL query into Appian:

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

2. 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.

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
  )
)

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
  )
)

Hello, thank you for your response.

The problem persist cause the Filters in Appian applied at the same time, For example:

Have this Cade ID and we want the last date with "LogText"= "Updated", that should be 10/30/2024:

So for example if the Filter for the dates are From 10-14-2024 To 10-14-2024 or From 10-01-2024 To 10-29-2024, it'll display the IDHistory "499462" (the orange one).

But if the Filter for the dates are From 10-30-2024 To 10-30-2024 or From 10-01-2024 To 10-31-2024, it'll display the IDHistory "1267064" (the green one). 


That's why I was looking for a way, that the Filter of the dates be apply on the result of the first filters, not at the same time as the others.