We help IT Professionals succeed at work.
Get Started

TSQL: Specifying days within a year and based on a certain number of years previous

60 Views
Last Modified: 2016-07-29
Hello:

The first piece of code below shows credit memos older than five years with an amount greater than 0, while the second piece of code shows credit memos with an amount greater than 0 but between 181 and 365 days from the credit memo's date.

This means that the second piece of code shows data only from the past year.  So, the first piece of code shows credit memos that are older than five years--not just credit memos between 181 and 365 days of this past year.

I need to be able to show the second piece of code at any point in time that I want within the past five years.

So, if I want to see credit memos in the "[181 - 365 Days]" bucket 1 year ago, I want to see that.  If I want to see credit memos from within that same bucket 4 years ago, I want to see that.

Is there a way, through perhaps a common table expression, that I can accomplish this?

Below are screenshots of what data displays when I run the first and second pieces of code, respectively.

Thanks, for the help!  It's much appreciated!

John


--credit memos older than five years whose balance is greater than zero 
select RM20101.CUSTNMBR as [CUSTOMER ID], RM00101.CUSTNAME AS [NAME], SUM(RM20101.CURTRXAM * - 1) AS [AMT REMAINING]
from RM20101 
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
where RMDTYPAL = 7 and DATEDIFF(yyyy, RM20101.DOCDATE, GETDATE()) > 5 and RM20101.CURTRXAM > 0
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME

Open in new window



--credit memos whose amount remains for the [181-365 Days] bucket within this year
SELECT RM20101.CUSTNMBR as [CUSTOMER ID], RM00101.CUSTNAME AS [NAME], SUM(RM20101.CURTRXAM * - 1) AS [AMT REMAINING], 
SUM(CASE WHEN (DATEDIFF(dd, RM20101.DOCDATE, GETDATE()) > 180 
AND DATEDIFF(dd, RM20101.DOCDATE, GETDATE()) <= 365)
THEN RM20101.CURTRXAM * - 1 ELSE 0 END) AS [181-365 DAYS]
FROM RM20101 
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR 
WHERE (RM20101.VOIDSTTS = 0) AND (RM20101.CURTRXAM > 0) AND RM20101.RMDTYPAL = 7
and DATEDIFF(dd, RM20101.DOCDATE, GETDATE()) > 180 
and DATEDIFF(dd, RM20101.DOCDATE, GETDATE()) <= 365
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME

Open in new window



FirstSecond
Comment
Watch Question
Data Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE