sql server - sql count statement with multiple date ranges -

i have 2 table different appointment dates.

table 1

id  start date 1   5/1/14 2   3/2/14 3   4/5/14 4   9/6/14 5   10/7/14 

table 2

id  start date 1   4/7/14 1   4/10/14 1   7/11/13 2   2/6/14 2   2/7/14 3   1/1/14 3   1/2/14 3   1/3/14 

if had set date ranges can count each appointment date fine need change date ranges. each id in table 1 need add distinct appointment dates table 2 6 months prior start date table 1. example: count distinct appointment dates id 1 (in table 2) appointment dates between 12/1/13 , 5/1/14 (6 months prior). result 2...4/7/14 , 4/10/14 within , 7/1/13 outside of 6 months.

so issue range changes each record , can not seem figure out how code this.for id 2 date range 9/1/14-3/2/14 , on.

thanks in advance!

try out:

 select id,         (             select  count(*)                table2               id = table1.id                 , table2.start_date >= dateadd(mm,-6,table1.start_date)         ) table2records    table1 

the dateadd subtracts 6 months date in table1 , subquery returns count of related records.