To Subquery or Not

[rinova]

I'm working on a query the will produce counts for 4 different Tiers. Here is the following 4 Tiers.

Employee (Member with no Child(ren) or Spouse):
Member Status is Active, Member Group# is 205, Member Job Code is not an Admin, Health Coverage Type is Individual, Member has no Dependent(s)

Employee and Spouse (Member with Spouse no Child(ren)):
Member Status is Active, Member Group# is 205, Member Job Code is not an Admin, Health Coverage Type is Family, Member has an active Spouse and no active Dependent(s)

Employee with Child(ren) but No Spouse (Member with Child(ren) no Spouse):
Member Status is Active, Member Group# is 205, Member Job Code is not an Admin, Health Coverage Type is Family, Member with active Dependent(s) and no active Spouse

Employee with Child(ren) and Spouse (Member with Child(ren) and Spouse):
Member Status is Active, Member Group# is 205, Member Job Code is not an Admin, Health Coverage Type is Family, Member with an active Spouse and Dependent(s)

I am currently using the following tables to accomplish this task.

dbo_partfile
dbo.depfile

The problem I'm having is figuring out how to get a members (record) with only a eligible Husband or Wife (dependent) but the member must not have any other eligible dependent (Son or Daughter or stepchild or etc...)

Should I use a subquery or am I making this more complicated than it has to be?

Here what I have so far:

SELECT [PART = Active Members].prt_ss_nbr, dbo_depfile.dep_depend_ssn, [PART = Active Members].prt_last_dep_nbr, dbo_depfile.dep_seq_nbr, dbo_depfile.dep_relation, [PART = Active Members].ps_cov_type, dbo_depfile.dep_term_date, dbo_depfile.dep_term_flag
FROM [PART = Active Members] LEFT JOIN dbo_depfile ON [PART = Active Members].prt_ss_nbr = dbo_depfile.dep_ss_nbr
WHERE (((dbo_depfile.dep_relation)<>"A" And (dbo_depfile.dep_relation)<>"D" And (dbo_depfile.dep_relation)<>"F" And (dbo_depfile.dep_relation)<>"G" And (dbo_depfile.dep_relation)<>"S" And (dbo_depfile.dep_relation)<>"X"));

or should I use

SELECT [PART = Active Members].prt_ss_nbr, dbo_depfile.dep_depend_ssn, [PART = Active Members].prt_last_dep_nbr, dbo_depfile.dep_seq_nbr, dbo_depfile.dep_relation, [PART = Active Members].ps_cov_type, dbo_depfile.dep_term_date, dbo_depfile.dep_term_flag
FROM [PART = Active Members] LEFT JOIN dbo_depfile ON [PART = Active Members].prt_ss_nbr = dbo_depfile.dep_ss_nbr
(SELECT [PART = Active Members].prt_ss_nbr, dbo_depfile.dep_depend_ssn, [PART = Active Members].prt_last_dep_nbr, dbo_depfile.dep_seq_nbr, dbo_depfile.dep_relation, [PART = Active Members].ps_cov_type, dbo_depfile.dep_term_date, dbo_depfile.dep_term_flag
FROM [PART = Active Members] LEFT JOIN dbo_depfile ON [PART = Active Members].prt_ss_nbr = dbo_depfile.dep_ss_nbr
WHERE (((dbo_depfile.dep_relation)<>"A" And (dbo_depfile.dep_relation)<>"D" And (dbo_depfile.dep_relation)<>"F" And (dbo_depfile.dep_relation)<>"G" And (dbo_depfile.dep_relation)<>"S" And (dbo_depfile.dep_relation)<>"X"));

Thank you all for any help with this.

-Rinova

 

[plog]

Those statments are just too complex of for me to wade through. However, there are few things I don't see that leads me to believe its not going to produce what you want.

1. Neither of those queries is an aggregate query, so they aren't going to produce just the data you want.

2. I don't see a conditional statement or any other code that puts your data into these 'Tiers' you have described.

In general, which is all I can do without knowing what or where your data is, this is how I would get the results you want.

Create a query based on your dependents table to put all the employees you have in there into into a Tier. Then I would use the employee table and LEFT JOIN that query to it, apply all the criteria that needs to be applied to your employee table (i.e. Member Group #205, Member Job code is not 'Admin', etc) and make that an aggregate query that counts your employees and groups them by their Tiers.

For specific help, post your tables' structures and some sample data from each.

 

[vbaInet]

And please explain what each criteria actually signify. "G", "A", "D", "S", "X" and "F" don't mean much to us.

 

[Cerial]

I'm guessing that a non-spouse dependent has a dep_relation code of A, D, F, G, S, or X. Based on that, I would do this:
SELECT [fields]
FROM (partfile INNER JOIN depfile AS Spouse ON partfile.prt_ss_nbr = Spouse.dep_ss_nbr AND Spouse.dep_relation = [Spouse code])
WHERE partfile.prt_ss_nbr NOT IN (SELECT dep_ss_nbr FROM depfile AS NonSpouse WHERE dep_ss_nbr = partfile.prt_ss_nbr AND dep_relation IN ('A', 'D', 'F', 'G', 'S', 'X'))

So you're basically saying that the member MUST have a spouse (hence the INNER JOIN), but MUST NOT have a non-spouse dependent (hence the NOT IN).

On a side note, your initial description seemed to contradict itself, because from the business requirements you identify "an active Spouse and no active Dependent(s)" -- as if a Spouse was not a Dependent, and then in your implementation you use "a eligible Husband or Wife (dependent)" -- putting Spouses and Dependents all in the same table.

 

[rinova]

I apologize for the misinderstanding on my part. The query you see in my original post is written to get a count for Employee and Spouse tier only. I am writing 4 separate queries one for each tier. As for the dependent relationship code A=adopted child(ren), F=foster child(ren), D=daughter, G=grandchild(ren), X=stepchild(ren) and S=son

I also noticed I pasted a query using the PART = Active Members query results. I decided to capture all eligible records first and use the results of that query to create the 4 separate tiers.

Thank you so much for replying to my post plog, vbaInet and Cerial

Posted from my iPhone

 

[rinova]

I'm guessing that a non-spouse dependent has a dep_relation code of A, D, F, G, S, or X. Based on that, I would do this:
SELECT [fields]
FROM (partfile INNER JOIN depfile AS Spouse ON partfile.prt_ss_nbr = Spouse.dep_ss_nbr AND Spouse.dep_relation = [Spouse code])
WHERE partfile.prt_ss_nbr NOT IN (SELECT dep_ss_nbr FROM depfile AS NonSpouse WHERE dep_ss_nbr = partfile.prt_ss_nbr AND dep_relation IN ('A', 'D', 'F', 'G', 'S', 'X'))

So you're basically saying that the member MUST have a spouse (hence the INNER JOIN), but MUST NOT have a non-spouse dependent (hence the NOT IN).

On a side note, your initial description seemed to contradict itself, because from the business requirements you identify "an active Spouse and no active Dependent(s)" -- as if a Spouse was not a Dependent, and then in your implementation you use "a eligible Husband or Wife (dependent)" -- putting Spouses and Dependents all in the same table.

Your right I should have not categorized husband and wife as dependent in my original post. I'm so used to doing that because the husband and wife records are found in the dbo.depfile table AKA the dependent table. Thanks for pointing that out it will help me if I ever have to explain this ever again.

 

[rinova]

I'm guessing that a non-spouse dependent has a dep_relation code of A, D, F, G, S, or X. Based on that, I would do this:
SELECT [fields]
FROM (partfile INNER JOIN depfile AS Spouse ON partfile.prt_ss_nbr = Spouse.dep_ss_nbr AND Spouse.dep_relation = [Spouse code])
WHERE partfile.prt_ss_nbr NOT IN (SELECT dep_ss_nbr FROM depfile AS NonSpouse WHERE dep_ss_nbr = partfile.prt_ss_nbr AND dep_relation IN ('A', 'D', 'F', 'G', 'S', 'X'))

Working on the suggestion you provided I'll let you know if it work for me. Thanks

 

[rinova]

I Should have started this post asking for this:

I have a Table called Dependents and a field called Relationship.

The Relationship field has the following codes: A=adopted child(ren) F=foster child(ren) D=daughter(s) G=grandchild(ren) X=stepchild(ren) S=son(s) H=Husband W=Wife

I have another Table Called Participants and they can be linked to the Dependents table using the Participants.prt_ss_nbr and Dependents.dep_ss_nbr fields.

I'm trying to write a query that will retrieve participants who only have a H or W in the relationship column and not other codes

Example:
Participant Relationship Code
Participant1 H
Participant1 S
Participant1 D
Participant2 H
Pariticipant3 W
Pariticipant3 S
Participant4 W

The query should retrieve only Participant 2 and 4

How do I do this? What else would you need to know to help me out with this problem I have.

Thank you,

-Rinova

 

[plog]

You will need a sub-query to identify those participants that have relationship codes other than H and W. Then you will take that and bump it against your table to find out which participants remain (only have H or W) This SQL will do it in one pass:

SELECT Participants.Participant
FROM Participants LEFT JOIN
    (SELECT Participants.Participant, Participants.[Relationship Code]
     FROM Participants
     WHERE (((Participants.[Relationship Code])<>"H" And (Participants.[Relationship Code])<>"W")))
     AS ExcludedParticipants
ON Participants.Participant = ExcludedParticipants.Participant
WHERE (((ExcludedParticipants.Participant) Is Null))
GROUP BY Participants.Participant;

 

[rinova]

I Should have started this post asking for this:

I have a Table called Dependents and a field called Relationship.

The Relationship field has the following codes: A=adopted child(ren) F=foster child(ren) D=daughter(s) G=grandchild(ren) X=stepchild(ren) S=son(s) H=Husband W=Wife

I have another Table Called Participants and they can be linked to the Dependents table using the Participants.prt_ss_nbr and Dependents.dep_ss_nbr fields.

I'm trying to write a query that will retrieve participants who only have a H or W in the relationship column and not other codes

Example:
Participant Relationship Code
Participant1 H
Participant1 S
Participant1 D
Participant2 H
Pariticipant3 W
Pariticipant3 S
Participant4 W

The query should retrieve only Participant 2 and 4

How do I do this? What else would you need to know to help me out with this problem I have.

Thank you,

-Rinova

I was able to get the below query to do what I wanted but now I have a new dilemma.

SELECT 
ActMbr.prt_ss_nbr, 
Spouse.dep_depend_ssn, 
ActMbr.prt_last_dep_nbr, 
Spouse.dep_seq_nbr, 
Spouse.dep_relation, 
ActMbr.ps_cov_type, 
Spouse.dep_term_date, 
Spouse.dep_term_flag
FROM [PART = Active Members] 
AS ActMbr 
LEFT JOIN dbo_depfile 
AS Spouse 
ON ActMbr.prt_ss_nbr = Spouse.dep_ss_nbr
WHERE NOT EXISTS
   (SELECT dbo_depfile.dep_relation                         
   FROM dbo_depfile                                    
   WHERE dbo_partfile.prt_ss_nbr = dbo_depfile.dep_ss_nbr     
    AND (((dbo_depfile.dep_relation)="A" Or (dbo_depfile.dep_relation)="D" Or (dbo_depfile.dep_relation)="F" Or (dbo_depfile.dep_relation)="G" Or (dbo_depfile.dep_relation)="S" Or (dbo_depfile.dep_relation)="X")));

The above code (query) is doing exactly what I told it but I didn't foresee this new problem. I need to include Participants who have terminated/ineligible children. The above code is excluding them.

The dependent table has a column called terminated date and another column called terminated flag. These two field are populated when a dependent is terminated. How can I use these columns to fix this problem?

Any help will be appreciated

 

[vbaInet]

It's a two step process.

First you create a query to return a count of those Participants that don't have a RelationshipCode of H or W (aircode):

SELECT ParticipantID
FROM ParticipantsTable
WHERE RelationshipCode NOT IN ('H', 'W')
GROUP BY ParticipantID

Secondly, you join the query above to your original table but this time via a LEFT JOIN from the original table to the query above. Next you put a criteria of Is Null under the PariticpantID of the query in step 1.

Edit: I didn't see your last two posts so the above is in response to the one prior to those.

 

[rinova]

Those statments are just too complex of for me to wade through. However, there are few things I don't see that leads me to believe its not going to produce what you want.

1. Neither of those queries is an aggregate query, so they aren't going to produce just the data you want.

2. I don't see a conditional statement or any other code that puts your data into these 'Tiers' you have described.

In general, which is all I can do without knowing what or where your data is, this is how I would get the results you want.

Create a query based on your dependents table to put all the employees you have in there into into a Tier. Then I would use the employee table and LEFT JOIN that query to it, apply all the criteria that needs to be applied to your employee table (i.e. Member Group #205, Member Job code is not 'Admin', etc) and make that an aggregate query that counts your employees and groups them by their Tiers.

For specific help, post your tables' structures and some sample data from each.

I created a query that gave me the participants who meet all criteria (i.e. Member Group #205, Member Job code is not 'Admin', etc) I called it [PART = Active Members].

 

[vbaInet]

Edit: I didn't see your last two posts so the above is in response to the one prior to those.

Neither did I see plog's post.

 

[rinova]

You will need a sub-query to identify those participants that have relationship codes other than H and W. Then you will take that and bump it against your table to find out which participants remain (only have H or W) This SQL will do it in one pass:

SELECT Participants.Participant
FROM Participants LEFT JOIN
    (SELECT Participants.Participant, Participants.[Relationship Code]
     FROM Participants
     WHERE (((Participants.[Relationship Code])<>"H" And (Participants.[Relationship Code])<>"W")))
     AS ExcludedParticipants
ON Participants.Participant = ExcludedParticipants.Participant
WHERE (((ExcludedParticipants.Participant) Is Null))
GROUP BY Participants.Participant;

I will try this and let you know if it works.

 

[vbaInet]

And if plog permits me to amend his code slightly

SELECT Participants.Participant
FROM Participants LEFT JOIN
    (
     SELECT Participants.Participant
     FROM Participants
     WHERE Participants.[Relationship Code] NOT IN ("H","W")
     GROUP Participants.Participant
    )
     AS ExcludedParticipants
ON Participants.Participant = ExcludedParticipants.Participant
WHERE (((ExcludedParticipants.Participant) Is Null))
GROUP BY Participants.Participant;

... just so the subquery returns distinct values.

 

[plog]

6 and one half dozen of the other. The main query Grouped By Participant so they were going to be unique in the end.

 

[vbaInet]

Yes plog, but you and I know that it's still best to limit the subquery to the least number of records possible.

 

[plog]

Yes plog, but you and I know that it's still best to limit the subquery to the least number of records possible.

Actually I didn't (and don't) and I am sure the main poster didn't as well. Without the 'why' it seems you are posting code without purpose.

Now we know why you amended code last time, however, we still don't know why its best to limit subquery to the least number of records. Do tell.

 

[vbaInet]

Pretty straightforward really, the less records you're joining against (especially via an Outter Join) the quicker your query will run.

 

[rinova]

Here is my new issue. should I create a new POST of continue with this thread?

I call this query Pariticipants and Spouse only:

I need to get a count of Pariticipants who meet the following criteria:
Pariticipants must have an Eligible spouse.
Pariticipants can have children but none of the children can be eligible.
Pariticipants can have no children
I'm using two table to create this query. The Participant and Dependent table.
The Dependents Table includes all spouses and children.
I identify spouses with the relationship code of "H" or "W"
I identify children with the relationship code of "A" or "D" or "F" or "G" or "S" or "X"
I identify terminated children with a terminated flag of "T" or "M" or "U" or "N" or "S" or "D" or "X" or "A" or "Y" or "O" or "H"

The problem I have is being able to retrieve a Participant with an Eligible spouse and children who don't exist or are all terminated.

Here is an example of what I am trying to do:

PART_SSN+ DEP_SSN__ + RELATION_CODE + TERM_FLAG +
---------_+------------+-----------------+------------+
Part_SSN1 + DEP_SSN01 +________H_____+__________+
---------_+------------+-----------------+------------+
Part_SSN1 + DEP_SSN02 +________S_____+_____T____+
---------_+------------+-----------------+------------+
Part_SSN1 + DEP_SSN03 +________D_____+_____A____+
---------_+------------+-----------------+------------+
Part_SSN2 + DEP_SSN04 +________W_____+_____T____+
---------_+------------+-----------------+------------+
Part_SSN2 + DEP_SSN05 +________F______+_____T____+
---------_+------------+-----------------+------------+
Part_SSN2 + DEP_SSN06 +________S______+_____A____+
---------_+------------+-----------------+------------+
Part_SSN3 + DEP_SSN07 +________H______+__________+
---------_+------------+-----------------+------------+
Part_SSN4 + DEP_SSN08 +________W______+__________+
---------_+------------+-----------------+------------+
Part_SSN4 + DEP_SSN09 +________D______+__________+
---------_+------------+-----------------+------------+
Part_SSN4 + DEP_SSN10 +________G______+__________+
---------_+------------+-----------------+------------+
Part_SSN5 + DEP_SSN11 +________H______+_____T____+
---------_+------------+-----------------+------------+

When the query completes the count should be 2. Participant_SSN1 + Participant_SSN3

With the code below I was trying to accomplish this but I kept retrieving Participants with terminated children but not all the children were terminated.

SELECT 
ActMbr.prt_ss_nbr, 
ActMbr.prt_first, 
ActMbr.prt_last, 
Dependents.dep_depend_ssn, 
Dependents.dep_relation, 
Dependents.dep_term_flag
FROM [PART = Active Members] 
AS ActMbr
LEFT JOIN dbo_depfile 
AS Dependents
ON ActMbr.prt_ss_nbr = Dependents.dep_ss_nbr
WHERE (((Dependents.dep_relation)="A" 
Or (Dependents.dep_relation)="D" 
Or (Dependents.dep_relation)="F" 
Or (Dependents.dep_relation)="G" 
Or (Dependents.dep_relation)="S" 
Or (Dependents.dep_relation)="X") 
AND ((Dependents.dep_term_flag) Is Not Null)) 
OR (((Dependents.dep_relation)="H" 
Or (Dependents.dep_relation)="W") 
AND ((Dependents.dep_term_flag) Is Null));

What will you need to know about my database to help me create a query that accomplish this task.