Case Stmt or Group By Returning Three Results (Only Want One)

The following code is creating three instances for each tenant, I hope to only create one instance for each tenant (where 'ExtrLowIncLmt', 'VLowIncLmt' and 'LowIncLmt' are detailed on the same line). Is the issue with my case statements? Group by clause?

select b.saddr1 as 'PropName',
b.scity as 'PropCity',
b.sstate as 'PropState',
c.scode as 'UnitID',
c.ibedrooms as 'UnitSize',
d.scode as 'ResidentCode',
rtrim(d.sfirstname) + ' ' + rtrim(d.slastname) as 'HOHName',
a.dteffective as 'CertEffDate',
cast(a.scerttype as varchar(50)) as 'CertType',
case when a.itype = 2 then 'Tax Credit' else
case a.sSubsidyType
when 1 then 'Sec 8'
when 2 then 'Rent Supp'
when 3 then 'RAP'
when 4 then 'Sec 236'
when 5 then 'BMIR'
when 7 then 'Sec 202 PRAC'
when 8 then 'Sec 811 PRAC'
when 9 then 'Sec 202/162 PAC'
end end as 'SubType',
a.ifamsizeforinclimit as '#HHMem',
a.dtotannincome as 'CrntIncome',
CASE
WHEN a.iFamSizeForIncLimit = 01 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit1
WHEN a.iFamSizeForIncLimit = 02 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit2
WHEN a.iFamSizeForIncLimit = 03 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit3
WHEN a.iFamSizeForIncLimit = 04 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit4
WHEN a.iFamSizeForIncLimit = 05 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit5
WHEN a.iFamSizeForIncLimit = 06 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit6
WHEN a.iFamSizeForIncLimit = 07 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit7
WHEN a.iFamSizeForIncLimit = 08 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit8
WHEN a.iFamSizeForIncLimit = 09 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit9
WHEN a.iFamSizeForIncLimit = 10 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit10
WHEN a.iFamSizeForIncLimit = 11 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit11
WHEN a.iFamSizeForIncLimit = 12 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit12
WHEN a.iFamSizeForIncLimit = 13 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit13
WHEN a.iFamSizeForIncLimit = 14 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit14
WHEN a.iFamSizeForIncLimit = 15 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit15
WHEN a.iFamSizeForIncLimit = 16 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit16
END as 'ExtrLowIncLmt',
CASE
WHEN a.iFamSizeForIncLimit = 01 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit1
WHEN a.iFamSizeForIncLimit = 02 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit2
WHEN a.iFamSizeForIncLimit = 03 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit3
WHEN a.iFamSizeForIncLimit = 04 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit4
WHEN a.iFamSizeForIncLimit = 05 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit5
WHEN a.iFamSizeForIncLimit = 06 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit6
WHEN a.iFamSizeForIncLimit = 07 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit7
WHEN a.iFamSizeForIncLimit = 08 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit8
WHEN a.iFamSizeForIncLimit = 09 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit9
WHEN a.iFamSizeForIncLimit = 10 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit10
WHEN a.iFamSizeForIncLimit = 11 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit11
WHEN a.iFamSizeForIncLimit = 12 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit12
WHEN a.iFamSizeForIncLimit = 13 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit13
WHEN a.iFamSizeForIncLimit = 14 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit14
WHEN a.iFamSizeForIncLimit = 15 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit15
WHEN a.iFamSizeForIncLimit = 16 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit16
END as 'VLowIncLmt',
CASE
WHEN a.iFamSizeForIncLimit = 01 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit1
WHEN a.iFamSizeForIncLimit = 02 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit2
WHEN a.iFamSizeForIncLimit = 03 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit3
WHEN a.iFamSizeForIncLimit = 04 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit4
WHEN a.iFamSizeForIncLimit = 05 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit5
WHEN a.iFamSizeForIncLimit = 06 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit6
WHEN a.iFamSizeForIncLimit = 07 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit7
WHEN a.iFamSizeForIncLimit = 08 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit8
WHEN a.iFamSizeForIncLimit = 09 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit9
WHEN a.iFamSizeForIncLimit = 10 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit10
WHEN a.iFamSizeForIncLimit = 11 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit11
WHEN a.iFamSizeForIncLimit = 12 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit12
WHEN a.iFamSizeForIncLimit = 13 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit13
WHEN a.iFamSizeForIncLimit = 14 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit14
WHEN a.iFamSizeForIncLimit = 15 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit15
WHEN a.iFamSizeForIncLimit = 16 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit16
END as 'LowIncLmt',
case when a.dtotannincome then 'Tenant Qualifies as Low Income'
when a.scurrentincomestatus = 2 then 'Tenant Qualifies as Very Low Income'
when a.scurrentincomestatus = 3 then 'Tenant Qualifies as Extremely Low Income'
when a.scurrentincomestatus = ' ' then 'Not Section 8' end as 'HHIncLmt'
from MPI_LatestCompletedCertByProgram_View a,
property b
INNER JOIN WLIncLimitXRef wlr
ON b.hMy = wlr.hLink AND
wlr.iType = 3 AND wlr.b50059 = '-1'
INNER JOIN WLIncLimit wll
ON wlr.hIncLimit = wll.hMy
INNER JOIN WLincLimitDet wld
ON wll.hMy = wld.hIncLimit,
unit c,
hmunit c2,
tenant d
where a.hprop = b.hmy
and a.hprop = c.hproperty
and a.hunit = c.hmy
and a.hunit = c2.hunit
and a.hprop = d.hproperty
and a.htenant = d.hmyperson
and a.scerttype not in ('TM', 'MO')
and d.istatus <> 1
and getdate() BETWEEN wld.dtLimitEffective AND wld.dtLimitExpiration
and wll.iPercentIncomeLimit IN (30,50,80)
and b.scode = '420'
group by b.saddr1,b.scity,b.sstate,case when a.itype = 2 then 'Tax Credit' else
case a.sSubsidyType
when 1 then 'Sec 8'
when 2 then 'Rent Supp'
when 3 then 'RAP'
when 4 then 'Sec 236'
when 5 then 'BMIR'
when 7 then 'Sec 202 PRAC'
when 8 then 'Sec 811 PRAC'
when 9 then 'Sec 202/162 PAC'
end end,c.scode,c.ibedrooms,d.scode,rtrim(d.sfirstname) + ' ' + rtrim(d.slastname),a.dteffective,a.scerttype,a.ifamsizeforinclimit,
a.dtotannincome,
CASE
WHEN a.iFamSizeForIncLimit = 01 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit1
WHEN a.iFamSizeForIncLimit = 02 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit2
WHEN a.iFamSizeForIncLimit = 03 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit3
WHEN a.iFamSizeForIncLimit = 04 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit4
WHEN a.iFamSizeForIncLimit = 05 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit5
WHEN a.iFamSizeForIncLimit = 06 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit6
WHEN a.iFamSizeForIncLimit = 07 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit7
WHEN a.iFamSizeForIncLimit = 08 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit8
WHEN a.iFamSizeForIncLimit = 09 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit9
WHEN a.iFamSizeForIncLimit = 10 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit10
WHEN a.iFamSizeForIncLimit = 11 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit11
WHEN a.iFamSizeForIncLimit = 12 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit12
WHEN a.iFamSizeForIncLimit = 13 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit13
WHEN a.iFamSizeForIncLimit = 14 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit14
WHEN a.iFamSizeForIncLimit = 15 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit15
WHEN a.iFamSizeForIncLimit = 16 and wll.iPercentIncomeLimit = 30 THEN wld.cLimit16
END,
CASE
WHEN a.iFamSizeForIncLimit = 01 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit1
WHEN a.iFamSizeForIncLimit = 02 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit2
WHEN a.iFamSizeForIncLimit = 03 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit3
WHEN a.iFamSizeForIncLimit = 04 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit4
WHEN a.iFamSizeForIncLimit = 05 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit5
WHEN a.iFamSizeForIncLimit = 06 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit6
WHEN a.iFamSizeForIncLimit = 07 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit7
WHEN a.iFamSizeForIncLimit = 08 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit8
WHEN a.iFamSizeForIncLimit = 09 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit9
WHEN a.iFamSizeForIncLimit = 10 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit10
WHEN a.iFamSizeForIncLimit = 11 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit11
WHEN a.iFamSizeForIncLimit = 12 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit12
WHEN a.iFamSizeForIncLimit = 13 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit13
WHEN a.iFamSizeForIncLimit = 14 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit14
WHEN a.iFamSizeForIncLimit = 15 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit15
WHEN a.iFamSizeForIncLimit = 16 and wll.iPercentIncomeLimit = 50 THEN wld.cLimit16
END,
CASE
WHEN a.iFamSizeForIncLimit = 01 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit1
WHEN a.iFamSizeForIncLimit = 02 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit2
WHEN a.iFamSizeForIncLimit = 03 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit3
WHEN a.iFamSizeForIncLimit = 04 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit4
WHEN a.iFamSizeForIncLimit = 05 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit5
WHEN a.iFamSizeForIncLimit = 06 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit6
WHEN a.iFamSizeForIncLimit = 07 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit7
WHEN a.iFamSizeForIncLimit = 08 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit8
WHEN a.iFamSizeForIncLimit = 09 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit9
WHEN a.iFamSizeForIncLimit = 10 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit10
WHEN a.iFamSizeForIncLimit = 11 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit11
WHEN a.iFamSizeForIncLimit = 12 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit12
WHEN a.iFamSizeForIncLimit = 13 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit13
WHEN a.iFamSizeForIncLimit = 14 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit14
WHEN a.iFamSizeForIncLimit = 15 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit15
WHEN a.iFamSizeForIncLimit = 16 and wll.iPercentIncomeLimit = 80 THEN wld.cLimit16
END,
a.scurrentincomestatus

Without table definition and sample data, it is almost impossible to give a qualified answer.
My (wild) guess is, that you might get better result using max function on ExtrLowIncLmt, VLowIncLmt and LowIncLmt (remember to remove the sections from the group statement).
Something like this perhaps:

select b.saddr1 as PropName
      ,b.scity as PropCity
      ,b.sstate as PropState
      ,c.scode as UnitID
      ,c.ibedrooms as UnitSize
      ,d.scode as ResidentCode
      ,rtrim(d.sfirstname)+' '+rtrim(d.slastname) as HOHName
      ,a.dteffective as CertEffDate
      ,cast(a.scerttype as varchar(50)) as CertType
      ,case
          when a.itype=2 then 'Tax Credit'
          else
             case a.sSubsidyType
                when 1 then 'Sec 8'
                when 2 then 'Rent Supp'
                when 3 then 'RAP'
                when 4 then 'Sec 236'
                when 5 then 'BMIR'
                when 7 then 'Sec 202 PRAC'
                when 8 then 'Sec 811 PRAC'
                when 9 then 'Sec 202/162 PAC'
             end
       end as SubType
      ,a.ifamsizeforinclimit as [#HHMem]
      ,a.dtotannincome as CrntIncome
      ,max(case
              when wll.iPercentIncomeLimit=30
              then case
                      when a.iFamSizeForIncLimit=01 then wld.cLimit1
                      when a.iFamSizeForIncLimit=02 then wld.cLimit2
                      when a.iFamSizeForIncLimit=03 then wld.cLimit3
                      when a.iFamSizeForIncLimit=04 then wld.cLimit4
                      when a.iFamSizeForIncLimit=05 then wld.cLimit5
                      when a.iFamSizeForIncLimit=06 then wld.cLimit6
                      when a.iFamSizeForIncLimit=07 then wld.cLimit7
                      when a.iFamSizeForIncLimit=08 then wld.cLimit8
                      when a.iFamSizeForIncLimit=09 then wld.cLimit9
                      when a.iFamSizeForIncLimit=10 then wld.cLimit10
                      when a.iFamSizeForIncLimit=11 then wld.cLimit11
                      when a.iFamSizeForIncLimit=12 then wld.cLimit12
                      when a.iFamSizeForIncLimit=13 then wld.cLimit13
                      when a.iFamSizeForIncLimit=14 then wld.cLimit14
                      when a.iFamSizeForIncLimit=15 then wld.cLimit15
                      when a.iFamSizeForIncLimit=16 then wld.cLimit16
                   end
           end
          ) as ExtrLowIncLmt
      ,max(case
              when wll.iPercentIncomeLimit=50
              then case
                      when a.iFamSizeForIncLimit=01 then wld.cLimit1
                      when a.iFamSizeForIncLimit=02 then wld.cLimit2
                      when a.iFamSizeForIncLimit=03 then wld.cLimit3
                      when a.iFamSizeForIncLimit=04 then wld.cLimit4
                      when a.iFamSizeForIncLimit=05 then wld.cLimit5
                      when a.iFamSizeForIncLimit=06 then wld.cLimit6
                      when a.iFamSizeForIncLimit=07 then wld.cLimit7
                      when a.iFamSizeForIncLimit=08 then wld.cLimit8
                      when a.iFamSizeForIncLimit=09 then wld.cLimit9
                      when a.iFamSizeForIncLimit=10 then wld.cLimit10
                      when a.iFamSizeForIncLimit=11 then wld.cLimit11
                      when a.iFamSizeForIncLimit=12 then wld.cLimit12
                      when a.iFamSizeForIncLimit=13 then wld.cLimit13
                      when a.iFamSizeForIncLimit=14 then wld.cLimit14
                      when a.iFamSizeForIncLimit=15 then wld.cLimit15
                      when a.iFamSizeForIncLimit=16 then wld.cLimit16
                   end
           end
          ) as VLowIncLmt
      ,max(case
              when wll.iPercentIncomeLimit=80
              then case
                      when a.iFamSizeForIncLimit=01 then wld.cLimit1
                      when a.iFamSizeForIncLimit=02 then wld.cLimit2
                      when a.iFamSizeForIncLimit=03 then wld.cLimit3
                      when a.iFamSizeForIncLimit=04 then wld.cLimit4
                      when a.iFamSizeForIncLimit=05 then wld.cLimit5
                      when a.iFamSizeForIncLimit=06 then wld.cLimit6
                      when a.iFamSizeForIncLimit=07 then wld.cLimit7
                      when a.iFamSizeForIncLimit=08 then wld.cLimit8
                      when a.iFamSizeForIncLimit=09 then wld.cLimit9
                      when a.iFamSizeForIncLimit=10 then wld.cLimit10
                      when a.iFamSizeForIncLimit=11 then wld.cLimit11
                      when a.iFamSizeForIncLimit=12 then wld.cLimit12
                      when a.iFamSizeForIncLimit=13 then wld.cLimit13
                      when a.iFamSizeForIncLimit=14 then wld.cLimit14
                      when a.iFamSizeForIncLimit=15 then wld.cLimit15
                      when a.iFamSizeForIncLimit=16 then wld.cLimit16
                   end
           end
          ) as LowIncLmt
      ,case
          when a.dtotannincome            then 'Tenant Qualifies as Low Income'
          when a.scurrentincomestatus=2   then 'Tenant Qualifies as Very Low Income'
          when a.scurrentincomestatus=3   then 'Tenant Qualifies as Extremely Low Income'
          when a.scurrentincomestatus=' ' then 'Not Section 8'
       end as HHIncLmt
  from MPI_LatestCompletedCertByProgram_View as a
       inner join property as b
               on b.hmy=a.hprop
              and b.scode='420'
       inner join WLIncLimitXRef as wlr
               on wlr.hLink=b.hMy
              and wlr.iType=3
              and wlr.b50059='-1'
       inner join WLIncLimit as wll
               on wll.hMy=wlr.hIncLimit
              and wll.iPercentIncomeLimit in (30,50,80)
       inner join WLincLimitDet as wld
               on wld.hIncLimit=wll.hMy
              and wld.dtLimitEffective<=cast(current_timestamp as datetime)
              and wld.dtLimitExpiration>=cast(current_timestamp as datetime)
       inner join unit as c
               on c.hproperty=a.hprop
              and c.hmy=a.hunit
       inner join hmunit as c2
               on c2.hunit=a.hunit
       inner join tenant as d
               on d.hproperty=a.hprop
              and d.hmyperson=a.htenant
              and d.istatus<>1
 where a.scerttype not in ('TM','MO')
 group by b.saddr1
         ,b.scity
         ,b.sstate
         ,c.scode
         ,c.ibedrooms
         ,d.scode
         ,rtrim(d.sfirstname)+' '+rtrim(d.slastname)
         ,a.dteffective
         ,cast(a.scerttype as varchar(50))
         ,case
             when a.itype=2 then 'Tax Credit'
             else
                case a.sSubsidyType
                   when 1 then 'Sec 8'
                   when 2 then 'Rent Supp'
                   when 3 then 'RAP'
                   when 4 then 'Sec 236'
                   when 5 then 'BMIR'
                   when 7 then 'Sec 202 PRAC'
                   when 8 then 'Sec 811 PRAC'
                   when 9 then 'Sec 202/162 PAC'
                end
          end
         ,a.ifamsizeforinclimit
         ,a.dtotannincome
         ,case
             when a.dtotannincome            then 'Tenant Qualifies as Low Income'
             when a.scurrentincomestatus=2   then 'Tenant Qualifies as Very Low Income'
             when a.scurrentincomestatus=3   then 'Tenant Qualifies as Extremely Low Income'
             when a.scurrentincomestatus=' ' then 'Not Section 8'
          end
;

Ps.: Do yourself a favour and use (real) joins (or apply) on all tables. Also format your code (with a "wopper" like your code, it's easy to miss focus if it's not formated).

1 Like

Bitsmed,
You are amazing! Your wild guess worked; I needed the aggregate function (max). Also, thank you for the constructive feedback on the code; I am self taught and obviously still struggle at times. I updated my code to match yours to learn proper syntax. I am better at reading (vs. writing) code and often steal existing code (I very rarely write my own); therefore, I pick up other people's bad habits.
Can I please ask your feedback on how to write my last piece of code? Seeing that my query now returns a number for ExtrLowIncLmt, VLowIncLmt, and LowIncLmt, I want to compare a.dtotannincome to each of these three numbers to determine the income at which the tenant qualifies. I was hoping the following would work:
,case
when a.dtotannincome < 'ExtrLowIncLmt' then 'Tenant Qualifies as Extremely Low Income'
when a.scurrentincomestatus < 'VLowIncLmt' then 'Tenant Qualifies as Very Low Income'
when a.scurrentincomestatus < 'LowIncLmt' then 'Tenant Qualifies as Low Income'
end as HHIncLmt
Unfortunately, I get the following error:
Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Thanks in advance!!!!
Nicki

I'm also self taught, so keep practicing - I do all the time :slight_smile:

You cannot refer to a constructed alias (field) from the select section, you can "only" refer to fields from your tables (from section).

To solve your problem, you can either:

  • refer to the whole max(...) statements
  • wrap you query in a subselect or common table expression
  • create a temporary table

The common table expression goes something like:

with cte
  as (select b.saddr1 as PropName
            ,b.scity as PropCity
            ,b.sstate as PropState
            ,c.scode as UnitID
            ,c.ibedrooms as UnitSize
            ,d.scode as ResidentCode
            ,rtrim(d.sfirstname)+' '+rtrim(d.slastname) as HOHName
            ,a.dteffective as CertEffDate
            ,cast(a.scerttype as varchar(50)) as CertType
            ,case
                when a.itype=2 then 'Tax Credit'
                else
                   case a.sSubsidyType
                      when 1 then 'Sec 8'
                      when 2 then 'Rent Supp'
                      when 3 then 'RAP'
                      when 4 then 'Sec 236'
                      when 5 then 'BMIR'
                      when 7 then 'Sec 202 PRAC'
                      when 8 then 'Sec 811 PRAC'
                      when 9 then 'Sec 202/162 PAC'
                   end
             end as SubType
            ,a.ifamsizeforinclimit as [#HHMem]
            ,a.dtotannincome as CrntIncome
            ,max(case
                    when wll.iPercentIncomeLimit=30
                    then case
                            when a.iFamSizeForIncLimit=01 then wld.cLimit1
                            when a.iFamSizeForIncLimit=02 then wld.cLimit2
                            when a.iFamSizeForIncLimit=03 then wld.cLimit3
                            when a.iFamSizeForIncLimit=04 then wld.cLimit4
                            when a.iFamSizeForIncLimit=05 then wld.cLimit5
                            when a.iFamSizeForIncLimit=06 then wld.cLimit6
                            when a.iFamSizeForIncLimit=07 then wld.cLimit7
                            when a.iFamSizeForIncLimit=08 then wld.cLimit8
                            when a.iFamSizeForIncLimit=09 then wld.cLimit9
                            when a.iFamSizeForIncLimit=10 then wld.cLimit10
                            when a.iFamSizeForIncLimit=11 then wld.cLimit11
                            when a.iFamSizeForIncLimit=12 then wld.cLimit12
                            when a.iFamSizeForIncLimit=13 then wld.cLimit13
                            when a.iFamSizeForIncLimit=14 then wld.cLimit14
                            when a.iFamSizeForIncLimit=15 then wld.cLimit15
                            when a.iFamSizeForIncLimit=16 then wld.cLimit16
                         end
                 end
                ) as ExtrLowIncLmt
            ,max(case
                    when wll.iPercentIncomeLimit=50
                    then case
                            when a.iFamSizeForIncLimit=01 then wld.cLimit1
                            when a.iFamSizeForIncLimit=02 then wld.cLimit2
                            when a.iFamSizeForIncLimit=03 then wld.cLimit3
                            when a.iFamSizeForIncLimit=04 then wld.cLimit4
                            when a.iFamSizeForIncLimit=05 then wld.cLimit5
                            when a.iFamSizeForIncLimit=06 then wld.cLimit6
                            when a.iFamSizeForIncLimit=07 then wld.cLimit7
                            when a.iFamSizeForIncLimit=08 then wld.cLimit8
                            when a.iFamSizeForIncLimit=09 then wld.cLimit9
                            when a.iFamSizeForIncLimit=10 then wld.cLimit10
                            when a.iFamSizeForIncLimit=11 then wld.cLimit11
                            when a.iFamSizeForIncLimit=12 then wld.cLimit12
                            when a.iFamSizeForIncLimit=13 then wld.cLimit13
                            when a.iFamSizeForIncLimit=14 then wld.cLimit14
                            when a.iFamSizeForIncLimit=15 then wld.cLimit15
                            when a.iFamSizeForIncLimit=16 then wld.cLimit16
                         end
                 end
                ) as VLowIncLmt
            ,max(case
                    when wll.iPercentIncomeLimit=80
                    then case
                            when a.iFamSizeForIncLimit=01 then wld.cLimit1
                            when a.iFamSizeForIncLimit=02 then wld.cLimit2
                            when a.iFamSizeForIncLimit=03 then wld.cLimit3
                            when a.iFamSizeForIncLimit=04 then wld.cLimit4
                            when a.iFamSizeForIncLimit=05 then wld.cLimit5
                            when a.iFamSizeForIncLimit=06 then wld.cLimit6
                            when a.iFamSizeForIncLimit=07 then wld.cLimit7
                            when a.iFamSizeForIncLimit=08 then wld.cLimit8
                            when a.iFamSizeForIncLimit=09 then wld.cLimit9
                            when a.iFamSizeForIncLimit=10 then wld.cLimit10
                            when a.iFamSizeForIncLimit=11 then wld.cLimit11
                            when a.iFamSizeForIncLimit=12 then wld.cLimit12
                            when a.iFamSizeForIncLimit=13 then wld.cLimit13
                            when a.iFamSizeForIncLimit=14 then wld.cLimit14
                            when a.iFamSizeForIncLimit=15 then wld.cLimit15
                            when a.iFamSizeForIncLimit=16 then wld.cLimit16
                         end
                 end
                ) as LowIncLmt
               ,a.scurrentincomestatus
        from MPI_LatestCompletedCertByProgram_View as a
             inner join property as b
                     on b.hmy=a.hprop
                    and b.scode='420'
             inner join WLIncLimitXRef as wlr
                     on wlr.hLink=b.hMy
                    and wlr.iType=3
                    and wlr.b50059='-1'
             inner join WLIncLimit as wll
                     on wll.hMy=wlr.hIncLimit
                    and wll.iPercentIncomeLimit in (30,50,80)
             inner join WLincLimitDet as wld
                     on wld.hIncLimit=wll.hMy
                    and wld.dtLimitEffective<=cast(current_timestamp as datetime)
                    and wld.dtLimitExpiration>=cast(current_timestamp as datetime)
             inner join unit as c
                     on c.hproperty=a.hprop
                    and c.hmy=a.hunit
             inner join hmunit as c2
                     on c2.hunit=a.hunit
             inner join tenant as d
                     on d.hproperty=a.hprop
                    and d.hmyperson=a.htenant
                    and d.istatus<>1
       where a.scerttype not in ('TM','MO')
       group by b.saddr1
               ,b.scity
               ,b.sstate
               ,c.scode
               ,c.ibedrooms
               ,d.scode
               ,rtrim(d.sfirstname)+' '+rtrim(d.slastname)
               ,a.dteffective
               ,cast(a.scerttype as varchar(50))
               ,case
                   when a.itype=2 then 'Tax Credit'
                   else
                      case a.sSubsidyType
                         when 1 then 'Sec 8'
                         when 2 then 'Rent Supp'
                         when 3 then 'RAP'
                         when 4 then 'Sec 236'
                         when 5 then 'BMIR'
                         when 7 then 'Sec 202 PRAC'
                         when 8 then 'Sec 811 PRAC'
                         when 9 then 'Sec 202/162 PAC'
                      end
                end
               ,a.ifamsizeforinclimit
               ,a.dtotannincome
               ,a.scurrentincomestatus
     )
select PropName
      ,PropCity
      ,PropState
      ,UnitID
      ,UnitSize
      ,ResidentCode
      ,HOHName
      ,CertEffDate
      ,CertType
      ,SubType
      ,[#HHMem]
      ,CrntIncome
      ,ExtrLowIncLmt
      ,VLowIncLmt
      ,LowIncLmt
      ,HHIncLmt
      ,case
          when CrntIncome<ExtrLowIncLmt        then 'Tenant Qualifies as Extremely Low Income'
          when scurrentincomestatus<VLowIncLmt then 'Tenant Qualifies as Very Low Income'
          when scurrentincomestatus<LowIncLmt  then 'Tenant Qualifies as Low Income'
       end as HHIncLmt
  from cte
;
1 Like

A temporary table worked beautifully, thank you bitsmed. I couldn't have finished this report without your help! Thank you a millions times over!!!!