Case Statement results based on another field

Question

Im trying to create a case statement to only show the value of AmountRequested based on the value in MortgageStatus Basically if the MortgageStatus = 4 or 5 or 6 or 7 then i want to see the value of AmountRequested else show me 0

SELECT  s.ISDESC as Source,
p.ClientId,p.Id AS [Profile ID],c.CaseTypeDesc As [Case Type],u.FullName AS Advisor,
ISNULL(app.StatusDesc, ' No contact') as [Mortgage status] ,
CASE pm.AmountRequested
WHEN  pm.MortgageStatus IN (4,5,6,7) THEN pm.AmountRequested
Else '0'
END,
em.Description,
pm.ProcFeeExp,
pm.ArrangementFee,t.DueDate as [Enquiry Date],s.ISDESC
From Tasks AS t
LEFT JOIN dbo.tbl_Profile AS p
    ON t.FK_ProfileId = p.ID
LEFT JOIN dbo.tbl_CaseTypes AS c
    ON c.CaseTypeID = p.CaseTypeID
LEFT JOIN dbo.tbl_User AS u
    ON u.UserId = t.takenBy
LEFT JOIN tbl_Profile_Mortgage AS pm
    ON p.Id = pm.FK_ProfileId
LEFT JOIN tbl_AppStatus AS app
    ON pm.MortgageStatus = app.StatusId
LEFT JOIN tbl_DDEnquiryMethod AS em 
    ON t.EnquiryMethod = em.id
LEFT JOIN dbo.tbl_EnquiryType AS e 
    ON t.EnquiryType = e.EnquirytypeId
LEFT JOIN tbl_DDInitialSource as s
On t.EnquirySource = s.ISID

Thanks

Case _expression_ results based on another _column_...' – jarlh Nov 15 '18 at 11:32 — jarlh, Nov 15 '18 at 11:32

score 1 · Accepted Answer · answered Nov 15 '18 at 11:18

I think CASE syntax is incorrect. try this:

      SELECT s.ISDESC as Source,
         p.ClientId,
         p.Id AS [ Profile ID ],
         c.CaseTypeDesc As [ Case Type ],
         u.FullName AS Advisor,
         ISNULL(app.StatusDesc, ' No contact') as [ Mortgage status ],
         CASE  WHEN pm.MortgageStatus IN (4, 5, 6, 7) THEN
            pm.AmountRequested
           Else
            '0'
         END,
         em.Description,
         pm.ProcFeeExp,
         pm.ArrangementFee,
         t.DueDate as [ Enquiry Date ],
         s.ISDESC
    From Tasks AS t
    LEFT JOIN dbo.tbl_Profile AS p
      ON t.FK_ProfileId = p.ID
    LEFT JOIN dbo.tbl_CaseTypes AS c
      ON c.CaseTypeID = p.CaseTypeID
    LEFT JOIN dbo.tbl_User AS u
      ON u.UserId = t.takenBy
    LEFT JOIN tbl_Profile_Mortgage AS pm
      ON p.Id = pm.FK_ProfileId
    LEFT JOIN tbl_AppStatus AS app
      ON pm.MortgageStatus = app.StatusId
    LEFT JOIN tbl_DDEnquiryMethod AS em
      ON t.EnquiryMethod = em.id
    LEFT JOIN dbo.tbl_EnquiryType AS e
      ON t.EnquiryType = e.EnquirytypeId
    LEFT JOIN tbl_DDInitialSource as s
      On t.EnquirySource = s.ISID

Case Statement results based on another field

1 Answers1