February 26, 2013 at 7:57 am
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.
February 26, 2013 at 8:15 am
-- 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
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
February 26, 2013 at 8:43 am
Now I get this error.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
February 26, 2013 at 8:50 am
Is this - [SecDepIntRate] a column name or a value?
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
February 26, 2013 at 9:09 am
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
February 26, 2013 at 9:21 am
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')
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
February 26, 2013 at 9:39 am
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')
February 27, 2013 at 1:29 am
Continued here.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply