Question
· Apr 11

Dynamically Generating SQL queries Based on Incremental Column values

I'm facing a challenge in dynamically generating SQL queries based on incremental q_ids for a project I'm working on. Here's the scenario:

  • I have a table log_reports that contains logs of service activities, including timestamps and associated q_ids.
  • Each service log entry is associated with a cls and a q.
  • My goal is to generate SQL queries that calculate statistics such as average, minimum, and maximum time differences between the log creation time and the current timestamp, for each combination of cls_id and q_id.

Current Approach:

Here's the SQL query I'm currently using:

SELECT
    LPAD(FLOOR(AVG(DATEDIFF(HOUR, log.created_at, CURRENT_TIMESTAMP))), 2, '0') || ':' ||
    LPAD(FLOOR(AVG(DATEDIFF(MINUTE, log.created_at, CURRENT_TIMESTAMP))), 2, '0') || ':' ||
    LPAD(FLOOR(AVG(DATEDIFF(SECOND, log.created_at, CURRENT_TIMESTAMP))), 2, '0') AS average_time_difference, 
    
    LPAD(FLOOR(MIN(DATEDIFF(HOUR, log.created_at, CURRENT_TIMESTAMP))), 2, '0') || ':' ||
    LPAD(FLOOR(MIN(DATEDIFF(MINUTE, log.created_at, CURRENT_TIMESTAMP))), 2, '0') || ':' ||
    LPAD(FLOOR(MIN(DATEDIFF(SECOND, log.created_at, CURRENT_TIMESTAMP))), 2, '0') AS min_time_difference,
    
    LPAD(FLOOR(MAX(DATEDIFF(HOUR, log.created_at, CURRENT_TIMESTAMP))), 2, '0') || ':' ||
    LPAD(FLOOR(MAX(DATEDIFF(MINUTE, log.created_at, CURRENT_TIMESTAMP))), 2, '0') || ':' ||
    LPAD(FLOOR(MAX(DATEDIFF(SECOND, log.created_at, CURRENT_TIMESTAMP))), 2, '0') AS max_time_difference
FROM
    log_reports log  
LEFT JOIN
    pass_slip slip ON log.tkt = slip.id  
WHERE
    log.cls_id IN (61)
    AND log.q_id IN (19, 25, 27)
    AND slip.status IS NOT NULL
GROUP BY
     log.cls_id

 

This query works well for a fixed set of q_ids. However, I need to dynamically generate the query to include all q_id up to a certain point.

Expected Solution:

I'm looking for suggestions on how to dynamically generate the SQL query to include q_ids incrementally, such that:

  1. Initially, the query should consider only the first q_id.
  2. In subsequent executions, the query should include an additional q_id until all q_id are covered.

Additional Context:

  • I'm using IRIS Database, which does not support common table expressions (CTEs) or native looping constructs within SQL queries.
  • The solution can involve either SQL techniques.

Questions:

  1. How can I dynamically generate SQL queries to include incremental q_ids without needing to execute the query multiple times?
  2. Are there any best practices or alternative approaches I should consider for this scenario?
  3. What scripting languages or techniques can I use to achieve this dynamic query generation if SQL alone isn't sufficient?

Any insights, code examples, or pointers to relevant resources would be greatly appreciated!

Thank you!

Discussion (8)3
Log in or sign up to continue

I realize my previous request may have been confusing. Let me clarify my question.

For the current approach, I need to run the query each time by incrementing the column (queue_id) value. But I'm trying to run the query once, which should dynamically increment the column values. For sample Output I am expected:

quantiy_of_queue average_time_difference min_time_difference max_time_difference
1 05:25:15 01:25:15 06:26:15
2 06:25:15 02:25:15 07:26:15
3 06:20:15 01:25:15 07:30:15
4 04:25:15 02:25:15 06:40:15  

Hai @Ashok Kumar

Thank you for your response. However, my current approach involves hitting the database every time in order to obtain the desired results. Given that I am unsure of the number of q_ids that will be involved in the future, I am concerned about the performance impact. Therefore, I am exploring alternative approaches to address this issue.

Kindly let me know if there are any other approaches such as stored procedures or minimalistic database hits that can help me achieve my expected outcome.

Still not fully able to grasp what you are doing but if are trying to address 

log.cls_id IN (61) AND log.q_id IN (19, 25, 27)

I often find myself doing something like

log.cls_id %INLIST $LISTFROMSTRING(':Param1,',')

AND log.q_id %INLIST $LISTFROMSTRING(':Param2,',')

and then set Param1=61 and Param2 = "19, 25, 27"

so long as you know the values of Param1 and Param2. 

%INLIST is documented https://docs.intersystems.com/iris20241/csp/docbook/Doc.View.cls?KEY=RSQ...

and

$LISTFROMSTRING is documented here https://docs.intersystems.com/iris20241/csp/docbook/Doc.View.cls?KEY=RSQ...