HI experts,
Below is the query am using for previous day data(sysydate-1)
How can we modify below query to give output for weekly data ( suppose from 15th Aug to 21st Aug) daily wise count.
output expected like:
15th Aug --- count value
16th Aug --- count value
........21st Aug---count value
for weekly report day wise data.
SELECT TIMESTAMP,
SUM(TOTAL_TFS_COUNT) AS TOTAL_TFS_COUNT
FROM
(SELECT TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY') AS TIMESTAMP,
COUNT(UNIQUE A.JOBID) AS TOTAL_TFS_COUNT
FROM ATLAS_AGENTACT_PROD A,
ATLAS_AGENTLOGIN B
WHERE A.EVENTID IN ('ATLAS020')
AND B.AGENTROLE IN ('TFS_SW_TECH','TFS_SE_TECH','TFS_W_TECH','TFS_MW_TECH')
AND TRUNC(A.TIMESTAMP) = TRUNC(SYSDATE-1)
AND A.ATTUID =B.ATTUID
AND TRUNC(B.LOGINLOGOUTTIME) = TRUNC(SYSDATE-1)
GROUP BY B.AGENTROLE,
TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY')
)
GROUP BY TIMESTAMP