Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle query Need data in daily wise for weekly report

chandra_1986Aug 21 2021

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
This post has been answered by Paulzip on Aug 21 2021
Jump to Answer

Comments

cormaco

Please post your example XML as actual text.

User_2K4XP

Hi,
Below is the xml as actual text.

<Party>
<ID>1234</ID>
<Type>Organisation</Type>
<Entity>ABCD</Entity>
<OrgType>Registered company</OrgType>
<PartyId>
<Design>7890</Design>
<Name>ACN</Name>
</PartyId>
<TRDetail>
<TRIndicator>yes</TRIndicator>
<TRName>XYZ</TRName>
</TRDetail>
</Party>
<Party>
<ID>1234</ID>
<Type>Organisation</Type>
<Entity>ABCD</Entity>
<OrgType>Registered company</OrgType>
<PartyId>
<Design>7890</Design>
<Name>ACN</Name>
</PartyId>
<TRDetail>
<TRIndicator>yes</TRIndicator>
<TRName>XYZ</TRName>
</TRDetail>
</Party>

Thanks !

cormaco

Here is one way:
It is necessary to use XMLPARSE because your example is an XML fragment, not a complete XML file.

with example(xmlfile) as (
select xmlparse(content
'<Party>
	<ID>1234</ID>
	<Type>Organisation</Type>
	<Entity>ABCD</Entity>
	<OrgType>Registered company</OrgType>
	<PartyId>
		<Design>7890</Design>
		<Name>ACN</Name>
	</PartyId>
	<TRDetail>
		<TRIndicator>yes</TRIndicator>
		<TRName>XYZ</TRName>
	</TRDetail>
</Party>
<Party>
	<ID>1234</ID>
	<Type>Organisation</Type>
	<Entity>ABCD</Entity>
	<OrgType>Registered company</OrgType>
	<PartyId>
		<Design>7890</Design>
		<Name>ACN</Name>
	</PartyId>
	<TRDetail>
		<TRIndicator>yes</TRIndicator>
		<TRName>XYZ</TRName>
	</TRDetail>
</Party>') from dual)
select distinct x.* 
from example,xmltable(
    '/Party/TRDetail'
    passing xmlfile
    columns
        trindicator varchar2(3)  path 'TRIndicator',
        trname      varchar2(20) path 'TRName'
) x

TRINDICATOR TRNAME              
----------- --------------------
yes         XYZ                 
1 row selected.
1 person found this helpful
1 - 3

Post Details

Added on Aug 21 2021
#plsql, #sqldeveloper
2 comments
1,234 views
1 mention