Create a report to count the # of days a physician has a case assigned

  • Hi,


    I need to create a report that has created date, physician name, case #.  I need to display the following in the report.

    Average # of days a physician had the followig number of cases assigned:




    Here is my query.

    select a.accession_no, a.created_date, p.last_name, p.first_name, p.mid_name, p.full_name
    from accession_2 a
    join acc_role_assignment r on = r.acc_id
    join personnel_2 p on = r.assigned_to_id and role_id = 2
    where created_date >= @StartDate and created_date < = @EndDate

    I'm not sure how to do this in my report.  I can group on created date, then on physician to get the # of cases per day and per physician, but I'm not sure how to do the above, can someone give me some tips and ideas on how to get started?


    thanks a bunch!


  • This is a whole lot easier with some [fake] data.

    Does a case have a Close Date or similar? If you have that and a Calendar table, then this is absolutely stupid easy.

    SELECT c.CaseID, c.Doctor,  COUNT(*) As DayCount

    FROM Case c CROSS JOIN Calendar d

    WHERE d.TheDate >= c.OpenDate AND d.TheDate<=c.CloseDate

    AND c.IsHoliday = 0 AND c.IsWeekend=0

    GROUP BY c.CaseID, c.Doctor

    If you use a Calendar table, you can do fun things like leave out weekends and holidays and it's super simple

    Wait... Average days for what? You can calculate the number of days a case was open. So do you mean the average number of days a given doctor had his cases open? (Like I said, the best way to get a good answer is to provide some sample data and the expected result from that data.

    • This reply was modified 1 month, 3 weeks ago by  pietlinden.
  • I don't need to find out open cases only cases assigned.  I'm unclear why I need to bring in a calendar table (even though I do have one), when I have a created_date that need to pull the date from.    I don't understand cross joins or why I need to use it.  I need to know the average # of days where a dr had 6, 7, or 8 cases assigned to them.

  • Please read the answer from pietlinden. Yo must provide CREATE TABLE statements and sample data, which allows to see the goal.

    No table structure, no data => no help available

    Zidar's Theorem: The best code is no code at all...

  • What's the [EndDate] that matches with [Created Date] so we know how long a duration is?

    If you want useful help, please read and follow the instructions in this article: Forum Etiquette: How to post data/code on a forum to get the best help – SQLServerCentral

    I have found people here to be incredibly helpful when I ask a question that explains what data I have (create table and insert scripts), what output I'm trying to get, and maybe the logic. But only if I provide enough information to answer the question.

  • A link that further explains what, how, & why to provide DDL, sample data, & expected results for the sample data: How to post code problems

  • Ok, I created a report that has a count of the # of cases assigned per day.  Each column contains a count based on a specific # of day's. See the attached print screen.  I was hoping this would be easier to break down the question I am asking.  I need to modify this report to get the average # of days that has a count = 6, count = 7, count = 8, etc.  HOw woudl I modify this report to be able to come up with that information.


    # of cases assgned per day

  • Okay, since you don't want to provide data, we can't help you.

    Case closed.

    Good luck!

  • Here's the data:

    Created_Date Pathologist Cases6 cases7 cases8

    2022-06-01 Best Rocha M.D., Alejandro NULL NULL NULL

    2022-06-01 May M.D., Rebecca NULL NULL 1

    2022-06-01 Bourne M.D., T. David NULL NULL 1

    2022-06-01 Murphy M.D., Joel NULL 1 NULL

    2022-06-01 Bell M.D., Jane M NULL NULL 1

    2022-06-01 Kuperman M.D., Michael NULL 1 NULL

    2022-06-01 Wilson M.D., Jon NULL NULL 1

    2022-06-01 Dvanajscak M.D., Zeljko NULL NULL 1

    2022-06-01 Larsen M.D., Chris NULL NULL NULL

    2022-06-01 Walker M.D., Patrick D NULL NULL NULL

    2022-06-01 Boils M.D., Christie L. NULL NULL 1

    2022-06-01 Cassol M.D., Clarissa NULL NULL 1

    2022-06-01 Cossey M.D., L. Nicholas NULL NULL NULL

    2022-06-01 Sharma M.D., Shree G. NULL NULL NULL

    2022-06-02 Murphy M.D., Joel NULL NULL 1

    2022-06-02 Sharma M.D., Shree G. NULL NULL NULL

    2022-06-02 Bourne M.D., T. David NULL 1 NULL

    2022-06-02 Cassol M.D., Clarissa 1 NULL NULL

  • I changed the way I did this to try to make it easier.  I've calculed the case assignments and put in totals.  If cases 6 has a value of 1 it means that dr was assigned 6 cases, if cases7 has 1 one it means that dr was assigned 7 cases, etc.

  • If we respond with the following is how you return the average # of days a physician had cases, is that useful to you?


    Or this?

    Doctor                                    AvgDays

    Best Rocha M.D., Alejandro 6

    May M.D., Rebecca                 7

    Bourne M.D., T. David           8

    Murphy M.D., Joel                  5

    Bell M.D., Jane M                   4

    Probably not. Why? Because I did not provide any useable information in a way that you can copy it into an editor window and edit or execute it. That's what we need from you -- scripts that we can copy into SSMS to edit & execute without spending extra time trying to create those scripts ourselves.

    PLEASE read the links that pietlinden or I provided you, and follow those instructions if you want to enable us to help you. And please make sure your sample data and expected results are in synch with your latest business rules and DDL, and with each other.

  • I think I must be saying this wrong.  This is the quesiton that was asked of me:  What is the average number of days that physicians had the following number of cases assigned

    6 > 7 > 8 > 9 > 10 > 11


    He wants it for July 1, 2021 - June 30, 2022.  He doesn't want it broken down by phsician just in general.  It would be so much easier to give him the above information.  I know how to get the post that ratbak replied to.  I don't know how to get the answer to the question that the person asked me to pull this info for.  Maybe I am making it to complicated.


    I feel like I gave column info and data.  I'm not sure what else to provide, but I'm happy to provide anything that is requested as long as I know what your asking for.



  • Thi is what you gave us, approximately:

    CREATE TABLE #Doctors
    WorkDayDate date
    , Pathologist nvarchar(50)
    , [Today Pathologist had 6 cases] int
    , [Today Pathologist had 7 cases] int
    , [Today Pathologist had 8 cases] int

    --- Now, the legwork:
    INSERT INTO #Doctors (WorkDayDate,Pathologist,[Today Pathologist had 6 cases],[Today Pathologist had 7 cases],[Today Pathologist had 8 cases] )
    ('2022-06-01' , 'Best Rocha M.D., Alejandro', NULL, NULL,NULL)
    , ('2022-06-01' , 'May M.D., Rebecca',NULL, NULL ,1)
    , ('2022-06-01' , 'Bourne M.D., T. David',NULL ,NULL ,1)
    , ('2022-06-01' , 'Murphy M.D., Joel',NULL,1,NULL )
    , ('2022-06-01' , 'Bell M.D., Jane M',NULL, NULL ,1)
    , ('2022-06-01' , 'Kuperman M.D., Michael',NULL, 1 ,NULL)
    , ('2022-06-01' , 'Wilson M.D., Jon',NULL, NULL,1)
    , ('2022-06-01' , 'Dvanajscak M.D., Zeljko',NULL, NULL,1)
    , ('2022-06-01' , 'Larsen M.D., Chris',NULL, NULL, NULL)
    , ('2022-06-01' , 'Walker M.D., Patrick D',NULL, NULL, NULL)
    , ('2022-06-01' , 'Boils M.D., Christie L.',NULL ,NULL,1)
    , ('2022-06-01' , 'Cassol M.D., Clarissa',NULL, NULL,1)
    , ('2022-06-01' , 'Cossey M.D., L. Nicholas',NULL, NULL, NULL)
    , ('2022-06-01' , 'Sharma M.D., Shree G.',NULL, NULL, NULL)
    , ('2022-06-02' , 'Murphy M.D., Joel',NULL, NULL,1)
    , ('2022-06-02' , 'Sharma M.D., Shree G.',NULL, NULL, NULL)
    , ('2022-06-02' , 'Bourne M.D., T. David',NULL,1, NULL)
    , ('2022-06-02' , 'Cassol M.D., Clarissa',1, NULL, NULL)

    If we run SELECT * FROM #Doctors we get

    WorkDayDatePathologistToday Pathologist had 6 casesToday Pathologist had 7 casesToday Pathologist had 8 cases
    2022-06-01Best Rocha M.D., AlejandroNULLNULLNULL
    2022-06-01May M.D., RebeccaNULLNULL1
    2022-06-01Bourne M.D., T. DavidNULLNULL1
    2022-06-01Murphy M.D., JoelNULL1NULL
    2022-06-01Bell M.D., Jane MNULLNULL1
    2022-06-01Kuperman M.D., MichaelNULL1NULL
    2022-06-01Wilson M.D., JonNULLNULL1
    2022-06-01Dvanajscak M.D., ZeljkoNULLNULL1
    2022-06-01Larsen M.D., ChrisNULLNULLNULL
    2022-06-01Walker M.D., Patrick DNULLNULLNULL
    2022-06-01Boils M.D., Christie L.NULLNULL1
    2022-06-01Cassol M.D., ClarissaNULLNULL1
    2022-06-01Cossey M.D., L. NicholasNULLNULLNULL
    2022-06-01Sharma M.D., Shree G.NULLNULLNULL
    2022-06-02Murphy M.D., JoelNULLNULL1
    2022-06-02Sharma M.D., Shree G.NULLNULLNULL
    2022-06-02Bourne M.D., T. DavidNULL1NULL
    2022-06-02Cassol M.D., Clarissa1NULLNULL

    Now we can see what is going on. On each day, a doctor has seen either 6 or 7 or 8 patients. In each row, we allow 1 in only one column, the rest are NULL. Zero would be much better than NULL, but that is not the point of the exercise.

    My understanding is that you want this:

    SELECT WorkDayDate
    , [Avg 6 cases] = avg([Today Pathologist had 6 cases])
    , [Avg 7 cases] = avg([Today Pathologist had 7 cases])
    , [Avg 8 cases] = avg([Today Pathologist had 8 cases])
    FROM #Doctors
    GROUP BY WorkDayDate

    WorkDayDate Avg 6 cases Avg 7 cases Avg 8 cases
    ----------- ----------- ----------- -----------
    2022-06-01 NULL 1 1
    2022-06-02 1 1 1
    Warning: Null value is eliminated by an aggregate or other SET operation.

    (2 rows affected)

    If I misunderstood the question,  apologize. At least you should be able to provide proper data definitions (CREATE TABLE) and sample data. If by some chance I was right, then you can by me a beer, not becouse of the solution, but becouse I had to type INSERT INTO statements.

    Good Luck.

    Zidar's Theorem: The best code is no code at all...

  • Edit. Someone already did the inserts and my code didn't work.

    I can't see how average number of days means anything.

    Your sample data has two days in it. On 6/1 there are 7 physicians with Cases>8 and on 6/2 there is 1 physician with Cases>8. What would you call the average number of days? Physicians have Cases >8 on both days, so is the average 2 days total divide by 2 days in the data = 1?  And average of 1 means every day.

    For cases>7 there are 2 physicians on 6/1 and 1 physician on 6/2. Again the average number of days that physicians had Cases>7 is 1, but this doesn't reflect the higher values for cases>8.

    What numbers do you expect to see from your sample data given that it is a two day reporting period?







    • This reply was modified 1 month, 2 weeks ago by  Ed B.
    • This reply was modified 1 month, 2 weeks ago by  Ed B.
    • This reply was modified 1 month, 2 weeks ago by  Ed B.
  • I think you are on the right track here.

    Instead of an average for each day , he wants just 1 average per date range (weekdays) for 6 cases, 7 cases, and 8 cases.  He wants the average based on the date range of 07/01/2021 - 06/30/2022.  How would I modify your code to get the Average based on the date range?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply