x
Solved

Query Month Interval

Posted on 2014-03-26
Medium Priority
343 Views
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
Question by:rflorencio

LVL 35

Expert Comment

ID: 39955979
Something like...

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

Etc.
0

LVL 71

Expert Comment

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 38

Accepted Solution

ste5an earned 2000 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;
``````
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.