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
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