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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-31 : 18:52:49
|
| Harold writes "I have several problems and I am not sure what the answer is. I am using DTS to import the same 4 tables into SQL 2000 every 12 hours from another source. One of these tables is WBS which has WBS_KEY, WBS_CMF_NO, WBS_DESC. The WBS_CMF_NO is a 7 digit number that is a combination of ACCT and SUB_ACCT in the other 3 tables. The WBS_KEY looks likeanc1anc1.1anc1.1.1anc1.1.1.1am1am1.1am1.2am1.1.1am1.2.1and so on.I want to see the data rollup to the level above it. So am1 and anc1 would be a sumation of the children. What I would like to do is create 2 fields in the WBS table. ID and Parent and relate the records to each other. id,parent,key1,0,anc12,1,anc1.13,2,anc1.1.14,3,anc1.1.1.15,0,am16,5,am1.17,5,am1.28,6,am1.1.19,7,am1.2.1and so on.This is where I am lost. How and where would I handle these fields being created and populated? Is there a better way to rollup the data from the other 3 tables?I have done rollups using cursors and think I can make that work again." |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 21:07:12
|
See if these help you:http://www.sqlteam.com/item.asp?ItemID=8866http://www.windowsitpro.com/SQLServer/Article/ArticleID/15715/15715.htmlhttp://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htmI hope you didn't design this database. It looks like a disaster some mainframe programmer created that never quite got the idea of descriptive naming conventions and still thinks three digit codes for everything is the way to go.  Yeah, I know. That sounds like a Celkoism.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-31 : 21:13:56
|
Derrick, Derrick, Derrick, I guess you haven't seen how MS stores Project data in a SQL Server database?  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 21:24:26
|
| It looks like a disaster some mainframe programmer created that never quite got the idea of descriptive naming conventions and still thinks three digit codes for everything is the way to go.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Work Breakdown Structure problem