Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: complicated query

    I have an interesting query to come up with and am requesting advice. I am trying to get a count of PERSONS admitted to our system every hour on the hour ( a snapshot of active PERSONS at 1:00, 2:00, 3:00 ect). If the PERSON is ADMITTED at 2:01 and DISCHARGED at 2:59 they will NOT be counted. I need only PERSONS active at the top of the hour. The query will be used in Business Objects for a report.
    Thanks in advance!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Build the query (select user from .... and submit it as a job to
    dbms_jobs ... set the job to execute every hour, on the hour.
    Set the job_queue_processes to a value if not already set.

    HTH
    Gregg

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: complicated query

    SELECT to_char(DISCHARGED,'mm/dd/yyyy hh24') hour,
    count(*) patients
    FROM PERSONS
    WHERE DISCHARGED - ADMITTED >= 1/24 -- 1 hour
    GROUP BY to_char(DISCHARGED,'mm/dd/yyyy hh24')

    this should give you the number of patients that stayed atleast an hour

    HIH

  4. #4
    Join Date
    Apr 2004
    Posts
    2
    I need to run the query on historical data. For instance, if the user wants to see the stats for march 30, 2004, i need to be able to grab the data for each hour on that day. Any suggestions on this method?

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    create a table of Date_Hours. 1 record for every hour of every day. Then join this table to the Persons table.

    HIH

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    prompt "Enter Date (dd-mon-yy):" accept v_date

    for i in 1..24
    select ..... from .... where datefromtable =
    (v_date + i/24);

    ....
    ....

    HTH
    Gregg

  7. #7
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    You could also try

    Code:
    SELECT To_Char(DISCHARGED,'mm/dd/yyyy hh24') hour,
                Sum(Decode(Sign((DISCHARGED - ADMITTED) - 1/24),
                      -1, -- returns negative number then less than 1 hour
                          0,  -- add 0 else add 1
                             1)) patients 
    FROM PERSONS
    WHERE DISCHARGED Between begin_date And end_date
    GROUP BY to_char(DISCHARGED,'mm/dd/yyyy hh24')
    HIH

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296

    Re: complicated query

    Originally posted by twcampbell
    I have an interesting query to come up with and am requesting advice. I am trying to get a count of PERSONS admitted to our system every hour on the hour ( a snapshot of active PERSONS at 1:00, 2:00, 3:00 ect). If the PERSON is ADMITTED at 2:01 and DISCHARGED at 2:59 they will NOT be counted. I need only PERSONS active at the top of the hour. The query will be used in Business Objects for a report.
    Thanks in advance!
    please describe your table

    here is some code to go by:
    PHP Code:
    SELECT distinct
    COUNT
    (*) over (PARTITION BY t.customerTO_CHAR(t.admitted_dt,'HH24')) customer_count,
    t.customer
    FROM admitted_table t
    WHERE TO_CHAR
    (t.admitted_dt'MMDDYYYY HH24') = '04162004 12'
    AND t.discharged_dt >TO_date'04162004 12''MMDDYYYY HH24'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Before we answer this question, we need more information.

    1. If a person is admitted at 05:01 and discharged at 08:01 hours, do you want this person to be counted for the 6 o'clock, 7 o'clock and 8 o'clock data?

    2. If a person is admitted before the date in consideration, do you want them to be included?

    3. If a person is admitted on that day, but discharged after that date, do we want the person to be included?

    I think that none of the solutions suggested so far cater to these situations.

Posting Permissions

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