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 |
|
vavs
Starting Member
24 Posts |
 Posted - 2010-07-29 : 15:13:29
|
| I have two inventory tables representing approximately 200k records. I need to create a new table that uses what I call stair step logic. If field 1 and field 2 are equal, put a 1 in field 3, if not go to next record. Once this is complete, find the first record with 1 in field 3 and find all records that have field 2 equal its field 1. Repeat for all records the progress on with that logic until all records are found.In English what I am doing is finding items where the original tag number is equal to the current tag number. If the original tag and the current tag are equal, this is a master or original product. If this tag was processed, the current tag will have a different number than the original tag. It is possible that an original tag could go down as many as 5 levels. So for example, tag 123 has an original tag 123, therefore it is a master. Tags 123-01 and 123-02 have 123 in the original tag field. This makes them children of the master tag 123. Tag 123-06 has tag 123-01 as the original tag and is therefore the child of tag 123-01. What I want to do is run a stored procedure each morning that will create this table. I tried to do it in Access and the processing took forever. If I do it at the server level, I know that it will be faster. I can then create a report that shows all of the master tags in column 1 with their children in column 2 and the children's children in column 3 etc. Hence the stair step logic.Thanks in advance for any help on this issue. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 16:58:39
|
Many words  Please give table structure, sample data and wanted result.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
vavs
Starting Member
24 Posts |
Posted - 2010-07-30 : 08:40:54
|
| Thanks for the response. Here are the answers to your questions:Table structure:Tag# TextGauge Numeric 2 charWeight Numeric 8 charWidth Numeric 4 charOrigTag# TextLevel Numeric 2 charTag# Gauge Weight Width OrigTag# Level123 01 16 5000 60 123 01345 01 16 1000 12 123 01555555 16 3000 36 123 01ab123 01 16 1000 12 5555554000 01 16 1000 12 ab123 01The first check that needs to be done is for level 1 or master tags. The tag is identified by Tag# = OrigTag#. In the example above 123 01 qualifies as a master tag. In the column Level I would want to put in a 1. Now I need to look at all of the tags where the OrigTag# = 123 01. In my database I need to check approximately 10K tags which would be level 1. When I look at the above data, I see there are 2 tags 345 01 and 555555 which have a level 1 tag as the OrigTag#. These would need to have level updated to 2. I would look at level 2 tags and check for all matches to level 2 tags in the OrigTag# column. In this case ab123 01 has a level 2 OrigTag# and is a level 3. The process is repeated until there are no tags left. Once I get all of the tags completed, I am going to create a tag lineage based on the master tag. I will then be able to look at all of the activity that is associated with the master and I will be able to look at profitability.Because of the size of the database, I wanted to create a stored procedure that would run every morning after the database had been refreshed. I understand that SQL can do these types of queries much quicker than I could ever do in Access. Plus, I could automate the process to run as a part of my daily SQL updates.Thanks in advance for all of your insight and expertise, I am still learning SQL at this level. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-02 : 16:46:56
|
| There is a much better chance of getting some help if you can provide DDL and DML in a consumable format (along with the expected results). By that I mean SQL statements that we can actually run to test our code against. Here is a link that might help you prepare your data for consumption:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-10 : 11:52:43
|
| Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. That means no spaces in the data element names and no silly data type and "tbl-" prefixes. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html My guess is CREATE TABLE SomethingInventory(tag_nbr CHAR(5) NOT NULL, tag_step CHAR(2) DEFAULT '00' NOT NULL CHECK (step_nbr IN ('00', '01','02', '03', '04', '05')), PRIMARY KEY (tast_nbe, step_nbr), step_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, etc.); --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Stair step logic in stored procedure