Go Premium for a chance to win a PS4. Enter to Win

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

T-SQL: More Fun with Syntax

Hello:

Below is a snippet from a T-SQL query that I'm trying to build.  Here, I'm simply trying to add the results mathematically of several case statements.

But, as usual, SQL is giving me errors everywhere.

Please help!

Thanks!

John

SUM((CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 0
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
ELSE 0 
END) +
(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 1
AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 30
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
ELSE 0 
END) + 
(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 31
AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 60
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
 ELSE 0 
END) +
(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 61
AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 90
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
 ELSE 0 
END) +
(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 91
AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 180
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
 ELSE 0 
END) +
(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 181
AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 364
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
 ELSE 0 
END) +
(CASE WHEN DATEDIFF(dd, RM20101.DOCDATE, @DATE) >= 365
and RM20101.RMDTYPAL > 6
THEN RM20101.CURTRXAM * - 1  
 ELSE 0 
END)) as [BACK OUT CREDITS]

Open in new window

0
John Ellis
Asked:
John Ellis
  • 3
  • 2
1 Solution
 
ManjuIT - Project ManagerCommented:
What's the error ?
0
 
ManjuIT - Project ManagerCommented:
Try adding sum before every case statement
0
 
PortletPaulCommented:
what is the actual error message?

there isn't a syntax problem with that snippet
but there may be logic problems
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
PortletPaulCommented:
Here is a re-write of your sql snippet
SUM(
      (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 0                                                    THEN RM20101.CURTRXAM * -1 ELSE 0 END)
    + (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 1     AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 30  THEN RM20101.CURTRXAM * -1 ELSE 0 END) 
    + (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 31    AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 60  THEN RM20101.CURTRXAM * -1 ELSE 0 END) 
    + (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 61    AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 90  THEN RM20101.CURTRXAM * -1 ELSE 0 END) 
    + (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 91    AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 180 THEN RM20101.CURTRXAM * -1 ELSE 0 END) 
    + (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) = 181   AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) <= 364 THEN RM20101.CURTRXAM * -1 ELSE 0 END) 
    + (CASE WHEN RM20101.RMDTYPAL > 6  AND DATEDIFF(dd, RM20101.DOCDATE, @DATE) >= 365                                                  THEN RM20101.CURTRXAM * -1 ELSE 0 END)
    ) AS [BACK OUT CREDITS]

Open in new window

It does not really seem very important how many days difference there is; anything from less than zero to greater than 365 is treated that exact same way

why not just do this?

SUM(CASE WHEN RM20101.RMDTYPAL > 6 AND RM20101.DOCDATE IS NOT NULL THEN RM20101.CURTRXAM * -1 ELSE 0 END) AS [BACK OUT CREDITS]

Open in new window

1
 
John EllisAuthor Commented:
LOL!  Actually, you're right.  There was no syntax error.  I simply didn't put my "DECLARE" and "SET" statements for my @DATE variable at the beginning before I ran the code.

Sorry, for the bother!

John
0
 
PortletPaulCommented:
no problems, but do look at my re-write please, not sure if your case expressions are doing what you expect
1

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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