PIVOT Table 12 Months Forcast

If you want to change the display to include the year, you may need to go to dynamic SQL.

FYI, you can use DATENAME() to simplify getting the month name:
datename( month, Invoice.[Date] )

You can then concatenate the year to that, but again to get this in the pivot you will have to use dynamic SQL approach.  Please read here:

Dynamic Pivot Procedure for SQL Server - http:A_653.html

Thank you guys for your replies,

aneeshattingal: Your code doesn't work, I've attached a picture of the result of your code

mwvisa1:  I will go over the link and let you know

Thanks

Okay.  An alternative would be to code as dynamic rolling period, so you would end up with :

CurrentMonth, 1Month, 2Months, 3Months, etc.

Pivot Rolling Periods Without Using Dynamic T-SQL - http:A_654.html

You would basically, use DATEDIFF() to calculate the difference in months from current date then use [0] - [12] in your query.  

Since this is a forecast, you may be looking to get the prior year's data and fill it in but with current month first so that on paper it looks like projected values you can then change.  If that is the case, the NULLs may have been what looked wrong in Aneesh's aside from the column names.  With that being said, please try this :


SELECT [InvoiceNo], [Number]
    , [0] AS CurrentMonth
    , [11] AS NextMonth
    , [10] AS 2MonthsFromNow
    , [9] AS 3MonthsFromNow
    , [8] AS 4MonthsFromNow
    , [7] AS 5MonthsFromNow
    , [6] AS 6MonthsFromNow
    , [5] AS 7MonthsFromNow
    , [4] AS 8MonthsFromNow
    , [3] AS 9MonthsFromNow
    , [2] AS 10MonthsFromNow
    , [1] AS 11MonthsFromNow
    , [12] AS CurrentMonthNextYear
FROM
(
  SELECT DATEDIFF(MONTH, Invoice.[Date], GETDATE()) [Month],
     Invoice.InvoiceNo [InvoiceNo],
     InvoiceLine.Amount [Amount],
     Project.Number [Number]
  FROM Invoice
  INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId
  LEFT OUTER JOIN Project ON Invoice.ProjectId = Project.ProjectId
) AS InvoiceLine
PIVOT
(
  SUM(InvoiceLine.Amount)
  FOR [Month] IN (
     [0],[1],[2],[3],[4]
     [5],[6],[7],[8],
     [9],[10],[11],[12]
  )
) AS PivotTable
ORDER BY [Number] DESC


If you want it as Aneesh had, then add back the date criteria and change this portion of code :

DATEDIFF(MONTH, Invoice.[Date], GETDATE())

To :

DATEDIFF(MONTH, GETDATE(), Invoice.[Date])

Then the columns become this :
, [0] AS CurrentMonth
    , [1] AS NextMonth
    , [2] AS 2MonthsFromNow
...
,[12] AS CurrentMonthNextYear


Now you have the best of both worlds where you don't have to update the query each month or deal with dynamic SQL while having some indication in the column name what month you are dealing with.  If the Nov-2009, etc. identification is a must have then as I said the Article by Mark is a good starting place.


Regards,

Kevin

Thanks Kevin for your support,

I am getting an error with your code, please see the attachment

I am roaming around the same idea as yours, please see the attached code (pseudo code)

strSEL = N'SELECT *
FROM(
  SELECT
    CASE MONTH(Invoice.[Date]) --Date subtraction gives you 0 to 11
      WHEN 1 THEN ' + MonthString(DateAdd(m,0,Date.Now )) + '-' + YearString(DateAdd(m,0,Date.Now)) + '
      WHEN 2 THEN ' + MonthString(DateAdd(m,1,Date.Now )) + '
      WHEN 3 THEN MonthString(DateAdd(m,2,Date.Now ))
      WHEN 4 THEN MonthString(DateAdd(m,3,Date.Now ))
      WHEN 5 THEN MonthString(DateAdd(m,4,Date.Now ))
      WHEN 6 THEN MonthString(DateAdd(m,5,Date.Now ))
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 THEN 'December'
    END AS [Month],
    Invoice.InvoiceNo [InvoiceNo], 
    InvoiceLine.Amount [Amount],
    Project.Number [Number]
  FROM Invoice INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId LEFT OUTER JOIN
	   Project ON Invoice.ProjectId =  Project.ProjectId
) InvoiceLine
PIVOT
(
  SUM(InvoiceLine.Amount)
  FOR [Month] IN (
    MonthString(DateAdd(m,0,Date.Now )),[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) AS PivotTable
ORDER BY [Number] DESC

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

Sorry, my mistake.  Typing too quickly.  You have to put brackets or something around the name since it starts with number.

I will look at your attempt.

SELECT [InvoiceNo], [Number]
    , [0] AS CurrentMonth
    , [11] AS NextMonth
    , [10] AS 2MonthsFromNow
    , [9] AS 3MonthsFromNow
    , [8] AS 4MonthsFromNow
    , [7] AS 5MonthsFromNow
    , [6] AS 6MonthsFromNow
    , [5] AS 7MonthsFromNow
    , [4] AS 8MonthsFromNow
    , [3] AS 9MonthsFromNow
    , [2] AS 10MonthsFromNow
    , [1] AS 11MonthsFromNow
    , [12] AS CurrentMonthNextYear
FROM
(
  SELECT DATEDIFF(MONTH, Invoice.[Date], GETDATE()) [Month],
     Invoice.InvoiceNo [InvoiceNo],
     InvoiceLine.Amount [Amount],
     Project.Number [Number]
  FROM Invoice
  INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId
  LEFT OUTER JOIN Project ON Invoice.ProjectId = Project.ProjectId
) AS InvoiceLine
PIVOT
(
  SUM(InvoiceLine.Amount)
  FOR [Month] IN (
     [0],[1],[2],[3],[4]
     [5],[6],[7],[8],
     [9],[10],[11],[12]
  )
) AS PivotTable
ORDER BY [Number] DESC

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

here is my improved code,  the values under the pivot:

[0],[1],[2],[3],[4]
    [5],[6],[7],[8],
    [9],[10],[11],[12]
are giving me some problems, what should go there instead?

Thanks

SELECT *
FROM(
  SELECT
    CASE DATEDIFF(m,Invoice.[Date],GETDATE())  --Date subtraction gives you 0 to 11
      WHEN 0 THEN DateAdd(MONTH, 0, GETDATE()) 
      WHEN 1 THEN DateAdd(MONTH, 1, GETDATE())
      WHEN 2 THEN DateAdd(MONTH, 2, GETDATE())
      WHEN 3 THEN DateAdd(MONTH, 3, GETDATE())
      WHEN 4 THEN DateAdd(MONTH, 4, GETDATE())
      WHEN 5 THEN DateAdd(MONTH, 5, GETDATE())
      WHEN 6 THEN DateAdd(MONTH, 6, GETDATE())
      WHEN 7 THEN DateAdd(MONTH, 7, GETDATE())
      WHEN 8 THEN DateAdd(MONTH, 8, GETDATE())
      WHEN 9 THEN DateAdd(MONTH, 9, GETDATE())
      WHEN 10 THEN DateAdd(MONTH, 10, GETDATE())
      WHEN 11 THEN DateAdd(MONTH, 11, GETDATE())
    END AS [Month],
    Invoice.InvoiceNo [InvoiceNo], 
    InvoiceLine.Amount [Amount],
    Project.Number [Number]
  FROM Invoice INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId LEFT OUTER JOIN
	   Project ON Invoice.ProjectId =  Project.ProjectId
) InvoiceLine
PIVOT
(
  SUM(InvoiceLine.Amount)
  FOR [Month] IN (
    DateAdd(MONTH, 0, GETDATE()),[2],[3],[4],
    [5],[6],[7],[8],
    [9],[10],[11],[12]
  )
) AS PivotTable
ORDER BY [Number] DESC

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

[0],[1],[2],[3],[4]
    [5],[6],[7],[8],
    [9],[10],[11],[12]

The above values go along with DATEDIFF().

It looks like a type-o as there is a missing comma (,).

[0],[1],[2],[3],[4],
    [5],[6],[7],[8],
    [9],[10],[11],[12]

If you change the [Month] column to other data, then the IN statement must have valid values that will in turn become the columns.

Almost there!

Does the code make sense? I am getting one more error, I am attaching it in the picture

Thanks for your support

Okay fair enough, I will play with it later.

You will get the points.

Thanks