Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Location
    Indiana
    Posts
    6

    Question Unanswered: Comparable bar graphs

    I keep running into this problem and finally want to get it sorted out. I am creating a chart which shows average exam volume/counts per hour. For example, I will chart out Weekday hourly averages spanning over a month's time. For Tuesday there will be data/bars for only those hours which had activity. This can be confusing when you compare this to Wednesday which could have different hours of activity. This of course throws off the color scheme of each bar. Ideally I want a spot for each hour (whether it is 12am to 11pm or 9-5). This would fix both the color scheme and place an equal spacing to the charts for quick review.

    http://i96.photobucket.com/albums/l1...Joe/delete.jpg

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is your data source? If it's a SQL query then I know of a way
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2006
    Location
    Indiana
    Posts
    6

    Data Source

    Basically pulling all Exams for a certain Department for the whole year

    SELECT "vusrExam"."Accession", "vusrExam"."CompletedDTTM", "vusrExam"."ExamCode", "vusrExam"."ExamDesc", "vusrExam"."OrgCode", "vusrExam"."DepartmentCode", "vusrExam"."PatientStatus", "vusrExam"."PatientTypeCode", "vusrExam"."ExamStatus", "vusrExam"."IsInactive"
    FROM "IDXrad"."dbo"."vusrExam" "vusrExam"
    WHERE "vusrExam"."OrgCode"='PVH' AND ("vusrExam"."CompletedDTTM">={ts '2008-01-01 00:00:00'} AND "vusrExam"."CompletedDTTM"<{ts '2009-01-01 00:00:00'}) AND "vusrExam"."DepartmentCode"='INTR' AND "vusrExam"."ExamStatus"<>'X' AND "vusrExam"."IsInactive"='N'

    Then I am grouping on exam completion time (CompletedDTTM) by Day of the Week (Mon,Tue, Wed etc), then by hour (8am,9am etc) so that I can get averages for that day during that hour.

    http://i96.photobucket.com/albums/l1...e/delete-1.jpg

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're grouping within the report? the way I am going to suggest means you hand the data to CR grouped already.

    The idea is that you create a number (or calendar) table on the source database and then use this in an outer join to your current query such that you get a record for each day/time regardless of whether there's an exam for that time period.

    If you think this option is worth pursuing then let me know and we'll see waht we can put together
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2006
    Location
    Indiana
    Posts
    6

    thanks

    Ah, I can see how this type of join would accomplish the trick. I am SQL challenged so bear with me. Thank you for your time!

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this get you anywhere close?
    Code:
    SELECT n.period
         , Count(vusrExam.CompletedDTTM) As [number_of_exams]
    FROM   (
            SELECT DateAdd(hh, number, {ts '2008-01-01 00:00:00'}) As [period]
            FROM   dbo.Numbers
            WHERE  number BETWEEN 0 AND 366 --Years worth
           ) As [n]
     LEFT
      JOIN IDXrad.dbo.vusrExam As [vusrExam]
        ON vusrExam.CompletedDTTM BETWEEN n.period AND DateAdd(hh, 1, n.period)
    WHERE  vusrExam.OrgCode = 'PVH'
    AND    vusrExam.CompletedDTTM >= {ts '2008-01-01 00:00:00'}
    AND    vusrExam.CompletedDTTM <  {ts '2009-01-01 00:00:00'}
    AND    vusrExam.DepartmentCode = 'INTR'
    AND    vusrExam.ExamStatus <> 'X'
    AND    vusrExam.IsInactive = 'N'
    GROUP
        BY n.period
    note the use of a numbers table
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •