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

  • Also, when I run my query based on your above code, everything returns as 1 for average.  I know that is not correct.

    select created_date, avg(Cases6) 
    from [dbo].[tmp_CasesAssigned]
    group by Created_date
    order by created_date

    Results:

    Created_Date Avg_Cases6

    2020-01-02 1

    2020-01-03 NULL

    2020-01-04 1

    2020-01-06 NULL

    2020-01-07 NULL

    2020-01-08 NULL

    2020-01-09 1

    2020-01-10 1

    2020-01-11 1

    2020-01-13 NULL

    2020-01-14 1

    2020-01-15 1

    2020-01-16 1

    2020-01-17 1

    2020-01-18 1

    2020-01-20 1

    2020-01-21 1

    2020-01-22 NULL

    2020-01-23 1

    2020-01-24 1

    2020-01-25 NULL

    2020-01-27 NULL

    2020-01-28 1

    2020-01-29 NULL

    2020-01-30 1

    2020-01-31 1

    2020-02-01 1

    2020-02-03 NULL

    2020-02-04 NULL

    2020-02-05 1

    2020-02-06 1

    2020-02-07 1

    2020-02-08 NULL

    2020-02-10 NULL

    2020-02-11 1

    2020-02-12 1

    2020-02-13 1

    2020-02-14 1

    2020-02-15 1

     

    Also, How do I come up with the total number of days worked in that date range?  There are some saturday's so I can't just calucate total days by Mon-fri in a year.  Is there something I can do with DateAdd or Datediff to come up with this number?  I do have a calendar table if it would help to join that to get a total number of days to come up with this average.  I hope I don't offend anyone when I say; I hate creating reports for metrics 🙂

  • ashatimjohn wrote:

    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?

    This gives you the average physician count per day for each category. This is not an "average number of days" but I don't know what that means. If this is not what you need, what should the number be for the category case>8? There are 8 physicians in two days.

    select  avg(a.Cases6) as AvgCases6PerDay,
    avg(a.Cases7) as AvgCases7PerDay,
    avg(a.Cases8) as AvgCases8PerDay
    from (
    select Created_Date,
    sum(Cases6)*1.0 as Cases6,
    sum(Cases7)*1.0 as Cases7,
    sum(Cases8)*1.0 as Cases8
    from dbo.summary
    group by Created_Date
    ) as a

    Code with data

    DROP TABLE IF EXISTS dbo.summary
    CREATE TABLE dbo.summary
    ( Created_Date date,
    Pathologist varchar(100),
    Cases6 int,
    Cases7 int,
    Cases8 int
    )

    INSERT dbo.summary
    VALUES
    ('2022-06-01' , 'Best Rocha M.D., Alejandro', 0, 0,0)
    , ('2022-06-01' , 'May M.D., Rebecca',0, 0 ,1)
    , ('2022-06-01' , 'Bourne M.D., T. David',0 ,0 ,1)
    , ('2022-06-01' , 'Murphy M.D., Joel',0,1,0 )
    , ('2022-06-01' , 'Bell M.D., Jane M',0, 0 ,1)
    , ('2022-06-01' , 'Kuperman M.D., Michael',0, 1 ,0)
    , ('2022-06-01' , 'Wilson M.D., Jon',0, 0,1)
    , ('2022-06-01' , 'Dvanajscak M.D., Zeljko',0, 0,1)
    , ('2022-06-01' , 'Larsen M.D., Chris',0, 0, 0)
    , ('2022-06-01' , 'Walker M.D., Patrick D',0, 0, 0)
    , ('2022-06-01' , 'Boils M.D., Christie L.',0 ,0,1)
    , ('2022-06-01' , 'Cassol M.D., Clarissa',0, 0,1)
    , ('2022-06-01' , 'Cossey M.D., L. Nicholas',0, 0, 0)
    , ('2022-06-01' , 'Sharma M.D., Shree G.',0, 0, 0)
    , ('2022-06-02' , 'Murphy M.D., Joel',0, 0,1)
    , ('2022-06-02' , 'Sharma M.D., Shree G.',0, 0, 0)
    , ('2022-06-02' , 'Bourne M.D., T. David',0,1, 0)
    , ('2022-06-02' , 'Cassol M.D., Clarissa',1, 0, 0)


    select avg(a.Cases6) as AvgCases6,
    avg(a.Cases7) as AvgCases7,
    avg(Cases8) as AvgCases8
    from (
    select Created_Date,
    sum(Cases6)*1.0 as Cases6,
    sum(Cases7)*1.0 as Cases7,
    sum(Cases8)*1.0 as Cases8
    from dbo.summary as a
    group by Created_Date
    ) as a

Viewing 2 posts - 16 through 17 (of 17 total)

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