How to write subquery to get the same fields from undetermined tables dynamically

admin

4 years ago

SELECT SUM(t.orgBilledDuration/60) AS Duration
FROM 
(SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_631
     WHERE FROM_UNIXTIME(connectTime/1000, '%Y-%m-%d %H:%i:%s') BETWEEN '2021-10-20 00:00:00' AND '2021-12-05 23:59:59'
 UNION ALL
 SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_632
     WHERE FROM_UNIXTIME(connectTime/1000, '%Y-%m-%d %H:%i:%s') BETWEEN '2021-10-20 00:00:00' AND '2021-12-05 23:59:59'
 UNION ALL
 SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_633
     WHERE FROM_UNIXTIME(connectTime/1000, '%Y-%m-%d %H:%i:%s') BETWEEN '2021-10-20 00:00:00' AND '2021-12-05 23:59:59'
) t

From the above SQL, I tried to write the following query to select the same fields from undetermined tables dynamically. Following query gives the result from one table dynamically but need to write the above subquery dynamically. Can anyone suggest on this?

Each nested select statement is for month wise tables. Need to search from different month’s tables

SET @tab_id:= 631;
SET @tab:= concat('Successfuliptsp.vbSuccessfulCDR_',@tab_id);
SET @ID_1:= 'FROM_UNIXTIME(connectTime/1000, ''%Y-%m-%d %H:%i:%s'') BETWEEN ''2021-11-25 00:00:00'' AND ''2021-11-30 23:59:59''';
SET @s = CONCAT('select SUM(orgBilledDuration)/60 from ', @tab, ' where ', @ID_1); 
PREPARE STMT1 FROM @s; 
EXECUTE STMT1; 
DEALLOCATE PREPARE STMT1;