Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
TSQL: Specifying days within a year and based on a certain number of years previous
Posted on 2016-07-28
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
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
--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
[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
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
Active 7 days ago
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 15 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.