SSRS: Projecting into Future Months that "Cross" Years

Hello:

The code below shows part of a much larger query of mine that feeds into a SQL Reporting Services (SSRS) report.  

This code, as you can see, takes into account the current month we are in.  And, the code is designed to project 18 months into the future.

But, I had forgotten that the remainder of the code (from Month 2 onward) is not taking into account the year portion of a date.  

For example, right now, my report is only showing data projected through December of this year (2013).  This represents this month (October), next month (November), and the next month (December).  

It is not providing any data for January, February, etc. of 2014, because I did not program references to a particular year.

I don't know that I've ever programmed time "elements" like this that potentially cross into the next year or the year after (i.e. 18 months into the future from October).

I know that "DATEPART(y, date)" will give me the year, and I know that I can also use DATEPART(y, getdate()) to reference the current year.

This client is on a "calendar-based" fiscal year.  So, there are no business dates that deviate from the calendar year.

So, how do I place this logic into my code below?

Thanks!  Much appreciated!

TBSupport


CASE WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 0
then 'Month 1'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 1 then 'Month 2'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 2 then 'Month 3'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 3 then 'Month 4'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 4 then 'Month 5'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 5 then 'Month 6'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 6 then 'Month 7'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 7 then 'Month 8'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 8 then 'Month 9'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 9 then 'Month 10'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 10 then 'Month 11'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 11 then 'Month 12'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 12 then 'Month 13'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 13 then 'Month 14'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 14 then 'Month 15'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 15 then 'Month 16'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 16 then 'Month 17'
WHEN DATEPART(m, POP10110.REQDATE) - DATEPART(m, getdate())= 17 then 'Month 18' else''
end as Month
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simone BSenior E-Commerce AnalystCommented:
If I have the right idea of what you're looking for, you may not need a CASE here. Try this, replacing your entire CASE statement:

SELECT 
'Month ' + CAST(DATEDIFF(m,GETDATE(),POP10110.REQDATE) + 1 AS VARCHAR(2))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
Hi There, Buttercup1:

Unfortunately, I need an "if, then" type statement for the report that I'm doing.  It's a matrix report that has "Month 1", "Month 2", etc. as columns and totals for those months are within each column.

TBSupport
0
Simone BSenior E-Commerce AnalystCommented:
The query will return Month 1, Month 2, etc, based on the value of POP10110.REQDATE. See the image below for sample results.

Sample Results
edit (sorry)

You only need to have one line in your query, and you will get the Month # for each line.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Simone BSenior E-Commerce AnalystCommented:
Here are additional sample results from actual data. These show negative because my dates are in the past:

Real data
0
PortletPaulfreelancerCommented:
please try this:
    DECLARE @s AS datetime, @e AS datetime
    
    SET @s = cast(getdate() AS date)
    SET @s = dateadd(DAY,-datepart(DAY,@s)+1,@s)
    SET @e = dateadd(MONTH,18,@s)
    
    SELECT
      CASE datediff(MONTH,getdate(),POP10110.REQDATE) + 1
           WHEN 1  THEN 'Month 1'
           WHEN 2  THEN 'Month 2'
           WHEN 3  THEN 'Month 3'
           WHEN 4  THEN 'Month 4'
           WHEN 5  THEN 'Month 5'
           WHEN 6  THEN 'Month 6'
           WHEN 7  THEN 'Month 7'
           WHEN 8  THEN 'Month 8'
           WHEN 9  THEN 'Month 9'
           WHEN 10 THEN 'Month 10'
           WHEN 11 THEN 'Month 11'
           WHEN 12 THEN 'Month 12'
           WHEN 13 THEN 'Month 13'
           WHEN 14 THEN 'Month 14'
           WHEN 15 THEN 'Month 15'
           WHEN 16 THEN 'Month 16'
           WHEN 17 THEN 'Month 17'
           WHEN 18 THEN 'Month 18'
           ELSE ''
       END AS Mnth
    , datediff(MONTH,getdate(),POP10110.REQDATE)
    FROM POP10110
    WHERE ( POP10110.REQDATE >= @s AND POP10110.REQDATE < @e )
;


    CREATE TABLE POP10110
    	([REQDATE] datetime)
    ;
    	
    INSERT INTO POP10110
    	([REQDATE])
    VALUES
    	('2013-09-01 00:00:00'),
    	('2013-10-01 00:00:00'),
    	('2013-11-30 00:00:00'),
    	('2013-12-30 00:00:00'),
    	('2014-01-29 00:00:00'),
    	('2014-02-28 00:00:00'),
    	('2014-03-30 00:00:00'),
    	('2014-04-29 00:00:00'),
    	('2014-05-29 00:00:00'),
    	('2014-06-28 00:00:00'),
    	('2014-07-28 00:00:00'),
    	('2014-08-27 00:00:00'),
    	('2014-09-26 00:00:00'),
    	('2014-10-26 00:00:00'),
    	('2014-11-25 00:00:00'),
    	('2014-12-25 00:00:00'),
    	('2015-01-24 00:00:00'),
    	('2015-02-23 00:00:00'),
    	('2015-03-25 00:00:00'),
    	('2015-04-24 00:00:00')
    ;

**[Results]**:
    
    |     MNTH | COLUMN_1 |
    |----------|----------|
    |  Month 1 |        0 |
    |  Month 2 |        1 |
    |  Month 3 |        2 |
    |  Month 4 |        3 |
    |  Month 5 |        4 |
    |  Month 6 |        5 |
    |  Month 7 |        6 |
    |  Month 8 |        7 |
    |  Month 9 |        8 |
    | Month 10 |        9 |
    | Month 11 |       10 |
    | Month 12 |       11 |
    | Month 13 |       12 |
    | Month 14 |       13 |
    | Month 15 |       14 |
    | Month 16 |       15 |
    | Month 17 |       16 |
    | Month 18 |       17 |
    
  [1]: http://sqlfiddle.com/#!3/0738b0/1

Open in new window

0
PortletPaulfreelancerCommented:
I'm sorry, perhaps I didn't fully understand the requirement.

Do you want?
A: one case expression
B: 18 individual columns
0
TBSupportAuthor Commented:
Hi PortletPaul!

Thanks, for the response.  But, it looks like Buttercup1 provided the solution, as I just saw.

Thanks, again, though.

TBSupport
0
PortletPaulfreelancerCommented:
and it is what I recommended as well ... you may recall this?

        SELECT
                POP10100.VENDORID      AS VendorID
              , SUM(POP10100.SUBTOTAL) AS Total
              , 'Month ' + convert(varchar,( DATEPART(m,POP10110.REQDATE) - DATEPART(m, GETDATE()) +1 )) AS [Month]
              -- change from case expression to concatenation
        
              , 0                      AS PastDue
              , 0                      AS Released
        FROM POP10100

Open in new window

>>Unfortunately, I need an "if, then" type statement for the report

oh well - at least you have confirmed it works for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.