create pivot from two queries for graphing

[Guest]

I have a table of trouble tickets for a group of test events. The table is
like so:

table Tickets
{
enum TestEvent
date OriginationDate
enum Resolution
date ResolutionDate
}

My goal is to produce a multi-line graph with d ate along the x-axis and
cumulative total along the y-axis. There will be two lines for each
TestEvent. One that shows originated tickets and one that shows resolved
tickets. The following queries generate the cumulative totals for a given
TestEvent.

SELECT OriginationDate,
(SELECT count(Q.OriginationDate)
FROM Tickets As Q
WHERE Q.OriginationDate <= Tickets.OriginationDate
AND TestEvent='alpha3'
) As CumulativeToDate
FROM Tickets
GROUP BY OriginationDate
ORDER BY OriginationDate;

SELECT ResolutionDate, (SELECT count(Q.ResolutionDate)
FROM Tickets As Q
WHERE Q.ResolutionDate <= Tickets.ResolutionDate
AND TestEvent='alpha3'
AND Resolution IN ('Deferred','Closed')
) AS CumulativeToDate
FROM Tickets
GROUP BY ResolutionDate
ORDER BY ResolutionDate;

I'm looking for the easiest way to get all of this to the graph. I've
expirimented with the crosstab query wizard, but I can't get cumulative
totals and it only produces Originated or Resolved, not both.

Happy Halloween!

 

[[MVP] S.Clark]

I guess your options would be:
1. a UNION query - yecch.
2. Write the needed data to a temp table, then use it as the basis for the
crosstab.

I stink at graphs, but maybe just getting the data into one spot is all
you're missing.

 

[Guest]

Nod. I was headed toward the temp table solution. Thanks!