Multiple counts on same table
Posted: Tue Dec 06, 2011 2:16 pm
I had a question via PM. that's not what PM is for.
rekhamf wrote:Hi ,
I have 55 querys similar like this in my pgm to get different count from the table , is that possible to comibine the query in to single dml to improve effeciency ?rekhamfEXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-3WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 18 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '2'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC
EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-2WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 17 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 11 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '4'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC
EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-1WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 10 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '5'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC