Syntax help 1

[LT2]

SQL Svr 2000

I have the following select statement:

Select ac.Client, ag.Name, t.TxDate, v.PdAmt, p.Payor, v.CoPay, b.BillNum, b.AjDate, v.RPaySeq
From Payments v
  inner join AClient ac on v.ClientNum = ac.ClientNum
  inner join Provider ag on ac.ProvNum = ag.ProvNum
  left join FundingSource p on v.FSID = p.FSID
  inner join BDetail bd on v.BDID = bd.BDID
  left join Bill b on bd.BLID = b.BLID
  inner join Treat t on bd.TxID = t.TxID
Where bd.Status = 2 --Paid
  and b.Adjd = 1 --Adjudicated
  and ag.ProvNum = 111

I would like to incorporate into the select portion an additional field based upon the following criteria.

(IF v.PdAmt < 0
 Then 'Void'
  If v.RpaySeq = 99
  Then 'Reallocate'
  Else 'none' ) as 'AdjustType'

Can anyone offer some guidance?

 

[gmmastros]

try this:

Select ac.Client, ag.Name, t.TxDate, v.PdAmt, p.Payor, v.CoPay, b.BillNum, b.AjDate, v.RPaySeq[!],
       Case When v.PdAmt < 0 Then 'Void'
            When v.RpaySeq = 99 Then 'Reallocate'
            Else 'none' 
            End as 'AdjustType'[/!]
From Payments v
  inner join AClient ac on v.ClientNum = ac.ClientNum
  inner join Provider ag on ac.ProvNum = ag.ProvNum
  left join FundingSource p on v.FSID = p.FSID
  inner join BDetail bd on v.BDID = bd.BDID
  left join Bill b on bd.BLID = b.BLID
  inner join Treat t on bd.TxID = t.TxID
Where bd.Status = 2 --Paid
  and b.Adjd = 1 --Adjudicated
  and ag.ProvNum = 111

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LT2]

Excellent, Thanks George!

I tried CASE but obviously had something wrong.

Thank you for your prompt reply, it is greatly appreciated.

LT

 

[gmmastros]

You're welcome.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom