Skip to main content
February 6, 2024
Solved

Filter data between filter of two dates

  • February 6, 2024
  • 10 replies
  • 0 views

a!localVariables(
  local!date: todatetime(ri!startTime, ri!endTime),
  local!pId: a!queryEntity(
    entity:cons!DS_DATA,
    query:
    a!query(
      selection:
        a!querySelection(
          columns: {
            a!queryColumn(
              field:"pId",
              alias:"pId"
            )
          }
        ),
      logicalExpression: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field:"startTime",
              operator: ">=",
              value : local!date
            ),
            a!queryFilter(
              field:"endTime",
              operator: "<=",
              value : local!date
            ),
            
          }
        )
      },
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).data,

Best answer by herver5127

a!localVariables(
 local!startDate: todatetime(ri!startTime),
  local!endDate todatetime(ri!endTime),
  local!pId: a!queryEntity(
    entity:cons!DS_DATA,
    query:
    a!query(
      selection:
        a!querySelection(
          columns: {
            a!queryColumn(
              field:"pId",
              alias:"pId"
            )
          }
        ),
      logicalExpression: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field:"startTime",
              operator: ">=",
              value : local!startDate
            ),
            a!queryFilter(
              field:"startTime",
              operator: "<=",
              value : local!endDate
            ),
            
          }
        )
      },
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).data,
  local!pIdR:index(
    local!pId,
    "pId",
    {}
  ),

10 replies

konduruc733182
February 6, 2024

Hello [mention:60209c2a71304231ab861500c2c17107:e9ed411860ed4f2ba0265705b8793d05] 
What's your question? Please share some information.

February 6, 2024

Hello,

The question is how to filter a data, "id" here, between two dates with the code above

konduruc733182
February 6, 2024

Changed a little bit. Can you try out this and check if this is what you are looking for

a!localVariables(
  local!data: a!queryEntity(
    entity:cons!DS_DATA,
    query: a!query(
      logicalExpression: {
        a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field:"startTime",
              operator: ">=",
              value : todatetime(ri!startDate),
              applyWhen: a!isNotNullOrEmpty(ri!startDate)
            ),
            a!queryFilter(
              field:"endtime",
              operator: "<=",
              value : todatetime(ri!startDate),
              applyWhen: a!isNotNullOrEmpty(ri!startDate)
            ),

          }
        )
      },
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: -1
      )
    ),
    fetchTotalCount: true
  ).data,
  index(
    local!data,
    "pId",
    null
  )
)