Helpdesk database statistics crosstab report

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Helpdesk Man
    #1

    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”


    Jan Feb Mar Apr

    New Closed New Closed New Closed New Closed
    10 5 13 6 23 6 45 25

    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!
    Tags: None
    • louis nguyen
      #2
      Re: Helpdesk database statistics crosstab report

      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.com/ln_web_reporting_page.asp

        Comment

        • Helpdesk Man
          #3
          Re: Helpdesk database statistics crosstab report


          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!

            Comment

            Previous template Next
            Working...