[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

T-SQL: What in the world? I just want to say that, if this is greater than 0, then show it for goodness sakes!

Hello:

As you can tell from the title of this post, I'm really frustrated with something.

The first set of code below is a snippet from a query, and this code is working.  It is simply saying show me all past due customer balances greater than 180 days.

The second code snippet is really where I'm having trouble, because I'm getting syntax errors everywhere.

In that second piece of code, all I'm trying to say is if the first piece of code is > 0, then display it.  

But, no matter how many times I play with parentheses plus no matter how many years I've been in this business plus no matter how many books that I've read on T-SQL, I can never get syntax right.

Please help!  Thanks!

John

CASE WHEN DATEDIFF (dd, RM20101.DOCDATE, @DATE) > 180
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180
and RM20101.RMDTYPAL < 7
THEN RM20101.CURTRXAM ELSE 0 
END AS [>180]

Open in new window



CASE WHEN (DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180
and RM20101.RMDTYPAL < 7
THEN RM20101.CURTRXAM ELSE 0
END) > 0 THEN 
(CASE WHEN (DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180
and RM20101.RMDTYPAL < 7
THEN RM20101.CURTRXAM ELSE 0
END) AS [BAD DEBT]

Open in new window

0
John Ellis
Asked:
John Ellis
1 Solution
 
ManjuIT - Project ManagerCommented:
Can you try this...

CASE WHEN (case when DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180
and RM20101.RMDTYPAL < 7
THEN RM20101.CURTRXAM ELSE 0
END) > 0 THEN
(CASE WHEN (DATEDIFF(dd, RM20101.DOCDATE, @DATE) > 180
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
WHEN DATEDIFF(dd, RM20101.DUEDATE, @DATE) > 180
and RM20101.RMDTYPAL < 7
THEN RM20101.CURTRXAM ELSE 0
END) else 0 end AS [BAD DEBT]
0
 
John EllisAuthor Commented:
Thanks, manju!  This worked!

I simply needed to use two "CASE WHEN" statements at the beginning and an "extra" ELSE 0 END, at the end of the clause.

Thank you!

John
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now