CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
panza
panza

Reputation: 1431

Automatically updating the timestamp values in User Defined Reports (SQL Developer)

I have defined an User Defined Report in Oracle SQL Developer with the following query to be run monthly:

SELECT TO_CHAR(SUMMARY_TIMESTAMP,'YYYY-MM-DD') AS day, Count(ID) AS row_count
FROM S_SEARCH_SUMMARIES 
WHERE SUMMARY_TIMESTAMP > '01-Nov-13' AND SUMMARY_TIMESTAMP < '01-Dec-13' 
AND SOURCE_INSTITUTION = 'My Institution' 
GROUP BY TO_CHAR(SUMMARY_TIMESTAMP,'YYYY-MM-DD')
ORDER BY day DESC

Is there any way to automatically update the SUMMARY_TIMESTAMP on the basis of the current month, instead of manually changing it?

Upvotes: 0

Views: 151

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

Maybe there is a cleaner way to do this, but one thought is to add something like this to your query:

    WITH dates
     AS (SELECT TO_DATE (
                   EXTRACT (YEAR FROM SYSDATE) || '-' || EXTRACT (MONTH FROM
                   SYSDATE) || '-01',
                   'YYYY-MM-DD')
                   DT_END,
                TO_DATE ( (EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -1))) ||
                         '-' || EXTRACT (MONTH FROM ADD_MONTHS (SYSDATE, -1))
                         || '-01',
                         'YY-MM-DD')
                   DT_START
           FROM DUAL)
  SELECT TO_CHAR (SUMMARY_TIMESTAMP, 'YYYY-MM-DD') AS day,
         COUNT (ID) AS row_count
    FROM S_SEARCH_SUMMARIES, dates
   WHERE     SUMMARY_TIMESTAMP > dates.DT_START
         AND SUMMARY_TIMESTAMP < dates.DT_END
         AND SOURCE_INSTITUTION = 'My Institution'
GROUP BY TO_CHAR (SUMMARY_TIMESTAMP, 'YYYY-MM-DD')
ORDER BY day DESC

Upvotes: 1

Related Questions