Case Statement

Question

Post reply

Case Statement

dmarz96

Old Hand

Points: 305
More actions
February 26, 2013 at 7:57 am

#271000

Good Day To All I'm a Newbie so please be patient.
I don't know if this is possible or am I barking up the wrong tree.
I have a table that has a different fee types within it. I'm trying to use a case stastatement create a different column when the type is different.
this is what I have so far and I get an error.
Basically 1 person can have many fees and the fee type is in P.STYPE and the fee value is in P.HVALUE
select p.HPROP, p.STYPE, p.SVALUE
from PROPOPTIONS P
wherecase p.STYPE
when 'nsffee' then p.hvalue = [NSFFee],
when 'MTMFee' then p.hvalue = [MTMFee] ,
when 'DDEPOSITINTEREST' then p.hvalue = [SecDepIntRate],
end
If I should do this another way be advise.
Kind Regards,
David M.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- APPLY can simplify calculations like this;

SELECT

p.HPROP,

p.STYPE,

p.SVALUE,

x.Newcolumn

FROM PROPOPTIONS P

CROSS APPLY (

SELECT Newcolumn = CASE

WHEN p.STYPE = 'nsffee' THEN 'NSFFee'

WHEN p.STYPE = 'MTMFee' THEN 'MTMFee'

WHEN p.STYPE = 'DDEPOSITINTEREST' THEN 'SecDepIntRate'

END

) x

WHERE p.hvalue = x.Newcolumn

-- then if you want, you can rewrite like so;

SELECT

p.HPROP,

p.STYPE,

p.SVALUE

FROM PROPOPTIONS P

WHERE p.hvalue = CASE

WHEN p.STYPE = 'nsffee' THEN 'NSFFee'

WHEN p.STYPE = 'MTMFee' THEN 'MTMFee'

WHEN p.STYPE = 'DDEPOSITINTEREST' THEN 'SecDepIntRate'

END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dmarz96 Old Hand Points: 305 More actions · Answer 2

Now I get this error.

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Is this - [SecDepIntRate] a column name or a value?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dmarz96 Old Hand Points: 305 More actions · Answer 4

Good Day Chris M and thanks for your help.

I'll be as detail as possible.

p.stype = different fee type (example NSFFEE,MTMFee,DDEPOSITINTEREST)

PHvalue = fee amount (example 1.00, 21.00,50.00)

P.Hprop = person property number associated with there fees

NSFFee, MTMFee, SecDepIntRate new columns where I would like to hold the values for the different fee types

I don't need to sum them everyone on this property pays the same fee.

when I run this

SELECT

p.HPROP,

p.STYPE,

p.SVALUE,

p.HVALUE

FROM PROPOPTIONS P where p.HPROP = '240'

I get this -these are all the fees assoicated with this HPROP ='240'

HPROPSTYPE SVALUEHVALUE

240TAXCRNEXTAR0.0000

240LeaseExpPerJan0.0000

240LeaseExpPerFeb0.0000

240LeaseExpPerMar0.0000

240LeaseExpPerApr0.0000

240PAYER 0.0000

240AppPerUnit1.0000

240DaysNotice30.0000

240MakeReady15.0000

240MaxHoldDays21.0000

240NSFChecks2.0000

240LeaseTermMonths12.0000

240LeaseTermMinimum6.0000

240LeaseTermRound0.0000

240RoundPrevEOMDate0.0000

240MoveInChargeNextMonthDate20.0000

240TaxOnRent0.0000

240TaxRate0.0000

240TaxChgCode0.0000

240MTMFee150.0000

240MTMPercent0.0000

240MTMChgCode202.0000

240ConcChgCode0.0000

240EarlyTermFee0.0000

240EarlyTermFeeType0.0000

240EarlyTermChgCode204.0000

240NSFBankFee0.0000

240NSFBankFeeAcct0.0000

240NSFFee50.0000

240NSFChgCode221.0000

240NSFRENEWALRESET1.0000

240LateRunNightly0.0000

240bLateIgnoreReceiptDate0.0000

240iLateFloatDays7.0000

240sLateFeeUseFlagLeaseCharge0.0000

240hLateFeeCode197.0000

240LateGraceDays7.0000

240iLateGraceDay27.0000

240iLateMaxDays0.0000

240dLateMinDueAmt0.0000

240iLateType22.0000

240dLateAmt25.0000

240dLateMaxPercent0.0000

240BPRORATERENT1.0000

240B30DAY0.0000

240BCHARGEMOVEIN1.0000

240BCHARGEMOVEOUT1.0000

240BPROYEAR0.0000

240RentChgCode161.0000

240XFERCHGMOVEIN-1.0000

240XFERCHGMOVEOUT-1.0000

240XFERRESETNSF-1.0000

240XFERLATEFEEDETAILS-1.0000

240XFERSECURITYDEPOSIT-1.0000

240LeaseRenew90rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt0.0000

240LeaseRenew30rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt0.0000

240LeaseExpPerJly0.0000

240LeaseExpPerAug0.0000

240LeaseExpPerSep0.0000

240LeaseExpPerOct0.0000

240LeaseExpPerDec0.0000

240LeaseDocumentrs_DK_rp50_Sample_LeaseDoc_STL_HG_Case1422865.txt0.0000

240LeaseRenew60rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt0.0000

240MoveInStatement0.0000

240LeaseDenialLetter0.0000

240DDEPOSITINTEREST03000.0000

240CreditCheckAgency0.0000

240LeaseExpPerMay0.0000

240LeaseExpPerJun0.0000

240CONTACT0.0000

240Language0.0000

240LeaseRenew120rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt0.0000

240BALLOWMAXRENTOVERRIDE0.0000

240dLatePercent20.0000

240CHKNEWCHARGES0.0000

240GLSEGMENT10.0000

240GLSEGMENT20.0000

240GLSEGMENT30.0000

240GLSEGMENT40.0000

240hPayableCashAcct20995.0000

240hReceiptCashAcct20982.0000

240hSecDepCashAcct20925.0000

240hPayableAPAcct20516.0000

240TranPerBatch0.0000

240ResPerUnit1.0000

240BALLOWNEGATIVERECEIPTS0.0000

240LeaseExpPerNov0.0000

240CreditCheckPropID0.0000

240BlueMoon_Serial0.0000

240BlueMoon_Email0.0000

240bNoAchChkScn0.0000

240iACHCountryFormat0.0000

240BudgetBook0.0000

240HrsDiffFromServerTime0.0000

240WOTaxCharge0.0000

240WOCommPay0.0000

240WOCommChg0.0000

240WOEmailTech0.0000

240WOStayOnDash0.0000

240ReceiptVendor0.0000

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Ah, I think you're looking at a pivot - does this help?

SELECT

p.HPROP,

p.STYPE,

p.SVALUE,

NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END,

MTMFee = CASE WHEN p.STYPE = 'MTMFee' THEN p.hvalue END,

SecDepIntRate = CASE WHEN p.STYPE = 'DDEPOSITINTEREST' THEN p.hvalue END

FROM PROPOPTIONS P

WHERE p.STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dmarz96 Old Hand Points: 305 More actions · Answer 6

Thanks now I have to include this in a view.

Well now I'm getting 3 records.

I put this in my select statement and I get no errors

, x.FAC_ACTIVITY[FAC_ACTIVITY]

, x.NUMMAINTEMP[NUMMAINTEMP]

, LH.dtLeaseFrom [LHdtLeaseFrom]

, LH.dtLeaseTo [LHdtleaseto]

, crp.destimated [crpdestimated]

--, pps.HValue [ppsNSFFEE]

,NSFFee = CASE WHEN pps.STYPE = 'nsffee' THEN pps.hvalue END

,MTMFee = CASE WHEN pps.STYPE = 'MTMFee' THEN pps.hvalue END

,SecDepIntRate = CASE WHEN pps.STYPE = 'DDEPOSITINTEREST' THEN pps.hvalue END

, m.stext [memoStext]

This what I put in my where cause

fromvw_DK_RMMISCDATA x

left joinProperty p on x.RM_ID = p.sCode

left join PROPOPTIONS pps on p.hmy = pps.HPROP and STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Continued here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden