Search for data on a specific date

Question

I have a table that stores entries for the user

This includes the columns username , FromDate , ToDate , Status

I have a requirement when I need to find the number of active users (status) on 1 Dec 2010 and on 1 April 2011 .

In other words, we are trying to determine how many users were active 1 Dec 2010 and 1 Apr 2011 , so that we can compare how many users were added / lost.

How can I do that?

Answer 1

Users who were active both on December 1 and April 1:

 with cte as ( select UserName, FromDate, ToDate from MyTable where Status = 'Active' -- adjust to your status type ) select distinct UserName from cte where FromDate < '20101202' and ToDate >= '20101201' and UserName in ( select * from cte where FromDate < '20110402' and ToDate >= '20110401' ) 

To find out which users have been added and which users have been deleted, you can do something like this:

 with cte as ( select UserName, FromDate, ToDate from MyTable where Status = 'Active' -- adjust to your status type ) select UserName, sum(WasActiveOnDecFirst) WasActiveOnDecFirst, sum(WasActiveOnAprFirst) WasActiveOnAprFirst from ( select isnull(du.UserName, au.UserName) UserName, case when du.UserName is null then 0 else 1 end WasActiveOnDecFirst, case when au.UserName is null then 0 else 1 end WasActiveOnAprFirst from ( select distinct UserName from cte where FromDate < '20101202' and ToDate >= '20101201' ) du full join ( select distinct UserName from cte where FromDate < '20110402' and ToDate >= '20110401' ) au on du.UserName = au.UserName ) tt group by UserName with rollup 

The result will look like this:

 UserName WasActiveOnDecFirst WasActiveOnAprFirst -------------------- ------------------- ------------------- user1 1 1 user2 1 1 user3 1 1 user4 0 1 NULL 3 4 

The last line is the full user number.

Answer 2

 declare @FromDate date = '20101201' declare @ToDate date = '20110401' select count(case when @FromDate between FromDate and ToDate then 1 end) as CountFromDate, count(case when @ToDate between FromDate and ToDate then 1 end) as CountToDate from YourTable where FromDate <= @ToDate and ToDate >= @FromDate and Status = 1 

Answer 3

EDIT: Please contact Mikael Eriksson . It looks much better than the request I wrote.

If you have only two dates to compare, you can do something like this. Screenshot # 1 shows the output of the query below regarding sample data. Users 1 , 4 , 6 and 9 were active on 2010-12-01 . Users 1 , 3 , 4 , 5 , 7 and 8 were active on 2011-04-01 . Therefore, counter 4 and 6 . .

 DECLARE @Date1 DATETIME DECLARE @Date2 DATETIME SET @Date1 = '2010-12-01' SET @Date2 = '2011-04-01' SELECT @Date1 AS AsOnDate , COUNT(Username) AS ActiveCount FROM dbo.Users WHERE @Date1 BETWEEN FromDate AND ToDate UNION SELECT @Date2 AS AsOnDate , COUNT(Username) AS ActiveCount FROM dbo.Users WHERE @Date2 BETWEEN FromDate AND ToDate 

Hope this helps.

Screenshot # 1: