Help with calculation

1. Member
Join Date
Dec 1969
Posts
96

## Help with calculation

I have posted quite a few times on this subject so this time I am going to stop confusing everyone and repost from the beginning.<BR><BR>I have 2 tables (tbl_subscriber and tbl_dependents) and the subscriber has a one-to-many relationship with the dependents. The data that I need to get is as follows:<BR><BR>I need to know how many subscribers there are that have only 1 dependent associated with them and if either one (subscriber or dependent) is over 70 years of age. I also need this information for the subscribers that have more than 1 dependent associated. Below is the code that I am using. The code was provided to me from an earlier post. The problem that is happening it&#039;s returning the wrong number of records and I think the reason is my code is messed up. Hopefully someone can take a look and see what I am doing wrong.<BR>Thanks in advance<BR><BR>&#060;-----CODE-----&#062;<BR>select count(*) as subid,&#039;Single 70 or over&#039;<BR>from (select ts.subid from tbl_subscriber ts inner join tbl_dependents td on ts.subid = td.subid<BR>where purchdate between &#039;06/01/2002&#039; and &#039;06/30/2002&#039; and (ts.dob&#060;=&#039;07/19/1933&#039; or td.dob&#060;=&#039;07/19/1933&#039;)<BR>group by ts.subid having count(*) = 1) as s<BR>union<BR>select count(*) as subid,&#039;Family 70 or over&#039;<BR>from (select ts.subid from tbl_subscriber ts inner join tbl_dependents td on ts.subid = td.subid<BR>where purchdate between &#039;06/01/2002&#039; and &#039;06/30/2002&#039; and (ts.dob&#060;=&#039;07/19/1933&#039; or td.dob&#060;=&#039;07/19/1933&#039;)<BR>group by ts.subid having count(*) &#062; 1) as q

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## I think you need to ask...

...Lars or Dutch, whoever it was that gave you this.<BR><BR>But what I would try first would be just executing the inner parts of that query, separately, to see what you get. If those results aren&#039;t right, then of course the counts won&#039;t be right.<BR><BR>For example, try just this:<BR><BR>select ts.subid from tbl_subscriber ts inner join tbl_dependents td <BR>on ts.subid = td.subid<BR>where purchdate between &#039;06/01/2002&#039; and &#039;06/30/2002&#039; <BR>and (ts.dob&#060;=&#039;07/19/1933&#039; or td.dob&#060;=&#039;07/19/1933&#039;)<BR>group by ts.subid <BR>having count(*) = 1<BR><BR>And this:<BR><BR>select ts.subid from tbl_subscriber ts inner join tbl_dependents td <BR>on ts.subid = td.subid<BR>where purchdate between &#039;06/01/2002&#039; and &#039;06/30/2002&#039; <BR>and (ts.dob&#060;=&#039;07/19/1933&#039; or td.dob&#060;=&#039;07/19/1933&#039;)<BR>group by ts.subid <BR>having count(*) &#062; 1<BR><BR>See what those give you. If they aren&#039;t right, then tell us (well, tell Lars?) why and what you are getting that isn&#039;t right.<BR><BR>But why 1933???<BR><BR>That&#039;s only 69 years ago, not 70 years.<BR><BR>

3. Member
Join Date
Dec 1969
Posts
96

## RE: I think you need to ask...

Thanks Bill, I haven&#039;t seen Lars post since day before yesterday so I figured he is not in or didn&#039;t get a chance to see my post. I am going to try these items you gave me and see what I get. By the way, you are right that is only 69. That is the calculation for those under 70 (not 70 or under). I should have read my post closer.<BR><BR>Thanks for the help,.....Again. You guys are awesome.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## No, you have it wrong!

Look at that again!<BR><BR>That is asking for individuals with <BR> dob &#060;= &#039;7/19/1933&#039;<BR><BR>So an individual with a dob of 3/15/1988 will be GREATER than 7/19/1933 and will *not* be found.<BR><BR>The query, as written, is finding individuals age 69 *and older*.<BR><BR>Maybe that&#039;s your problem?<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•