SQL Query - Determining new visitors each day

Question

I have a schema which looks as follows (simplified):

CREATE TABLE MyTable (
   DateTimeOffset HitDate NOT NULL,
   IpAddress varchar(15)
)

A sample row might look as follows:

'7/10/2013 8:05:29 -07:00' '111.222.333.444'

I'm trying to work out a query that will give me, for each day (e.g., 7/10/2013) the number of unique IpAddresses. Actually, that part is fairly easy and I already created a query for that. However, for this query what I want is the number of unique IpAddresses that had never existed before the current date. I don't care about after the date, just before the date.

For example, assume I have the following data and this is all I have:

'7/10/2013 8:05:29 -07:00' '111.222.333.444'
'7/10/2013 12:05:29 -07:00' '111.222.333.222'
'7/9/2013 9:05:29 -07:00' '111.222.333.444'
'7/9/2013 10:05:29 -07:00' '111.222.333.555'
'7/8/2013 11:05:29 -07:00' '111.222.333.222'
'7/8/2013 4:05:29 -07:00' '111.222.333.555'

The query should output the following:

'7/8/2013' 2 (neither IpAddress existed before this date so both are new)
'7/9/2013' 1 (only one of the IpAddresses is new - the one ending in '444')
'7/10/2013' 0 (both IpAddresses had existed before this date)

Target database is SQL Server 2012. I'm offering a bounty of 100 points to the first person to correctly create a SQL statement.

Answer 1

CREATE TABLE #MyTable (
HitDate DateTimeOffset NOT NULL,
IpAddress varchar(15))

insert #mytable values
('7/10/2013 8:05:29 -07:00', '111.222.333.444'),
('7/10/2013 12:05:29 -07:00', '111.222.333.222'),
('7/9/2013 9:05:29 -07:00' ,'111.222.333.444'),
('7/9/2013 10:05:29 -07:00', '111.222.333.555'),
('7/8/2013 11:05:29 -07:00', '111.222.333.222'),
('7/8/2013 4:05:29 -07:00', '111.222.333.555')

;WITH a AS
(
 select 
 cast(HitDate as date) HitDate,  
 IpAddress
 from #mytable
), b AS
(
 SELECT  min(HitDate) md, IpAddress
 FROM a
 GROUP BY IpAddress
)
SELECT c.HitDate, Count(distinct b.IpAddress) IpAddress
FROM b
right join (select distinct HitDate from a) c
on b.md = c.HitDate
GROUP by c.HitDate

Result:

HitDate    IpAddress
2013-07-08 2
2013-07-09 1
2013-07-10 0