portal activity report query

[Former Member]

Hi,

We have a customized portal activity report in which there is a SQL Query "

select to_char((to_date('01-JAN-1970','DD-MON-YYYY')(a.TIMESTAMPHOUR/86400000)),'DY') as TIMESTAMPDAY, to_char((to_date('01-JAN-1970','DD-MON-YYYY')(a.TIMESTAMPHOUR/86400000)))||' (GMT)' as TIMESTAMPHOUR, decode(a.OBJECTTYPE,'i','iView','p','Page') as pageTYPE, a.CUSTOM as OBJECTName , a.VISITS as hits , a.IMPRESSIONS as IMPRESSIONS, b.LOGONID as ID, a.PCDURL as PCDURL from WCR_WEBCONTENTSTAT a ,WCR_USERPAGEUSAGE b where ( PCDURL = 'pcd:portal_content/com.freescale.SUPO_Roles/com.freescale.sup_rol_armanager/com.freescale.sup_wks_0005/com.freescale.sup_wks_0001/com.freescale.sup_pge_0003' or PCDURL = 'pcd:portal_content/com.freescale.SUPO_Roles/com.freescale.sup_rol_bidder/com.freescale.sup_wks_0006/com.freescale.sup_pge_0011') and a.ID = b.HOURLY_ID and AGGREGATIONLEVEL= 'h' and a.TIMESTAMPHOUR > (select ((to_date('01-JAN-2008','DD-MON-YYYY')-(to_date('01-JAN-1970','DD-MON-YYYY'))))*86400000 from DUAL) "

I need to modify this quey for taking a time duration of 3 months till date period .Is it possible / how to debug this query? Can anyone please help me in this

[former_member194668 · Accepted solution]

Hi Varsha,

The query that you gave returns the hits of the sepcified url after Jan 1st 2008.

I didn't understand your requirement...

Whether you want last three months hits from the current(today's date)? or you want to specify the end date and three months before it? or you want to sepcify the date range (both from and to)?

-Aarthi

[Former Member]

I could use this query but when i give some start date and end date say:-start date as 16-JAN-2008 and end date as 16-MAR-2008 is does not show me data .

but if i give start date as 02-SEP-2008 and end date as 16-SEP-2008 I get the data in report .

Also aggregation level set for portal activity report is:-

First aggregation 2 SEP 2008 and

Last aggregation 29-SEP 2008 .

Could you please let me know how can i get data of the previous months .I need data for every 3 months.Is some settings required in portal activity report configuration

Thankxs in Advance

-Varsha

[Former Member]

Hi Aarthi,

I want to specify the date range. Could you please help me how can I change the query to incoporate a date range.

Thankxs in Advance

[former_member194668]

I dunno how will you be able to get the date range inside the query. Meaning, dynamic from and to dates which vary each time. I'm assuming that start_date and end_date are the parameters that you will be able to get them to the query. With the assumption, the query will look like this:

select to_char((to_date('01-JAN-1970','DD-MON-YYYY')+(a.TIMESTAMPHOUR/86400000)),'DY') as TIMESTAMPDAY, to_char((to_date('01-JAN-1970','DD-MON-YYYY')+(a.TIMESTAMPHOUR/86400000)))||' (GMT)' as TIMESTAMPHOUR, decode(a.OBJECTTYPE,'i','iView','p','Page') as pageTYPE, a.CUSTOM as OBJECTName , a.VISITS as hits , a.IMPRESSIONS as IMPRESSIONS, b.LOGONID as ID, a.PCDURL as PCDURL from WCR_WEBCONTENTSTAT a ,WCR_USERPAGEUSAGE b where ( PCDURL = 'pcd:portal_content/com.freescale.SUPO_Roles/com.freescale.sup_rol_armanager/com.freescale.sup_wks_0005/com.freescale.sup_wks_0001/com.freescale.sup_pge_0003' or PCDURL = 'pcd:portal_content/com.freescale.SUPO_Roles/com.freescale.sup_rol_bidder/com.freescale.sup_wks_0006/com.freescale.sup_pge_0011') and a.ID = b.HOURLY_ID and AGGREGATIONLEVEL= 'h' and a.TIMESTAMPHOUR > (select ((to_date(start_date,'DD-MON-YYYY')-(to_date('01-JAN-1970','DD-MON-YYYY'))))*86400000 from DUAL) and a.TIMESTAMPHOUR < (select ((to_date(end_date,'DD-MON-YYYY')-(to_date('01-JAN-1970','DD-MON-YYYY'))))*86400000 from DUAL) "

-Aarthi