August 8, 2001 at 10:19 am
I'm migrating a data report from a legacy system to SQL Server/Crystal Rpts. The report is grouped on 15 match conditions... detail is shown within each group where the 15 fields match. I've never done this so need all the advice I can get. My thought is to select distinct all 15 fields in the match condition and add a unique id field (1, 2, 3, etc.) to each distinct row for insert into a table. Having done that, I'll pull the rest of the fields (detail) for update into the same table. Now, where I had a table containing maybe 20 rows, I'll have a table containing possibly 500 rows because of the added detail. Now the ID field should contain several rows with 1, several with 2, etc. I'm at the point where I've selected distinct the 15 fields in the match condition. I haven't yet figured out how to add the unique id 1,2,3, etc. Does anyone know how I can do this? Thanks for any and all help.
Tracey
August 8, 2001 at 11:30 am
this look like it would need two tables rather than one -if u want a cursor less solution
if u can post the table structures and some sample data we might be able help
August 8, 2001 at 3:25 pm
I think I have found the answer. I'm going to populate my table with only the distinct match condition fields with also an ID field using the IDENTITY property.
Next, when I update the table with the detail, the records in each group should have the same ID number. I hope this works.
December 8, 2002 at 4:02 pm
I worked on a health care claims systems where we had literally hundreds of thousands of claims daily. We did exactly what you are describing. We took it one step further though and generated the unique value first, then inserted it into the table. This field compression saved us tons of space, back when space wasn't as cheap as it is today. Especially considering the volume of records we had.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply