...but I can't get it to save my life. Check this out.

I've got 3 tables they have more fields in them but these are the ones I need to calculate the query. I tried to articulate the relations in the diagram as well.

tbl_subscriber

subid----------v

subnumber---|-----v

dob | |

tbl_depdendents | |

subid----------^ |

dob |

tlb_renewals |

subnumber---------^

renewaldate

I need to:

Enter a date range and it returns

1. total number of renewal within the range (I got this one already)

2. total number of renewals that have only 1 dependent in the tbl_dependents and BOTH of them are under 60 years old

3. total number of renewals that have only 1 dependent in the tbl_dependents and EITHER ONE is 60 or over

4. total number of renewals that have more than 1 dependent in the tbl_dependents and ALL of them are under 60 years old

5. total number of renewals that have more than 1 dependent in the tbl_dependents and ANY one is 60 or over

Sounds simple but I'm coming up with duplicates and my querries keep counting records that have mixed criteria such as if a record has someone who is over 60 and under 60 it counts them in both the under and over query.

HELP! Ready to hang myself.

Thanks in advance,

Tom

basic problem was i needed to use yy in the datediff for the years test

and i've changed the Case when slightly

I've joined the tables as you question suggested...

I've used a suquery for the subscriber table as we need to determine the subscribers age

and another subquery for the dependents table which calculates the numbers of dependents

and wether they are under or over 60 by the subid..

the select then gives the answers to you 5 conditions...

either use the @start/@end variables for the dates.

or plug in you required date range...

Select count(*) as Renewals

,sum(case when d.num = 1 and d.u60=1 and subage<60 then 1 else null end) as c2

,sum(case when d.num = 1 and (d.o60=1 or subage>=60) then 1 else null end) as c3

,sum(case when d.num > 1 and d.o60=0 then 1 else null end) as c4

,sum(case when d.num > 1 and d.o60>0 then 1 else null end) as c5

From

tbl_renewals as r

Inner Join

(select a.*,datediff(yy,dob,getdat

on R.subnumber=s.subnumber

left outer join

(

select subid

,sum(case when datediff(yy,dob,getdate())

,sum(case when datediff(yy,dob,getdate())

,count(*) as Num

from tbl_depdendents

group by subid

) as D

on S.Subid=D.Subid

Where renewaldate between @Start and @end