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?
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).
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
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
;
A temporary table worked beautifully, thank you bitsmed. I couldn't have finished this report without your help! Thank you a millions times over!!!!