Display zero values

sairag

Hi I am working on report which will display the count for 8 quarters. When I am creating data set I am getting the count for the records that are available. The results are as follows.

QUARTER_START_DATE CNT

---

---
Oct-11 1
Jul-12 34
Oct-12 3
Jul-13 1

I am using the following query:

SELECT
TO_CHAR((TRUNC ("TARGET-DEPLOYMENT-DATE",'Q')+93),'MM-YYYY') AS quarter_start_date ,COUNT (ASSETURN) AS cnt
FROM ERRUSER.RPT_CLASSIFIERS_36
WHERE
"TARGET-DEPLOYMENT-DATE" >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
AND "TARGET-DEPLOYMENT-DATE" < ADD_MONTHS ( TRUNC (SYSDATE, 'Q'),15)
GROUP BY (TRUNC ("TARGET-DEPLOYMENT-DATE" ,'Q'))
ORDER BY TRUNC ("TARGET-DEPLOYMENT-DATE" , 'Q')

Can u suggest me how can I achieve the following result:


QUARTER_START_DATE CNT

---

---
Q3 - 2011 0
Q4 - 2011 1
Q1 - 2012 0
Q2 - 2012 34
Q3 - 2012 0
Q4 - 2012 3
Q1 - 2013 0
Q2 - 2013 1


Thanks in advance,
AR

mwilliams

If you had a list of the quarters you wanted, either from a static file or created with a scripted set based on a start and end date, you could embed a table bound to your actual data set into a table bound to your set that contains all possible quarters. You'd just limit the inside table with a filter or dataSet parameter. Then, you'd be guaranteed to have all the quarters show. You could also join your two dataSets, instead of embedding tables.

Hope this helps.