34,838
社区成员




select 费用名称,min(开始时间) as 开始时间,max(结束时间) as 结束时间,
COUNT(1) as 月数,MAX(单价) as 单价,MAX(数量),SUM(实收金额) as 实收金额
from
(select *,DATEDIFF(MONTH,'1900-01-01',开始时间) as diff,
ROW_NUMBER() over (partition by 费用名称 order by 开始时间) as seq
from table) as A
group by 费用名称,diff-seq
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#Tab1') is null
drop table #Tab1
Go
Create table #Tab1(费用名称 nvarchar(23),开始时间 Date,结束时间 Date,单价 INT,数量 int,实收金额 decimal(18,7))
Insert #Tab1
select N'物业费','2018-1-1','2018-1-30',1,88,0.6 union all
select N'物业费','2018-2-1','2018-2-28',1,88,0.6 union all
select N'物业费','2018-4-1','2018-4-30',1,88,0.6 union all
select N'停车费','2018-1-1','2018-1-30',NULL,1,250 union all
select N'停车费','2018-2-1','2018-2-28',NULL,1,250 union all
select N'停车费','2018-3-1','2018-3-30',NULL,1,250
GO
;WITH CTED
AS
(
SELECT *
, CASE WHEN 开始时间=LAG(DATEADD(mm,1,开始时间),1,开始时间) OVER (PARTITION BY 费用名称 ORDER BY 开始时间) THEN 1 ELSE 0 END AS Flag
,ROW_NUMBER()OVER(PARTITION BY 费用名称 ORDER BY 开始时间) AS RN
FROM #Tab1
)
SELECT 费用名称
, 单价
, MIN(开始时间) AS 开始日期
, MAX(结束时间) AS 结束日期
, COUNT(*) AS 月份
, SUM(数量) AS 数量
, SUM(实收金额) AS 实收金额
FROM
(SELECT *
, RN-ROW_NUMBER() OVER (PARTITION BY 费用名称, Flag ORDER BY RN) AS Grp
FROM CTED) AS T
GROUP BY 费用名称
, 单价
, T.Grp;
/*
费用名称 单价 开始日期 结束日期 月份 数量 实收金额
停车费 NULL 2018-01-01 2018-03-30 3 3 750.0000000
物业费 1 2018-01-01 2018-02-28 2 176 1.2000000
物业费 1 2018-04-01 2018-04-30 1 88 0.6000000
*/