Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Case Statement Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
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
where case 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.
Post #1424092
Posted Tuesday, February 26, 2013 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 7,133, Visits: 13,517
-- 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424101
Posted Tuesday, February 26, 2013 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
Now I get this error.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Post #1424117
Posted Tuesday, February 26, 2013 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 7,133, Visits: 13,517
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424121
Posted Tuesday, February 26, 2013 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
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'

HPROP STYPE SVALUE HVALUE
240 TAXCRNEXTAR 0.0000
240 LeaseExpPerJan 0.0000
240 LeaseExpPerFeb 0.0000
240 LeaseExpPerMar 0.0000
240 LeaseExpPerApr 0.0000
240 PAYER 0.0000
240 AppPerUnit 1.0000
240 DaysNotice 30.0000
240 MakeReady 15.0000
240 MaxHoldDays 21.0000
240 NSFChecks 2.0000
240 LeaseTermMonths 12.0000
240 LeaseTermMinimum 6.0000
240 LeaseTermRound 0.0000
240 RoundPrevEOMDate 0.0000
240 MoveInChargeNextMonthDate 20.0000
240 TaxOnRent 0.0000
240 TaxRate 0.0000
240 TaxChgCode 0.0000
240 MTMFee 150.0000
240 MTMPercent 0.0000
240 MTMChgCode 202.0000
240 ConcChgCode 0.0000
240 EarlyTermFee 0.0000
240 EarlyTermFeeType 0.0000
240 EarlyTermChgCode 204.0000
240 NSFBankFee 0.0000
240 NSFBankFeeAcct 0.0000
240 NSFFee 50.0000
240 NSFChgCode 221.0000
240 NSFRENEWALRESET 1.0000
240 LateRunNightly 0.0000
240 bLateIgnoreReceiptDate 0.0000
240 iLateFloatDays 7.0000
240 sLateFeeUseFlag LeaseCharge 0.0000
240 hLateFeeCode 197.0000
240 LateGraceDays 7.0000
240 iLateGraceDay2 7.0000
240 iLateMaxDays 0.0000
240 dLateMinDueAmt 0.0000
240 iLateType2 2.0000
240 dLateAmt2 5.0000
240 dLateMaxPercent 0.0000
240 BPRORATERENT 1.0000
240 B30DAY 0.0000
240 BCHARGEMOVEIN 1.0000
240 BCHARGEMOVEOUT 1.0000
240 BPROYEAR 0.0000
240 RentChgCode 161.0000
240 XFERCHGMOVEIN -1.0000
240 XFERCHGMOVEOUT -1.0000
240 XFERRESETNSF -1.0000
240 XFERLATEFEEDETAILS -1.0000
240 XFERSECURITYDEPOSIT -1.0000
240 LeaseRenew90 rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt 0.0000
240 LeaseRenew30 rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt 0.0000
240 LeaseExpPerJly 0.0000
240 LeaseExpPerAug 0.0000
240 LeaseExpPerSep 0.0000
240 LeaseExpPerOct 0.0000
240 LeaseExpPerDec 0.0000
240 LeaseDocument rs_DK_rp50_Sample_LeaseDoc_STL_HG_Case1422865.txt 0.0000
240 LeaseRenew60 rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt 0.0000
240 MoveInStatement 0.0000
240 LeaseDenialLetter 0.0000
240 DDEPOSITINTEREST 0 3000.0000
240 CreditCheckAgency 0.0000
240 LeaseExpPerMay 0.0000
240 LeaseExpPerJun 0.0000
240 CONTACT 0.0000
240 Language 0.0000
240 LeaseRenew120 rs_rp50_Sample_LeaseRenewalLetter.Crystal.txt 0.0000
240 BALLOWMAXRENTOVERRIDE 0.0000
240 dLatePercent2 0.0000
240 CHKNEWCHARGES 0.0000
240 GLSEGMENT1 0.0000
240 GLSEGMENT2 0.0000
240 GLSEGMENT3 0.0000
240 GLSEGMENT4 0.0000
240 hPayableCashAcct 20995.0000
240 hReceiptCashAcct 20982.0000
240 hSecDepCashAcct 20925.0000
240 hPayableAPAcct 20516.0000
240 TranPerBatch 0.0000
240 ResPerUnit 1.0000
240 BALLOWNEGATIVERECEIPTS 0.0000
240 LeaseExpPerNov 0.0000
240 CreditCheckPropID 0.0000
240 BlueMoon_Serial 0.0000
240 BlueMoon_Email 0.0000
240 bNoAchChkScn 0.0000
240 iACHCountryFormat 0.0000
240 BudgetBook 0.0000
240 HrsDiffFromServerTime 0.0000
240 WOTaxCharge 0.0000
240 WOCommPay 0.0000
240 WOCommChg 0.0000
240 WOEmailTech 0.0000
240 WOStayOnDash 0.0000
240 ReceiptVendor 0.0000




Post #1424134
Posted Tuesday, February 26, 2013 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 7,133, Visits: 13,517
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424139
Posted Tuesday, February 26, 2013 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 1:23 PM
Points: 8, Visits: 10
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
from vw_DK_RMMISCDATA x
left join Property p on x.RM_ID = p.sCode
left join PROPOPTIONS pps on p.hmy = pps.HPROP and STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST')
Post #1424153
Posted Wednesday, February 27, 2013 1:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 7,133, Visits: 13,517
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424391
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse