Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Location
    New York
    Posts
    4

    Unhappy Unanswered: Please help with multiple date query

    I need to extrapolate data from a table that contains multiple dates - Date surgery booked, new date of surgery, date surgery cancelled - where I am looking to determine within what date range these events took place.

    From the query (or queries) I need a report that will count how many surgeries were booked within a given time frame, how many were rescheduled from another time frame TO this specified time frame, and how many were cancelled WITHIN the given time frame.
    Additionally, I need the rescheduled surgeries to show up in the next time frame, under surgeries booked.

    So if I have a surgery booked 8/29/03 and reschedule it to 9/15/03, I need the query/report to subtract that counted date from the August date range and add it in to the September range going forward.

    So far I've been able to count the events but can't get a meaningful output in terms of number of cases that actually occurred within that time frame (# booked or # rescheduled within that time - # cancelled + # rescheduled to another time frame)

    Any suggestions would be gratefully appreciated!

    Many thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I assume that your dates are all stored in one field, and activity indicator is stored in another (2-column recordset, right?)

    Try to transpose it and then do a count:

    select count(booked), count(new_surgery_date), count(cancelled) from (
    select booked=case when activity='booked' then date_field else null end,
    new_surgery_date=case when activity='new' then date_field else null end,
    cancelled=case when activity='cancelled' then date_field else null end
    from table_name
    where date_field between '08/01/2003' and '08/31/2003'
    ) x

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdjabarov,

    Your query will count surgeries twice if they were booked and then rescheduled all within the time period, yielding incorrect results for the number of surgeries that occured. It also counts bookings for August even if they were rescheduled for September. Please see my previous post about requirements gathering: http://65.61.175.198/t894814.html



    SusanneMad,

    Post your table definition, as it makes a big difference in how to solve your problem. Probably you will need to check for the latest activity date per surgery to determine whether it actually occured during that period, and then filter out those where the last date entry activity was "cancelled".

    This method, for instance, would return the number of surgeries that actually occured during the time frame:

    select Count(*) Occured
    from activity_table
    inner join (select surgery_id, max(activity_date) Last_Date from activity_table) Last_Activity
    on activity_table.surgery_id = Last_Activity.surgery_id
    and activity_table.activity_date = Last_Activity.Last_Date
    where activity_table.activity <> 'Cancelled'
    and activity_table.activity_date between @StartDate and @EndDate

    blindman

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm is obviously missing the point, but I hope Susanne gets it.

  5. #5
    Join Date
    Aug 2003
    Location
    New York
    Posts
    4
    rdjabarov

    I don't have the dates stored in one field - I have three different fields (OR date; New (rescheduled date); and Cancelled date.

    Blindman

    My table definition is as follows:

    Initials: text
    Date: ()Now
    Patient Last Name: Text
    Patient First Name: Text
    Physician Name: Look up table (pulls data from imported Excel table)
    OR Date: Short Date
    Rescheduled To Date: Short Date
    Date Cancelled: Short Date
    Type of Service: Look up table (pulls from imported Excel table)
    Reason: Look up table (imported from Excel)

    As you accurately pointed out, any cases booked and rescheduled within the same time frame is counted twice when a run a query

    I am really not 'up' on all this at all (aka dbase dunce) and have been feeling my way through Access 97. So if you will indulge me, can you explain a little further how to filter out the cancelled dates and where I should put the method that you described earlier (is it one long expression?)

    Thank you so much for all your help everyone


    Originally posted by blindman
    rdjabarov,

    Your query will count surgeries twice if they were booked and then rescheduled all within the time period, yielding incorrect results for the number of surgeries that occured. It also counts bookings for August even if they were rescheduled for September. Please see my previous post about requirements gathering: http://65.61.175.198/t894814.html



    SusanneMad,

    Post your table definition, as it makes a big difference in how to solve your problem. Probably you will need to check for the latest activity date per surgery to determine whether it actually occured during that period, and then filter out those where the last date entry activity was "cancelled".

    This method, for instance, would return the number of surgeries that actually occured during the time frame:

    select Count(*) Occured
    from activity_table
    inner join (select surgery_id, max(activity_date) Last_Date from activity_table) Last_Activity
    on activity_table.surgery_id = Last_Activity.surgery_id
    and activity_table.activity_date = Last_Activity.Last_Date
    where activity_table.activity <> 'Cancelled'
    and activity_table.activity_date between @StartDate and @EndDate

    blindman

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So, if you have a surgery booked on 8/29 and rescheduled to 8/30, and then cancelled on 8/31, should you need to get 1, 1, 1? Or you need to get 0, 0, 1?

  7. #7
    Join Date
    Aug 2003
    Location
    New York
    Posts
    4
    Within the same date range (say 8/1/03 - 8/31/03) I need to get a count of all the events, so in the scenario you mention it would be 1, 1, 1. I seem to be able to get this result with a query listing all 3 date fields where date 1 is between x and y and date 2 is OR between date x and date y and date 3 is OR between date x and y.
    The real problem is when the dates roll over into another date range, say OR date 8/25, rescheduled to date 9/12/03. In that case I need the report to say
    OR date = 1, rescheduled date = 1, for date range 8/1/03-8/31/03 and then have the rescheduled date show in next range, say, 9/1/03-9/30/03. As I write this I see that even if I could get the date to rollover to the next date range, it will show in the rescheduled field for that range which causes confusion as that then also reads OR date = x, Rescheduled date = 1. If the rescheduled date in the first range implies that a case was rescheduled to another date, then it also means the same thing in the next date range, rather than implying that it was rescheduled TO that next date range.
    Okay, so maybe 'counting' isn't the way to go here . . . Any suggestions on output data by Physician Name, followed by the 3 kinds of date fields where it can count # for each date (rahter than listing each case for each date - may be 3 or 4 a day)?
    Or any other suggestions you might have at all is greatly appreciated

    Many thanks!

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Also, it appears that Type is the only remote indication of a surgery per patient/physician. BTW, bm's qry will not even run, and will produce wrong results if fixed, but I'll let him figure it out

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SusanneMad,

    Don't fry your brain by trying to write a single query that will do give everything you want from the get-go. First write some statements that individually calculate the values that you want. You can start by tweaking these:

    select count(*) Occured
    from YourTable
    where isnull(Rescheduled_To_Date, OR_Date) between @PeriodStart and @PeriodEnd
    and Date_Cancelled is null
    --isnull will return the OR_Date if the Rescheduled_To_Date is null

    select count(Rescheduled_To_Date) Rescheduled
    from YourTable
    where OR_Date between @PeriodStart and @PeriodEnd

    select count(*) OR_Dates
    from YourTable
    where OR_Date between @PeriodStart and @PeriodEnd

    select count(*) Cancelled
    from YourTable
    where OR_Date between @PeriodStart and @PeriodEnd
    and Date_Cancelled is null

    When these separate statements are giving you the results you expect, then you could look for ways to combine them if you are so inclined.

    One way to accomplish this might be to add your physician field into each statement and then use them as subqueries in a query linked to your physician table:

    select Physician, OR_Dates, Rescheduled, Cancelled, Occured
    from PhysicianTable
    left outer join (select Physician, count(*)...) OR_Subquery
    on PhysicianTable.Physician = OR_Subquery.Physician
    left outer join (select Physician, count(*)...) Rescheduled_Subquery
    on PhysicianTable.Physician = Rescheduled_Subquery.Physician
    left outer join (select Physician, count(*)...) Cancelled_Subquery
    on PhysicianTable.Physician = Cancelled_Subquery.Physician
    left outer join (select Physician, count(*)...) Occured_Subquery
    on PhysicianTable.Physician = Occured_Subquery.Physician

    The key to complex problems like this is to break them down into manageable and independently testable parts, and then put them back together step by step.

    Good luck, and don't be surprised if after you have this completed you show to the surgeons (or whoever) and they say "Well, that's not quite what we meant...."

    blindman

    oh, and by the way Robert - Congratulations! There were no coding errors in your last three posts. Perfection achieved!

  10. #10
    Join Date
    Aug 2003
    Location
    New York
    Posts
    4
    Thank you most kindly for your generous and kind assistance. My brain is indeed fried with all of this! I will attempt your solution with fingers crossed . . .


    Originally posted by blindman
    SusanneMad,

    Don't fry your brain by trying to write a single query that will do give everything you want from the get-go. First write some statements that individually calculate the values that you want. You can start by tweaking these:

    select count(*) Occured
    from YourTable
    where isnull(Rescheduled_To_Date, OR_Date) between @PeriodStart and @PeriodEnd
    and Date_Cancelled is null
    --isnull will return the OR_Date if the Rescheduled_To_Date is null

    select count(Rescheduled_To_Date) Rescheduled
    from YourTable
    where OR_Date between @PeriodStart and @PeriodEnd

    select count(*) OR_Dates
    from YourTable
    where OR_Date between @PeriodStart and @PeriodEnd

    select count(*) Cancelled
    from YourTable
    where OR_Date between @PeriodStart and @PeriodEnd
    and Date_Cancelled is null

    When these separate statements are giving you the results you expect, then you could look for ways to combine them if you are so inclined.

    One way to accomplish this might be to add your physician field into each statement and then use them as subqueries in a query linked to your physician table:

    select Physician, OR_Dates, Rescheduled, Cancelled, Occured
    from PhysicianTable
    left outer join (select Physician, count(*)...) OR_Subquery
    on PhysicianTable.Physician = OR_Subquery.Physician
    left outer join (select Physician, count(*)...) Rescheduled_Subquery
    on PhysicianTable.Physician = Rescheduled_Subquery.Physician
    left outer join (select Physician, count(*)...) Cancelled_Subquery
    on PhysicianTable.Physician = Cancelled_Subquery.Physician
    left outer join (select Physician, count(*)...) Occured_Subquery
    on PhysicianTable.Physician = Occured_Subquery.Physician

    The key to complex problems like this is to break them down into manageable and independently testable parts, and then put them back together step by step.

    Good luck, and don't be surprised if after you have this completed you show to the surgeons (or whoever) and they say "Well, that's not quite what we meant...."

    blindman

    oh, and by the way Robert - Congratulations! There were no coding errors in your last three posts. Perfection achieved!

Posting Permissions

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