Solved

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

Posted on 2016-07-28
3
30 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
0
Comment
Question by:John Ellis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Assisted Solution

by:Brian Chan
Brian Chan earned 250 total points
ID: 41734015
Would you mean this? I just mimic your logic from your second query.....

SELECT 
	RM20101.CUSTNMBR as [CUSTOMER ID]
	, RM00101.CUSTNAME AS [NAME]
	, SUM(RM20101.CURTRXAM * - 1) AS [AMT REMAINING]
	, SUM(	
			CASE WHEN (DATEDIFF(yyyy, RM20101.DOCDATE, GETDATE()) > 5 )
				THEN RM20101.CURTRXAM * - 1
			ELSE 0 END
		) AS [Past 5 years]
FROM RM20101 
	INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
WHERE RMDTYPAL = 7
	AND RM20101.CURTRXAM > 0
	AND (RM20101.VOIDSTTS = 0) 
	AND DATEDIFF(yyyy, RM20101.DOCDATE, GETDATE()) > 5 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 41734188
Multiple your bucket with a 1 or 2 or 3 to go back 1/2/3 years.
DECLARE @year INT
SELECT @year = 1;
--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()) > @year*180 
and DATEDIFF(dd, RM20101.DOCDATE, GETDATE()) <= @year*365
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME

Open in new window

0
 

Author Closing Comment

by:John Ellis
ID: 41734819
Thank you, both!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question