Count number of days that a doctor works

Question

Count number of days that a doctor works

NineIron

SSChampion

Points: 12537
More actions
March 27, 2013 at 7:09 am

#275636

I need to count the number of days that a doctor works in a month. My data has the doc's name and appointment date. The number of visits per month is
select MonthName, DocName, count(*)
from Appointments
group by MonthName, DocName
What I need to do is count each appointment day as one for each doc, then sum the count to get days worked. I just don't know how to do that efficiently.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

NineIron (3/27/2013)
I need to count the number of days that a doctor works in a month. My data has the doc's name and appointment date. The number of visits per month is
select MonthName, DocName, count(*)
from Appointments
group by MonthName, DocName
What I need to do is count each appointment day as one for each doc, then sum the count to get days worked. I just don't know how to do that efficiently.

From your description it sounds like the query you posted should return what you are looking for???

If that doesn't work then please take a few minutes and read the first article in my signature about best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You will probably have to aggregate by date then by month/docname to eliminate multiple visits per day. Follow Sean's advice - read the article, post ddl and dml to provide a readily-consumable test data set for folks to code against.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101122 More actions · Answer 3

My best guess on the very limited info so far is something like this:

select MonthName, DocName, count(distinct day(appointment_date)) as days_worked

from Appointments

group by MonthName, DocName

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

NineIron SSChampion Points: 12537 More actions · Answer 4

I need the result to be.........

FAMILY PRACTICE BARNERT M.D.,RUTH OCTOBER 6

CARDIOLOGY TESFE M.D.,SIMRET OCTOBER 8

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

Specialty varchar(50),

SchedProv varchar(50),

ApptDt2 DATETIME

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

--===== Insert the test data into the test table

INSERT INTO #mytable

(Specialty, SchedProv, ApptDt2)

SELECT 'FAMILY PRACTICE','BARNERT M.D.,RUTH','2012-10-01 00:00:00.000' UNION ALL

SELECT 'FAMILY PRACTICE','BARNERT M.D.,RUTH','2012-10-02 00:00:00.000' UNION ALL

SELECT 'FAMILY PRACTICE','BARNERT M.D.,RUTH','2012-10-03 00:00:00.000' UNION ALL

SELECT 'FAMILY PRACTICE','BARNERT M.D.,RUTH','2012-10-04 00:00:00.000' UNION ALL

SELECT 'FAMILY PRACTICE','BARNERT M.D.,RUTH','2012-10-05 00:00:00.000' UNION ALL

SELECT 'FAMILY PRACTICE','BARNERT M.D.,RUTH','2012-10-06 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-07 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-01 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-02 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-03 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-04 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-05 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-06 00:00:00.000' UNION ALL

SELECT 'CARDIOLOGY','TESFE M.D.,SIMRET','2012-10-07 00:00:00.000'

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

You said you expect all the sample data to be in October but with the DATEFORMAT you posted the dates are not all in October. If however you set the DATEFORMAT to MDY the following produces exactly what you are looking for.

select Specialty, SchedProv, datename(month, ApptDt2), count(*) as MyCount

from #mytable

group by Specialty, SchedProv, datename(month, ApptDt2)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NineIron SSChampion Points: 12537 More actions · Answer 6

Sorry about the date format and thanx for the help. I'll be better prepared for my next post.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

NineIron (3/27/2013)
Sorry about the date format and thanx for the help. I'll be better prepared for my next post.

No worries. I take that by changing the dateformat the query I posted worked for you?

Often posting these types of questions is very difficult to figure out what to post. Once I directed to the article (which obviously you read) what you posted was great.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

NineIron SSChampion Points: 12537 More actions · Answer 8

My bad. The sample data was a bit too simple. The data I gave only had one patient per day. Docs have multiple visits per day but, I still only need the number of distinct days by doc.

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

(

Specialty varchar(50),

SchedProv varchar(50),

ApptDt2 DATETIME

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT YMD