SQL Server 2008 Count(Distinct?

Question

ApptStart
2005-02-18 10:00:00.000
2005-02-18 13:00:00.000
2005-02-18 11:00:00.000
2005-02-21 09:00:00.000
2005-02-18 15:30:00.000
2005-02-18 14:30:00.000
.
.
.

I have a column in our database that looks like the above. I want to count appointments for the month for a given doc. On Fridays most of them do a half day. So I do not want to count Fridays with appointments only in the morning. If the appointment is in the afternoon, after 12:00:00.000 I want to include that day in the distinct count.

So far I have:

SELECT
   ScheduleDays = count(distinct CONVERT(datetime, convert(char(12), a.ApptStart, 1)))
FROM Appointments a
WHERE   
    ApptKind = 1 AND 
    --filter on current month
    a.ApptStart >= ISNULL(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),'1/1/1900') AND
    a.ApptStart < ISNULL(DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0),'1/1/3000') AND
    --Filter on doctor
    a.ResourceID in (201)

This worked when I counted every day with appointments on it, but like I said I need to exclude those half days.So I was thinking about only looking at the last right chars of the ApptStart and comparing it x > noon in a case inside the distinct count...

I tried the following, but it did not work:

ScheduleDays = count(distinct case when (Right(a.ApptStart, 12)) > '12:00:00:000' then 1 else 0 END)

Thanks in advance!

EDIT I tried:

SELECT  
ScheduleDays=COUNT(DISTINCT(CAST(datediff(d,0,a.ApptStart) as datetime)))
FROM Appointments a
WHERE   
ApptKind = 1 AND 
--filter on current month
a.ApptStart >= ISNULL(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),'1/1/1900') AND
a.ApptStart < ISNULL(DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0),'1/1/3000')AND
--filter all days that aren't Friday, and then give you all Fridays with an hour > 12.
 DATENAME(weekday, a.ApptStart) <> 'Friday' OR DATEPART(hour, a.ApptStart) > 12 AND
--Filter on doctor
a.ResourceID in (201)

for 1808 as the count

What do you mean when you say your code "didn't work"? What results did you get? Or did you get an error? — Jim Dagg, Jul 16 '13 at 16:01
I mean that it only returned 1 appointment.. it should have been less than 16 for that particulair doc for this month as 16 was the count before I tried to get rid of the friday half days. EDIT I just counted it should return 12. — user2588088, Jul 16 '13 at 16:06
You are counting the unique results of a case statements whose only results can be 1 or 0, the count then can only be in the range 0 to 2. — bendataclear, Jul 16 '13 at 16:11
Can you give your question a better, more descriptive title? It looks like a question that could be of use to other users, so would be a shame if it will not be found by them because of the title :-) — Josien, Jul 17 '13 at 14:52

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

You probably want to treat dates as dates, rather than strings. You can determine whether a particular timestamp is on a Friday -- or the hour of a timestamp -- using DATEPART, without having to CONVERT it into a CHAR:

datename(weekday, timestamp_value) -- returns Friday

datepart(weekday, timestamp_value) -- returns either 5 or 6, depending on the value of SET DATEFIRST. (Get day of week in SQL 2005/2008)

datepart(hour, timestamp_value) -- returns hour part

Using these, you can test whether a timestamp is on Friday at or after noon by checking if datepart(weekday, timestamp_value) = 6 and datepart(hour, timestamp_value) >= 12.

bendataclear pointed out that you're using distinct on a case statement which can only ever return 0 or 1, so your total will only ever be 0, 1, or 2. If you're trying to determine which days the doctors worked more than half a day, you'll need to select distinct dates --

SELECT COUNT(DISTINCT(CAST(datediff(d,0,timestamp_value) as datetime)))
FROM table_name 
WHERE DATENAME(weekday, timestamp_value) <> 'Friday' OR DATEPART(hour, timestamp_value) > 12
AND the rest of your filters here

The WHERE clause there will give you all days that aren't Friday, and then give you all Fridays that have an hour > 12.

edited May 23 '17 at 12:05

Community

1
1

answered Jul 16 '13 at 16:15

Jim Dagg

2,044
22
29

Thank you for your responses. I believe I almost have it; that datepart is going to be very useful. What should I use instead of "then 1 else 0?" – user2588088 Jul 16 '13 at 16:33
Oh! That might be part of the problem too! @bendataclear above pointed out that you're using distinct on a case statement which can only ever return 0 or 1, so your total will only ever be 0, 1, or 2. If you're trying to determine which days the doctors worked more than half a day, you could just do a distinct on the date itself -- SELECT COUNT(DISTINCT(CAST(datediff(d,0,timestamp_value) as datetime)). I've edited the answer to accommodate. – Jim Dagg Jul 16 '13 at 16:51
I tried the following and received 1808.. whoops I did something wrong. --filter on half days and fridays DATENAME(weekday, a.ApptStart) <> 'Friday' OR DATEPART(hour, a.ApptStart) > 12 – user2588088 Jul 16 '13 at 17:03
Did you remember to include your original filters? – Jim Dagg Jul 16 '13 at 17:07
2

Oh I figured it out; changed the or to and on the filter you helped with. Jim thank you so much for your help! I would vote up your answer, but I just registerd and have no rep points. This was a great resource : ) – user2588088 Jul 16 '13 at 17:21
No problem! Go ahead and click the check mark to accept the answer if it got you where you needed to go. – Jim Dagg Jul 16 '13 at 17:43

SQL Server 2008 Count(Distinct?

1 Answers1