Query Month Interval

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
rflorencioAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ste5anConnect With a Mentor Senior DeveloperCommented:
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
 
Paul MacDonaldDirector, Information SystemsCommented:
Something like...

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

Etc.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
All Courses

From novice to tech pro — start learning today.