简单的SQL，写不出来

[wangyounihao]

c_Id c_NO c_ServiceType c_PlanDate c_CompleteDate
18403 RP2020090179 维修 2020-09-16 00:00:00.000 NULL
18404 RP2020090180 调试 2020-09-16 00:00:00.000 2020-09-16 17:58:58.683
18406 RP2020090182 清洗 2020-09-16 00:00:00.000 2020-09-16 22:21:09.067
18349 RP2020090125 安检 2020-09-16 00:00:00.000 2020-09-16 12:36:58.727
18402 RP2020090178 维修 2020-09-16 00:00:00.000 NULL
18405 RP2020090181 安装 2020-09-16 12:12:58.150 2020-09-16 16:22:05.657
18407 RP2020090183 安装 2020-09-16 12:39:26.313 NULL

想得到结果 安装 计划 2 ，完成 1
维修 计划 2 ，完成 0
清洗 计划 1 ，完成 1
调试 计划 1 ，完成 1
安检 计划 1 ，完成 1

Sql 怎么写？或者数据库怎么设计下？？？

[雨夹雪]

CREATE TABLE #T
(
c_Id INT, c_NO VARCHAR(20), c_ServiceType VARCHAR(10),c_PlanDate DATETIME, c_CompleteDate DATETIME
)

INSERT INTO #T ( c_Id , c_NO , c_ServiceType , c_PlanDate , c_CompleteDate )
VALUES  
(18403, 'RP2020090179','维修','2020-09-16 00:00:00.000', NULL),
(18404, 'RP2020090180','调试','2020-09-16 00:00:00.000','2020-09-16 17:58:58.683'),
(18406, 'RP2020090182','清洗','2020-09-16 00:00:00.000','2020-09-16 22:21:09.067'),
(18349, 'RP2020090125','安检','2020-09-16 00:00:00.000','2020-09-16 12:36:58.727'),
(18402, 'RP2020090178','维修','2020-09-16 00:00:00.000',NULL),
(18405, 'RP2020090181','安装','2020-09-16 12:12:58.150','2020-09-16 16:22:05.657'),
(18407, 'RP2020090183','安装','2020-09-16 12:39:26.313', NULL)

SELECT c_ServiceType,COUNT(c_PlanDate) AS 计划,COUNT(c_CompleteDate) AS 完成  FROM #T
GROUP BY c_ServiceType

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([c_Id] int,[c_NO] nvarchar(32),[c_ServiceType] nvarchar(22),[c_PlanDate] Date,[c_CompleteDate] Date)
Insert #T
select 18403,N'RP2020090179',N'维修','2020-09-16',null union all
select 18404,N'RP2020090180',N'调试','2020-09-16','2020-09-16' union all
select 18406,N'RP2020090182',N'清洗','2020-09-16','2020-09-16' union all
select 18349,N'RP2020090125',N'安检','2020-09-16','2020-09-16' union all
select 18402,N'RP2020090178',N'维修','2020-09-16',null union all
select 18405,N'RP2020090181',N'安装','2020-09-16','2020-09-16' union all
select 18407,N'RP2020090183',N'安装','2020-09-16',null
Go
--测试数据结束
SELECT c_ServiceType,
       '计划'+RTRIM(COUNT(1)) AS 计划数,
       '完成'+RTRIM(SUM(   CASE
                  WHEN c_CompleteDate IS NOT NULL THEN
                      1
                  ELSE
                      0
              END
          )) AS 完成数
FROM #T GROUP BY c_ServiceType