Oracle query Need data in daily wise for weekly report

chandra_1986

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

Paulzip

Really? This is fundamental date arithmetic. Are you new to Oracle?

Note :

• Timestamp is a reserved word, I suggest you call your column something else
• Your current date filtering will prohibit index usage if there are indexes on the date columns, my example won't

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
         a.timestamp >= trunc(sysdate - 7) and a.timestamp < trunc(sysdate) and
         a.attuid = b.attuid and
         b.loginlogouttime >= trunc(sysdate - 7) and b.loginlogouttime < trunc(sysdate)
  group by b.agentrole, to_char(trunc(a.timestamp), 'MM/DD/YY')
)
group by timestamp

You can replace the 7 in trunc(sysdate - 7) with however many days ago you want.

Paulzip

Really? This is fundamental date arithmetic. Are you new to Oracle?

Note :

• Timestamp is a reserved word, I suggest you call your column something else
• Your current date filtering will prohibit index usage if there are indexes on the date columns, my example won't

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
         a.timestamp >= trunc(sysdate - 7) and a.timestamp < trunc(sysdate) and
         a.attuid = b.attuid and
         b.loginlogouttime >= trunc(sysdate - 7) and b.loginlogouttime < trunc(sysdate)
  group by b.agentrole, to_char(trunc(a.timestamp), 'MM/DD/YY')
)
group by timestamp

You can replace the 7 in trunc(sysdate - 7) with however many days ago you want.

Frank Kulash

Hi, @chandra_1986

I hope the reply above answers your question. If not, post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that sample data at a given run time. Include examples of any special cases you need to handle. For example, if you care counting distinct jobids, what do you want when the same jobid occurs on two different day? Always post your full Oracle version (e.g. 12.2.0.1.0).