Comparing two lists to find new entries 1

[joebickley]

Hi

I have a table with 10000 applications in it each application is made by a person with an ID. I need to be able to find out how many of the people who made applications in the last 12 month period had never applied before.

Ive done this in MS access by making a qry for the old data and a query for this years data, joining them and counting the ones that dont match.

I want to do this in a Stored Proc alone if there is an easy way of doing this?

Thanks

joe

 

[nigelrivett]

select count(*) from
(
select tbl.ID
from tbl join (select distinct ID from tbl where date > dateadd(mm,-12,getdate()) tbl2
on tbl.ID = tbl2.ID
group by ID
having count(*) = 1
) a


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mimi2]

Hi nigelrivett ,
Would you mind to elaborate on your code.
I am having difficulties to follow.
Thanks a lot.

 

[nigelrivett]

(select distinct ID from tbl where date > dateadd(mm,-12,getdate()) tbl2

This gets the applications in the last 12 months.
It joins to the table on the ID - this will give a single entry if there is only one application for the ID but multiple if there is more than one.

group by ID
having count(*) = 1
will only take those with a single entry.

The select count(*) from just counts the entries from the derived table.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mimi2]

Thanks a lot.
1. I've never seen this kind of codeselect distinct ID from tbl where date > dateadd(mm,-12,getdate()) tbl2
I thought we needed the key word "join" to do that.

2. the " )a " at the end of the code, is it necessary ?

 

[nigelrivett]

1. You only need a join if you are joining tables. You can filter a single table with just a where clause.

2. It is a derived table so needs a name to reference it (even if it is the only table involved) hence "a" is the name it is given - could be more descriptive if you wish.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mimi2]

I am beginning to see the light !!!
Thanks again

 

[joebickley]

Hi thanks for your help. I neglected to mention that the years ran november to november and that i would pass in a year at random but i took your idea and worked on it.

heres the code for reference.


select count(*) from
(select distinct employeeid
from tblapplications
where applicationApprovedDate between '2003-11-01 00:00:00' and '2004-11-01 00:00:00'
) a
left join
(select distinct employeeid
from tblapplications
where applicationApprovedDate < '2003-11-01 00:00:00'
) b
on a.employeeid = b.employeeid
where b.employeeid is null


many thanks
Joe