Wednesday, June 2, 2010

Oracle query to display all the dates in a month or date ranges

In some requirements we have to show all the dates between the date range though a particular date entry is not  present in the DB. We can not acheive it in the crystal report straight forwardly. The only the way is writting query to bring all the dates in the data ranges and make left outer join with your core resultset by joining the dates.

For an example we need to find day wise count of particular entity,

SELECT
         TEMP.TEMP_DATE,
         (
           SELECT COUNT(ENTITY.ID) FROM
                (SELECT ENTITY.ID ....... YOUR CORE CONDITIONS ) INLINEVIEW  
           WHERE INLINEVIEW.DATE(+)=TEMP.TEMP_DATE
        )ENTITY_COUNT
FROM
  ( SELECT ({?FROMDATE}-1)+ LEVEL TEMP_DATE
     FROM DUAL CONNECT BY LEVEL <= ({?TODATE})-({FROMDATE}-1)  )TEMP
 GROUP BY TEMP.TEMP_DATE
 ORDER BY TEMP.TEMP_DATE

No comments:

Post a Comment