Help with Case Statement

Transact-SQL
1

[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://)

2

Can you post the DDL (table structure) for your table and some sample data? Is there one row for each person? What column indicates that two people are members of the same family? Without a table structure and sample data, it is hard figure out those.

3

This should give you an idea of a way to approach this. You can add additional CASE conditions if needed, for example, hm_head_count of 1 but hm_cohead_count > 1, etc.

SELECT h.hverify,
    CASE WHEN hm_head_count > 1 THEN CASE WHEN hm_dependent_count > 0 THEN 'JH w child' ELSE 'JH' END
         WHEN hm_head_count = 1 THEN
             CASE WHEN hm_female_head_count > 0
                  THEN CASE WHEN hm_dependent_count > 0 THEN 'SF w child' ELSE 'SF' END
                  ELSE CASE WHEN hm_dependent_count > 0 THEN 'SM w child' ELSE 'SM' END END
        ELSE 'No'
        END AS household_type                       
FROM hmsumm h
LEFT OUTER JOIN (
    SELECT hsumm,
        SUM(CASE WHEN imembernum = 1 THEN 1 ELSE 0 END) AS hm_head_count,
        SUM(CASE WHEN imembernum = 1 AND ssex = 'F' THEN 1 ELSE 0 END) AS hm_female_head_count,
        SUM(CASE WHEN imembernum = 1 AND ssex = 'M' THEN 1 ELSE 0 END) AS hm_male_head_count,
        SUM(CASE WHEN srelation = 'S' THEN 1 ELSE 0 END) AS hm_spouse_count,
        SUM(CASE WHEN srelation = 'C' THEN 1 ELSE 0 END) AS hm_cohead_count,
        SUM(CASE WHEN srelation = 'D' THEN 1 ELSE 0 END) AS hm_dependent_count
    FROM hmfamily
    GROUP BY hsumm
) AS hm_summary ON hm_summary.hsumm = h.hverify