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 |
|
dernest
Starting Member
1 Post |
 Posted - 2009-12-28 : 18:25:53
|
| I currently have a query written that takes data that has an ID field, New, Old, and Number fields such as the following:ID New Old Num53746923 A WJ 5In this case, the ID is the primary key and the new field, "A", is an update from the old status of "WJ" which was the 5th update for that ID, represented by the Num. The next time that ID shows up in the data (Num = 6), the Old field would have "A" and the new field would be whatever the updated symbol is. I ran a pivot query to show each unique ID and all of the New symbols listed in the columns to the right depending on how many there were. Some have 2 updates, others have up to 200. I am now trying something else and using the original data which is 21,000,000 records or so structured like the data above and here is what I have so far:select q.id, m.new, cast(m.num as int), cast(maxnum as int) from(select id, new, max(num) as 'maxnum'from master_turner_tablewhere new = 'i'group by id, new) qjoin master_turner_table M on(q.id = m.id and cast(maxnum as int) <= cast(m.num as int))order by q.id, cast(m.num as int)Essentially this gives me the last occurence of a certain symbol that I search for for each ID, in this case "I", and then any symbol that occurs after that until there are no more. It is generated in a list form with each record looking like the example i showed above. My question, is it possible to change this and possibly add a pivot to show any unique sequence of symbols that show up after a given symbol that I search for? In this example, if there are 10 different unique combinations of symbols that occur after "I" throughout the 22,000,000 records, can I summarize that and possibly show a count of how many times each sequence occurs? If that doesn't seem feasible, how could I just show the data that I generate from the query above with only one row for each ID instead of showing them in a list format? Thanks a lot.Drew |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
|
|
|
|
|
|
Need help with query for large database