Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Case Statement


Case Statement

Author
Message
dmarz96
dmarz96
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19011
-- 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
dmarz96
dmarz96
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
Now I get this error.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19011
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
dmarz96
dmarz96
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19011
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
dmarz96
dmarz96
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 25
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')
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19011
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search