6
Hello and thankyou in advance for your help :)
I am developing a sales forcasting application that allows users to enter expected prices for different products by month.
I have about six months to plan this so am just doing some preliminary investigation now.
My database (which will be SQL Server) should have as a minimum, 2 tables; "Products" to store information relating to products and "Prices" to store the prices for each product.
The prices table will have as a minimum, two fields, "PriceValue" and "Date". The date value will be mmm-yy (as prices are entered for a specific month.
What I then need to achieve in my UI is a grid to display all products as row headings and my months as column headings. The data in the grid cells would then be the prices.
This structure would basically be similar to that of a pivot table or cross tab query. I have therefore been looking at the PIVOT function introduced into SQL Server 2005 and see this as a possible solution, the only problem being that you must explicitly specify the column headings you wish to use.
My Question
Is there a way in which I can dynamically add a set number of ascending months too my pivot table rather than defining them by name [Jan-07], [Feb-07] etc.?
What I would like to achieve is a stored procedure that accepts two variables, a StartDate and EndDate.
If for example, StartDate was set to Jan-07 and EndDate to July-07, the query produced would show all the prices for this date range with the column headings to match Jan-07, Feb-07, Mar-07 etc.
I appreciate any help with this as I am struggling to find examples of what I need to achieve on the net.
Thanks.
