August 9, 2022 at 5:27 pm
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 🙂
August 9, 2022 at 6:13 pm
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 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply