Pivot Select

Hi,
I'm able to get Pivot to work but I'm having trouble limiting the record set. I want it to select only records from this FiscalYear. I have a table with a field called CurrentBudgetYear that I use as a control. So FiscalYear should equal CurrentBudgetYear but my results include all FiscalYears.

SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6, [7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12
FROM
(SELECT e.ProjNo, e.TaskCode, e.FiscalPeriod, e.ActualAmt
FROM tblActualExpend AS e
INNER JOIN tblBudgetConfig ON e.FiscalYear = tblBudgetConfig.CurrentBudgetYear
)p
PIVOT
(
SUM(ActualAmt)
FOR FiscalPeriod IN
( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12])
)AS pvt
ORDER BY ProjNo, TaskCode;

Thanks in advanced for any help.

It appears that in the derived table, you need a WHERE clause to constrain the data to a specific FiscalYear.

Something like:

WHERE e.FiscalYear >= '20060101' and e.FiscalYear < '20070101'

Or whatever dates demarc your Fiscal Year.

|||Thanks Arnie, I can see where that would work. I really want something more automated. Thats why I use the control table. This way all I have to do is change the value in one field in one table and all my queries are current.|||

As you noticed, If your 'control table' has rows all of your Fiscal Years, you retreive all Fiscal Years. The only way it would work without a WHERE clause is if the 'control table' has only one row of data for the current FiscalYear.

If you want only one fiscal Year, then you will have to specify which one. The query processor can't read your mind.

You could still 'automate' the process, for example, assuming your FiscalYear begins July 1:

WHERE e.FiscalYear >= cast( cast( ( year( getdate() ) - 1) AS char(4)) + '0701' AS datetime )
AND e.FiscalYear < cast( cast( ( year( getdate() )) AS char(4)) + '0701' AS datetime )

Will always derive the Fiscal year for the current date.

|||

Sorry to have bothered you. The pivot works exactly as I want. I was running it on test data that I took at the end of the last fiscal year so I was getting all of the fiscal periods. Becuase of that I was sure it was not working. I just updated my tables and I'm now getting the results I was expecting.

As an FYI, the 'control' table only has one record in it....the FiscalYear. For reporting purposes we can't cut over to the new fiscal year at the start of the year (atleast in the database) so we use this table to control all of that.

Cheers!

|||Not a problem!