Forum Stats

  • 3,769,695 Users
  • 2,253,008 Discussions
  • 7,875,155 Comments

Discussions

Oracle query Need data in daily wise for weekly report

chandra_1986
chandra_1986 Member Posts: 285 Blue Ribbon

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

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 21, 2021 10:43AM Accepted Answer

    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.

    chandra_1986

Answers

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 21, 2021 10:43AM Accepted Answer

    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.

    chandra_1986
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    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).

    chandra_1986