seems to be a problem in my first case stmt here i need to convert this and i dont know how, hope somebody can help me out
select distinct top 10
tplan.plan_long_nm,
asset.ast_cntr_pol_num,
'As of Date' = convert(varchar(12),val.pcov_vlu_asof_dt,101),
'Cash Surrender Value' = val.pcov_cash_sur_amt,
'MVA Index Value / Account Value' = case when grp.prem_pmt_typ_cd in ('FPDA', 'NA') then 'needs mapping' else null end,--These values will only exist for KeyIndexFlex and KeySelect products.
'SNFL Adjustment Amount' = 'needs mapping', --not sure if the same rule applies for this as in the previous value
'Minimum Guaranteed Int Rate' = fixed.fxfund_guar_rt,
'Int Since Beg of Term' = fia.fia_int_earn_amt, -- + <Int Since Last Anniv>,
'Death Benefit Reduction' = dben.db_reduc_typ_dsc,
'Max Withdrawal Amount' = case when tplan.PLAN_LONG_NM in ('KEYSELECT','KEYINDEX FLEX') then 'N/A'
when grp.prem_pmt_typ_cd = 'SPDA' then (val.pcov_cash_sur_amt - 4000) else '0.00' end,
--NOTE ON MAX WITH AMOUNT IF grp.prem_pmt_typ_cd = 'SPDA' AND THE CALCULATION RESULTS IN A NEGATIVE ANSWER THEN JUST DISPLAY 0.00
'Mouseover' = case when grp.prem_pmt_typ_cd = 'SPDA' then 'Maximum withdrawal amount to leave contract open, this value leaves $4000 in the surrender value'
when tplan.PLAN_LONG_NM = 'KEYINDEX FLEX' then '$4000 must remain in the surrender value and $1000 must remain in each Indexed bucket'
when tplan.PLAN_LONG_NM = 'KEYSELECT' then '$2500 must remain in the account value' else null end
from [sv3535].[EntIntRepos1_qa].[dbo].[asset_t] asset
inner join [sv3535].[AnnTrRep1_qa].[dbo].[tr_policy_cov_t] policycov on asset.ast_src_id = policycov.pol_id and policycov.pcov_cov_seq_num = asset.ast_src_seq_num
inner join [sv3535].[AnnTrRep1_qa].[dbo].[tr_policy_cov_value_t] val on policycov.pol_id = val.pol_id and policycov.pcov_cov_seq_num = val.pcov_cov_seq_num
inner join [sv3535].[AnnTrRep1_qa].[dbo].[pcov_fixed_fund_rate_t] fixed on policycov.pol_id = fixed.pol_id
and fixed.pcov_cov_seq_num = policycov.pcov_cov_seq_num
inner join [sv3535].[AnnTrRep1_qa].[dbo].[pcov_fia_dtl_t] fia on policycov.pol_id = fia.pol_id and policycov.pcov_cov_seq_num = fia.pcov_cov_seq_num
inner join [sv3535].[AnnTrRep1_qa].[dbo].[tr_plan_t] tplan on policycov.plan_id = tplan.plan_id
inner join [sv3535].[AnnTrRep1_qa].[dbo].[dthben_reduc_type_t] dben on tplan.db_reduc_typ_cd = dben.db_reduc_typ_cd
inner join [sv3535].[AnnTrRep1_qa].[dbo].[product_cat_t] prod on tplan.prod_cat_cd = prod.prod_cat_cd
left outer join [sv3535].[EntIntRepos1_qa].[dbo].[product_group_t] grp on asset.ast_stat_cd = grp.ast_styp_cd
where asset.ast_cntr_pol_num like 'KA12545362'
and asset.data_src_cd = 'TRANSCND'
and prod.prod_cat_dsc = 'Indexed'