[I have been asked to create a report that identifies whether a household consists of the following:
SF (single female), SM (single male), JH (joint household), SF w/child, SM w/child, JH w/child.
I am not sure of how to write the case statement for JH, SF w/child, SM w/child, JH w/child. All of these types of households will have an additional record(s) (hm.imembernum > 1) on the hmfamily (hm) table.
For a JH, the additional household member will have a hm.srelation of 'S' (Spouse), 'C' (Co-Head) or 'O' (Other) and a hm.imembernum > 1 (the Head of Household 'H' is always hm.imembernum = 1).
For SF w/child, SM w/child or JH w/child households, there will be a record with a hm.srelation of 'D' (Dependent).
Below is my attempt at the case statement but I know that my logic is not accurate. Do I need an exists clause?
select h.hverify,
max(case
when h.imemcounted = 1
then case
when hm.srelation = 'H' and hm.ssex = 'F' then 'SF'
when hm.srelation = 'H' and hm.ssex = 'M' then 'SM'
end
when h.imemcounted = 2
then case
when hm.imembernum = 1 and hm.srelation = 'H' and hm.ssex = 'F' and hm.imembernum > 1 and hm.srelation = 'D' then 'SF w child'
when hm.imembernum = 1 and hm.srelation = 'H' and hm.ssex = 'M' and hm.imembernum > 1 and hm.srelation = 'D' then 'SM w child'
end
end) as HHDemographcs
from hmsumm h,
hmfamily hm
where h.hverify = hm.hsumm
and h.hmy = 337319
group by h.hverify
I believe I need to look at the hm.ssex of the Head of Household (hm.srelation = 'H', hm.imembernum = 1) when there is a Dependent (hm.srelation = 'H', hm.imembernum > 1) to determine if it's a SF w/child or SM w/child.
Also, I believe I need determine if there is a 'S' (Spouse), 'C' (Co-Head) or 'O' (Other) (hm.srelation in ('S', 'C', 'O'), hm.imembernum > 1) for a JH as well as if there is also a Dependent (hm.srelation = 'D', hm.imembernum > 1) to determine JH w/child.
Thanks in advance for the help!!](http://)