Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
latture
Starting Member
24 Posts |
 Posted - 2014-04-21 : 14:59:48
|
| Hello all. I'm needing some help with this report that I'm trying to write. I have a table that has store information, amount of sales, and year.Looks something like this. store storename Amount 2010 2011 2012 2013 20141-------a--------100-------0-----1-----0-----2-----02 b 100 0 0 3 1 13 c 200 0 0 1 1 04 d 100 0 0 0 3 1In the procedure I have these declarations. DECLARE @Reporting int = (SELECT MAX(RptPrd) FROM StoreInfo) DECLARE @ReportingDate date = CONVERT(varchar,(@RptPrd * 100)+ 1)DECLARE @Column varchar(4) = YEAR(@ReportingDate)I need to update the year columns from the table and I was hoping to do it using dynamic column but I can't seem to figure out how to do this. The purpose of the report is if the sales amount for a store is greater than what's in the amount field then I need to add 1 to the corresponding cell that falls under the same year. So if store 1 sales amount for the month is 150 then store 1 cell under 2014 would become 1 instead of 0. The reason I'm trying to use dynamic column is because next year another year will be added to the column and the report still needs to run automatically.Does this make sense? Can anyone help? Thanks. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-21 : 15:30:36
|
| Is that the structure of the report or the table or both?It's not clear what is data and what is the report to me. I'm hoping that what you have posted is the report output. If it is the table, then things get more difficult. Can you post your table structure along with sample data so we can run queries against it? Also, you mentioned that "if store 1 sales amount for the month is 150 then store 1 cell under 2014." Where does that 150 come from?Theoretically, you would have a table with three columns: Store (ID), Amount and Year. Then you can join to a store table to get the Store Name. But, more importantly, you can support any number of years without having to alter the table structure. Then you can use a dynamic pivot to get the report information. Here is a sample if a dynamic pivot:http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query |
 |
|
|
latture
Starting Member
24 Posts |
Posted - 2014-04-21 : 16:01:30
|
| Well heck. Here is the table structure. New year will be added every year. [store] [float] NULL, [name] [nvarchar](50) NULL, [Month] [nvarchar](3) NULL, [Year] [nvarchar](4) NULL, [Amount] [money] NULL, [1997] [float] NULL, [2002] [float] NULL, [2003] [float] NULL, [2004] [float] NULL, [2005] [float] NULL, [2006] [float] NULL, [2007] [float] NULL, [2008] [float] NULL, [2009] [float] NULL, [2010] [float] NULL, [2011] [float] NULL, [2012] [float] NULL, [2013] [float] NULL, [2014] [float] NULL, [updated] [datetime] NULLThe same structure that I provided is the table and I'm just going to be doing a simple SSIS export from the table.The 150 is just number greater than 100 that I came up with for the Amount field for store 1. Basically, if today's amount for store 1 is greater than 100 then I need to add 1 to the 0 under 2014. Same thing for next year 2015. However many times a store sales amount is greater than what's under Amount field then you add 1 under the current year. Does that clear things up a little? |
|
|
|
|
|
|
|
|
Dynamic Column Header