CODEWITHSUNDEEP
sqlsql-server
Khalil Rumman
Khalil Rumman

Reputation: 557

Count of active records between date range

I have two tables, like:

First Table (Users)

Id    Name   Active
-------------------
1     X      1
2     Y      1
3     Z      1
4     X1     0

and the second table (Records)

Id    UserId    Date
---------------------------
001   1         2015/09/19
002   1         2015/09/20
003   1         2015/09/09
004   2         2015/09/15
005   4         2015/09/20

From these tables I need to get the following result - if I filter on Date between 2015/09/21 and 2015/09/10 and Active=1 the result that I need like this

UserId       Name     Count
---------------------------
3            Z        0
2            Y        1
1            X        2

Count is the number of records in table Records in the selected range date and active=1

Upvotes: 4

Views: 2211

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

So first you have to join both the tables based on id .

Please do note that this is left join because we want 0 count values.

Apply the active criteria after this as where clause and before group by. Use the SUM aggregate function with case logic as we want 0 count values. 

select 
  u.id as [UserId], 
  u.Name as [Name],  
  sum(case when r.[Date] between '2015/09/10' and '2015/09/21' then 1 else 0 end) as [count]

from [users] u left join [records] r
  on u.Id =r.UserId 
where u.active=1
group by u.Id, u.Name

demo sql fiddle: http://sqlfiddle.com/#!6/b9c3a/12 demo output:

UserId  Name    count
1   X   2
2   Y   1
3   Z   0

Upvotes: 3

Related Questions