| Author |
Topic |
|
eric_ht
Starting Member
37 Posts |
 Posted - 2009-02-06 : 07:31:57
|
| I have data that needs to be returned to be displayed in a browser. So performance is an issue here. For any number of Plants a user can select up to three years of data to be displayed. That data will be summarized by plant and each year will be displayed in a corresponding column. For Example: Plant....... 2009Totals 2008Totals 2007TotalsThe data is stored by Plant and Year such as:Plant1, Year, amountSo I am assuming a three way join would have to occur based on plant with each individual join selecting the appropriate year. There is no guarantee that each plant has totals in a given year but I can force a record into the database to ensure their is a match in each year for a plant.Is a three way join the only way to accomplish this? Is there an easier faster way? What about not forcing a dummy record into the db for years that plant has no activity?Make sense? Or am I over complicating this?Thanks in advance for any help. |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-02-06 : 08:56:49
|
| [code]SELECT Plant ,SUM(CASE WHEN [Year] = 2009 THEN amount END) AS 2009Total ,SUM(CASE WHEN [Year] = 2008 THEN amount END) AS 2008Total ,SUM(CASE WHEN [Year] = 2007 THEN amount END) AS 2007TotalFROM PlantWHERE [Year] BETWEEN 2007 AND 2009GROUP BY Plant[/code] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-06 : 09:00:34
|
| [code]SELECT Plant,[2007],[2008],[2009]FROM (SELECT Plant1, Year, amount FROM Table)mPIVOT (SUM(amount) FOR Year IN ([2007],[2008],[2009]))p[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
eric_ht
Starting Member
37 Posts |
Posted - 2009-02-06 : 09:23:09
|
| Thanks Everyone. The years are dynamic and do not have to be in succession. Looks like the dynamic pivot table may be the way to go or I could build the other options through a dynamic select also. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-06 : 09:25:38
|
| i think you should use dynamic sql along with PIVOT operator as article suggests |
|
|
|
eric_ht
Starting Member
37 Posts |
Posted - 2009-02-06 : 10:51:30
|
| I think you are exactly right Visakh. But I've read the article and done a little research and pivots do seem to have their limitations.One appears to be the number of columns to aggregate for the pivot. so I need to sum things like number of orders, amount of sales, amount of returns, etc. by year(the pivot).Any suggestions on that? I've seen an example and it looks really messy... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2009-02-09 : 19:11:22
|
There is no need for anything dynamic, since you are always returning exactly 3 years. Just return Year1, Year2, Year3, and let your report/presentation layer worry about labeling year1 as "2009", year2 as "2010", and so on, whatever values are chosen for those parameters. In short, remember to never, ever return dynamic column names or use dynamic sql if you want avoid it, and it in this case you easily can.SELECT Plant ,SUM(CASE WHEN [Year] = @Year1 THEN amount END) AS Year1Total ,SUM(CASE WHEN [Year] = @Year2 THEN amount END) AS Year2Total ,SUM(CASE WHEN [Year] = @Year3 THEN amount END) AS Year3TotalFROM PlantWHERE [Year] in (@Year1,@Year2,@Year3)GROUP BY Plant - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
Is A JOIN My only option here?