sql - 每月获取新数据

Question

我的数据是这样的:

+--------+-----------+---------+
| doctor | datefield | patient |
+--------+-----------+---------+
| A      | 1/1/2011  | ABC123  |
| A      | 1/20/2011 | AAA123  |
| A      | 1/21/2011 | AAA123  |
|        |           |         |
| A      | 2/1/2011  | ABC123  |
| A      | 2/10/2011 | BBBYYY  |
|        |           |         |
| B      | 1/1/2011  | ABC123  |
| B      | 1/20/2011 | AXA435  |
| B      | 1/21/2011 | AAA123  |
|        |           |         |
| B      | 2/1/2011  | ABC123  |
| B      | 2/10/2011 | BBBYYY  |
+--------+-----------+---------+

我想计算每位医生的新患者与该特定医生的整个日期范围相比。

假设 2011 年 1 月 是第一个月。

逻辑:

1. doctor A had 2 new patients for january 2011
2. doctor A had 1 new patient for feb 2011
3. doctor B had 3 new patients for January 2011
4. doctor B had 1 new patient for feb 2011

这是我想要的结果:

+--------+-------+------+----------------+
| doctor | month | year | # new patients |
+--------+-------+------+----------------+
| A      |     1 | 2011 |              2 |
| A      |     2 | 2011 |              1 |
| B      |     1 | 2011 |              3 |
| B      |     2 | 2011 |              1 |
+--------+-------+------+----------------+

你能帮我开始吗？

Answer 1

更正了乔的回答的语法

select doctor, year, month, count(patient) [num_new]
from (select doctor, patient, min(MONTH([datefield])) [month], min(YEAR([datefield]))  [year]
    from [dbo].[test_aakruti]
    group by doctor, patient) as table1
group by doctor, [year], [month]