Helpdesk database statistics crosstab report

Hello SQL and Crystal Reports friends,

I am trying to make a report and need some help please.

It is a helpdesk database. Jobs are logged, and then closed. Each of
these events is timestamped in the database in the date fields
DateLogged and DateClosed

JanFebMarApr

NewClosedNewClosedNewClosedNewClosed
10 51362364525

etc.

I am trying to create a crosstab style report that will show each month
of the year along the top, and then the number of jobs logged and closed
during that month. The problem I am having is that when Crystal Groups
by the month, you have to specify a date field for the grouping. If I
select DateLogged, then the crosstab will accurately show all of the
jobs logged for that month, but is not correct for the jobs closed
during that month. The problem is that is counting the number of jobs
that were both logged AND closed during the grouped month.

Can anyone see how such a report is possible?

Furthermore, I would like to be able to calculate how many jobs were
open at the start of the month, as well as at the end of the month.

Thank you for your help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!There isn't an easy way to create cross tabs in sql. The next version
of mssqlserver is suppose to have some crosstabs functions. If you
really want to do it in sql, I have 3 articles that explained how to
do it using transact-sql. (Painful)
http://www.sql-server-performance.c...orting_page.asp|||Thanks for the feedback Louis. I read your article and it has given me
a few ideas, but isn't really what I'm after.

I have been able to achieve what I'm after partly by combining 2 queries
with the UNION operator. The first query aggregates the count of all
rows that occur on datelogged. The second query does the same with
dateclosed. Then I combine them to get the results. It works well, but
I am still looking for a way to count the number of jobs that were open
at a particular point in time.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!