May 4, 2011 at 10:28 am
I'm trying to chart incidents from our problem tracking software by month and struggling a bit. The query returns numbers of incidents per month, but might not necessarily have data for every month. ie, January 5, February 4, April 8. No data for March since there were no records. I've spent some time using a cross join to get values for months with no data, but it's running slow and its unnecessarily complex.
Is there a way to tell my chart what range I want it to display (ie, a whole year) and to fill in the blanks if no data is returned for a month?
May 4, 2011 at 10:46 am
No need to cross join.
make a list of all years / months in your date range. I personnally use a calendar table and a Years/months tables as well.
Then in the final step select * from that table left join (current result set).
isnull to output 0 and you're done. Shouldn't be slow at all.
May 4, 2011 at 12:14 pm
I'm still having trouble with this, what you're saying is basically (one of the ways) I've tried but I can't quite wrap my brain around it.
Here's my original query:
SELECT i.product_name AS productname,
i.month,
SUM(opened_count) AS openedcount,
SUM(closed_count) AS closedcount,
i.organization
FROM ( ( SELECT NVL(product_name, 'Unknown') AS product_name,
TRUNC(reported_date, 'MM') AS month,
COUNT(*) AS opened_count,
0 AS closed_count,
owner_support_organization AS organization
FROM aitremedydw.incident
WHERE reported_date > TRUNC(SYSDATE, 'yyyy') AND reported_date < SYSDATE
GROUP BY NVL(product_name, 'Unknown'), TRUNC(reported_date, 'MM'), owner_support_organization )
UNION ALL
( SELECT NVL(product_name, 'Unknown') AS product_name,
TRUNC(closed_date, 'MM') AS month,
0 AS opened_count,
COUNT(*) AS closed_count,
owner_support_organization AS organization
FROM aitremedydw.incident
WHERE closed_date > TRUNC(SYSDATE, 'yyyy') AND closed_date < SYSDATE
GROUP BY NVL(product_name, 'Unknown'), TRUNC(closed_date, 'MM'), owner_support_organization ) ) i
GROUP BY i.product_name, i.month, i.organization
Then I tried a left join:
SELECT * FROM
(SELECT TO_DATE('01-JAN-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-FEB-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-MAR-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-APR-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-MAY-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-JAN-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-JUN-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-JUL-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-AUG-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-SEP-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-OCT-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-NOV-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual
UNION
SELECT TO_DATE('01-DEC-' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyy')) AS month FROM dual) m
LEFT JOIN
(SELECT i.product_name AS productname,
i.month,
SUM(opened_count) AS openedcount,
SUM(closed_count) AS closedcount,
i.organization
FROM ( ( SELECT NVL(product_name, 'Unknown') AS product_name,
TRUNC(reported_date, 'MM') AS month,
COUNT(*) AS opened_count,
0 AS closed_count,
owner_support_organization AS organization
FROM aitremedydw.incident
WHERE reported_date > TRUNC(SYSDATE, 'yyyy') AND reported_date < SYSDATE
GROUP BY NVL(product_name, 'Unknown'), TRUNC(reported_date, 'MM'), owner_support_organization )
UNION ALL
( SELECT NVL(product_name, 'Unknown') AS product_name,
TRUNC(closed_date, 'MM') AS month,
0 AS opened_count,
COUNT(*) AS closed_count,
owner_support_organization AS organization
FROM aitremedydw.incident
WHERE closed_date > TRUNC(SYSDATE, 'yyyy') AND closed_date < SYSDATE
GROUP BY NVL(product_name, 'Unknown'), TRUNC(closed_date, 'MM'), owner_support_organization ) ) i
GROUP BY i.product_name, i.month, i.organization) x
ON m.month = x.month
ORDER BY 2, 1
...which gives me identical results and runs about 10x slower. Do you know what I'm doing wrong?
I've sent a request to our DBAs for help but there's no telling when they'll get to it. Thanks for the help!
May 4, 2011 at 2:08 pm
How much data is the query returning?
Another option is to insert the results into temp table. Then do an insert where not exists. The good thing here is that you can play with adding indexes (or at least stats), so that query can be better tuned.
I would personnally build a calendar table for this rather than building it on the fly. You will most likely use it often moving forward... I have 1 with every possible info about days and then another simply with years and months.
Also I would use union all instead of union. Tho I don't think this is where you're losing speed.
May 4, 2011 at 2:57 pm
It's not a lot, maybe 20k rows. I did just manage to get it working the way I like, and it's not running too terribly slow. Unfortunately I can't make any database changes so temp and calendar tables aren't possible.
May 4, 2011 at 2:59 pm
You can't event do select into #tmp?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply