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