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

Question

Post reply

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

ashatimjohn

SSC Veteran

Points: 247
More actions
August 3, 2022 at 9:12 pm

#4072174
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:
>6
>7
8
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 a.id = r.acc_id
join personnel_2 p on p.id = 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!

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

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

pietlinden SSC Guru Points: 63500 More actions · Answer 1

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 2 years, 3 months ago by pietlinden.

ashatimjohn SSC Veteran Points: 247 More actions · Answer 2

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.

Zidar SSC Enthusiast Points: 126 More actions · Answer 3

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...

pietlinden SSC Guru Points: 63500 More actions · Answer 4

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.

ratbak Ten Centuries Points: 1358 More actions · Answer 5

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

ashatimjohn SSC Veteran Points: 247 More actions · Answer 6

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

pietlinden SSC Guru Points: 63500 More actions · Answer 7

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

Case closed.

Good luck!

ashatimjohn SSC Veteran Points: 247 More actions · Answer 8

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

ashatimjohn SSC Veteran Points: 247 More actions · Answer 9

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.

ratbak Ten Centuries Points: 1358 More actions · Answer 10

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

Doctors

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.

ashatimjohn SSC Veteran Points: 247 More actions · Answer 11

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.

Thanks!

Zidar SSC Enthusiast Points: 126 More actions · Answer 12

Thi is what you gave us, approximately:

DROP TABLE IF EXISTS #Doctors
GO
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
)
GO

--- Now, the  legwork:
INSERT INTO #Doctors (WorkDayDate,Pathologist,[Today Pathologist had 6 cases],[Today Pathologist had 7 cases],[Today Pathologist had 8 cases] )
VALUES
  ('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...

Ed B Hall of Fame Points: 3665 More actions · Answer 13

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 2 years, 3 months ago by Ed B.
This reply was modified 2 years, 3 months ago by Ed B.
This reply was modified 2 years, 3 months ago by Ed B.

ashatimjohn SSC Veteran Points: 247 More actions · Answer 14

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?