SQL Server 2008 Count(Distinct CASE? -

select          scheduledays = count(distinct(cast(datediff(d, 0, a.apptstart) datetime)))      appointments        apptkind = 1 ,      --filter on current month     a.apptstart >= isnull(dateadd(month, datediff(month, 0, getdate()), 0),'1/1/1900')   ,     a.apptstart < isnull(dateadd(month, datediff(month, 0, getdate())+1, 0),'1/1/3000')and     --filter days aren't friday, , give fridays have hour > 12.     datename(weekday, a.apptstart) <> 'friday' , datepart(hour, a.apptstart) > 12 ,     --filter on doctor     a.resourceid in (201) 

this query through appointment start times , not count fridays our docs work half day on fridays. told want count them, half days (first time around told exclude them lol).

could please me case statement count fridays not have appointment after 12noon, half day? believe have go in scheduledays=count(distinct(cast(datediff(d,0,a.apptstart) datetime))). perhaps can put friday , after 12 filters in there instead of in clause if going use case anyways. scheduledays=count(distinct case when etc. appreciate help.

you can't count half things using count, not way go. but, can arithmetic. think this:

select (count(distinct (case when datename(weekday, a.apptstart) <> 'friday'                              cast(a.apptstart date)                         end)              ) +         0.5 * count(distinct (case when datename(weekday, a.apptstart) = 'friday'                                    cast(a.apptstart date)                               end)                    )        ) scheduledays 

if docs work on fridays half day, don't think need check time of appointment. of course, can if adding second count.

note count days, used simpler syntax of casting datetime date.

edit:

with hour check:

select (count(distinct (case when datename(weekday, a.apptstart) <> 'friday'                              cast(a.apptstart date)                         end)              ) +         0.5 * count(distinct (case when datename(weekday, a.apptstart) = 'friday' ,  datepart(hour, a.apptstart) <= 12                                     cast(a.apptstart date)                               end)                    )        ) scheduledays