计算ids，但也计算重复ids时

Question

Server 2008。我需要计算我的id列，还需要计算日期超过90天时间段时重复的id。

表：

 ID        Date     
 101     01/01/2015
 101     01/02/2015
 101     04/01/2015
 201     01/01/2015
 201     01/03/2015
 301     05/01/2015
 401     06/01/2015
 401     07/01/2015
 401     07/02/2015

我想看到的是：

 ID       TotalCount    Counts&WithDateGaps
 101       3               2
 201       2               1
 301       1               1
 401       3               1

Answer 1

declare @t table(
id int,
datet date
)


insert into @t
select 101,'01/01/2015' union
select  101,'01/02/2015' union
select  101,'04/01/2015' union
select  201,'01/01/2015' union
select  201,'01/03/2015' union
select  301,'05/01/2015' union
select  401,'06/01/2015' union
select  401,'07/01/2015' union
select  401,'07/02/2015'

Select T.ID, T.Total, Coalesce(C.Total, 0) As CountDtGaps
From 
(Select Id, count(*) As Total From @t Group By Id) As T
Left Outer Join (Select Id, count(*) As Total From @t Where datet < dateadd(dd, -90, Cast(getdate() as date)) Group By Id) As C
On T.Id = C.Id

--PS. r u sure about results expected? 90 days ago.. all rows are selected.