SQL如果有预约，我该如何创建将计数增加1的条件

Question

我正在编写一个SQL查询，其中我需要获得医生一个月内约会的天数。例如，如果医生今天有5次约会，那么这将算作1，如果明天A医生也有6个约会，这也将算作1，以便本月到目前为止的医生A现在总共有2天的约会。这是我下面的查询本质上，我需要获得医生在12月份预约的天数。12月有31天，因此医生可以在31或31分的31或12个约会等。任何帮助都很好。我很确定这是需要的条件

 select distinct()cast(apptdatetime as date) as AptDate, p.lastname as Lastname,p.firstname as Firstname,p.NPI,count(*) as ApptCount from appointment a 
inner join provider p on p.providerid=a.providerid
inner join campaignprovider cp on cp.providerid=p.providerid
where cast(apptdatetime as date) between '12/01/2017' and '12/31/2017'                     
group by cast(apptdatetime as date),p.lastname,p.firstname,p.NPI
order by cast(apptdatetime as date),p.lastname,p.firstname,p.NPI

Answer 1

您可以使用count(distinct)：

select p.lastname as Lastname, p.firstname as Firstname, p.NPI,
       count(distinct cast(apptdatetime as date)) as daycount,
       count(*) as apptcount
from appointment a inner join
     provider p
     on p.providerid = a.providerid inner join
     campaignprovider cp
     on cp.providerid = p.providerid
where cast(apptdatetime as date) between '2017-12-01' and '2017-12-31'
group by p.lastname, p.firstname, p.NPI
order by p.lastname, p.firstname, p.NPI;

Answer 2

首先：为什么需要施放日期？是否可以正确设置列类型？

否则：您所描述的只是对每个约会的日期值的独特计数。您可以按月进行分组，然后除以该月的天数，这可以分别通过表达式和功能调用来实现。

沿着这些行：

SELECT 
    COUNT(DISTINCT DATE(appointment_datetime)), 
    DAY(LAST_DAY(appointment_datetime)),
    last_name 
FROM 
    table 
GROUP BY 
    MONTH(appointment_datetime), 
    last_name;