CODEWITHSUNDEEP
sqlsql-servercount
Alex
Alex

Reputation: 2780

Count future occurrences of a value in SQL Server

I have two tables. One table has distinct id. I will call it distinct

id  date
---------------
a   2017-01-01
b   2016-01-01
c   2017-01-01

The other table is a log of all occurrences of an id. I will call it log

id    date
-----------------
a     2017-01-01
a     2016-01-01
b     2017-01-01
c     2017-01-01
b     2016-01-01
b     2015-01-01

The table I want is the count of all ids on and after their date listed in distinct. In this toy example the result would be the following:

id  count
-----------
a     1    -- on or after 2017-01-01
b     2    -- on or after 2016-01-01
c     1    -- on or after 2017-01-01

Upvotes: 0

Views: 50

Answers (2)

Gaurav
Gaurav

Reputation: 1109

Use below code :- 

select d.id, count(*) as cnt
from distinct d 
join log l 
on d.id = l.id
where l.date >= d .date
group by d.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

I think this is a join and group by:

select d.id, count(l.id)
from distinct d left join
     log l
     on l.id = d.id and l.date >= d.date
group by d.id;

Upvotes: 1

Related Questions