 Decode, NVL or which do I use? [message #184205] |
Tue, 25 July 2006 13:27  |
melsterDBA
Messages: 1
Registered: July 2006
|
Junior Member |
|
|
I have the following query:
SELECT TO_CHAR(reconcile_date,'yyyymm'),
COUNT(*),
200407
FROM TEMP_MMODRPT tg,
(SELECT per_id,
start_date,
MIN(reconcile_date)
FROM TEMP_MMODRPT
WHERE reconcile_date IS NOT NULL
AND billing_period = 'A'
GROUP BY per_id,
start_date
HAVING (MIN(reconcile_date) >= TO_DATE ('08/01/2004','mm/dd/yyyy')
AND MIN(reconcile_date)< TO_DATE('09/01/2004','mm/dd/yyyy'))
ORDER BY per_id, start_date) tm
WHERE tg.per_id = tm.per_id
AND tg.start_date = tm.start_date
AND tg.billing_period = 'A'
AND (reconcile_date >= TO_DATE('08/01/2004','mm/dd/yyyy')
AND reconcile_date < TO_DATE('07/01/2006','mm/dd/yyyy')
)
GROUP BY TO_CHAR(reconcile_date,'yyyymm');
The results look like this where the 1st col is a date, 2nd col is a COUNT(*), 3rd col is hardcoded header.
TO_CHA COUNT(*) 200407
------ ---------- ----------
200408 7593 200407
200508 4721 200407
200509 3 200407
200510 1 200407
200511 6 200407
200602 5 200407
200604 1 200407
200605 1 200407
I would like the results to look more like this, where if I have no record for the date, then put a count of 0 like so:
TO_CHA COUNT(*) 200407
------ ---------- ----------
200408 7593 200407
200409 0 200407
200410 0 200407
200411 0 200407
200412 0 200407
200501 0 200407
200502 0 200407
200503 0 200407
200504 0 200407
200505 0 200407
200506 0 200407
200507 0 200407
200508 4721 200407
200509 3 200407
200510 1 200407
200511 6 200407
200512 0 200407
200601 0 200407
200602 5 200407
200603 0 200407
200604 1 200407
200605 1 200407
Basically, fill in a record of "0" for the date with no recs.
Can you please help with this query?
Thanks!
|
|
|
|
| Re: Decode, NVL or which do I use? [message #184226 is a reply to message #184205] |
Tue, 25 July 2006 15:21  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
SELECT TO_CHAR(TG.reconcile_date,'yyyymm'),
NVL(COUNT(*),0),
200407
FROM TEMP_MMODRPT tg,
(SELECT per_id,
start_date,
MIN(reconcile_date)
FROM TEMP_MMODRPT
WHERE reconcile_date IS NOT NULL
AND billing_period = 'A'
GROUP BY per_id,
start_date
HAVING (MIN(reconcile_date) >= TO_DATE ('08/01/2004','mm/dd/yyyy')
AND MIN(reconcile_date)< TO_DATE('09/01/2004','mm/dd/yyyy'))
ORDER BY per_id, start_date) tm
WHERE tg.per_id = tm.per_id(+)
AND tg.start_date = tm.start_date(+)
AND tg.billing_period = 'A'
AND (reconcile_date >= TO_DATE('08/01/2004','mm/dd/yyyy')
AND reconcile_date < TO_DATE('07/01/2006','mm/dd/yyyy')
)
GROUP BY TO_CHAR(reconcile_date,'yyyymm');
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
