Solved

Query Month Interval

Posted on 2014-03-26
3
332 Views
Last Modified: 2014-03-26
I need a query to return the following columns:

Invoices Date,  Between 12 and 18 months
Invoices Date,  Between 18 and 24 months
Invoices Date,  Beyond 24 months

The filter is by date

Columns to consider: CustId, InvoiceDate, InvoiceNum, CreditAmount
0
Comment
Question by:rflorencio
3 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39955979
Something like...

SELECT whatever
WHERE invoicedate BETWEEN DATEADD (m, 12, GETDATE()) AND DATEADD (m, 18 , GETDATE())

Etc.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39956007
which format do you need? what will you do with the output?

SELECT CustId, InvoiceDate, InvoiceNum, CreditAmount, 'Between 12 and 18 months' as state
from yourtablename
WHERE invoicedate BETWEEN DATEADD (m, -12, GETDATE()) AND DATEADD (m, -18 , GETDATE())
union all
SELECT CustId, InvoiceDate, InvoiceNum, CreditAmount, 'Between 18 and 24 months' as state
from yourtablename
WHERE invoicedate BETWEEN DATEADD (m, -18, GETDATE()) AND DATEADD (m, -24, GETDATE())
union all
SELECT CustId, InvoiceDate, InvoiceNum, CreditAmount, 'Beyond 24 months' as state
from yourtablename
WHERE invoicedate < DATEADD (m, 24, GETDATE())
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 39956038
E.g.

WITH    Calculated
          AS ( SELECT   * ,
                        DATEDIFF(MONTH, InvoiceDate, GETDATE()) AS AgeInMonths
               FROM     yourTable
             )
    SELECT  * ,
            CASE WHEN AgeInMonths BETWEEN 12 AND 18 THEN '12-18'
                 WHEN AgeInMonths BETWEEN 18 AND 24 THEN '18-24'
                 WHEN AgeInMonths > 24 THEN '>24'
                 ELSE '<12'
            END AS IntervalText
    FROM    Calculated;

Open in new window

0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

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