Sorry, I'm not much of a reader.
You will need to read about looping and temp tables I would guess.
As a rough outline, here is what I would do.
Select the information that you want to appear on the report into a temp table. Something like:
Select * into #temp from tablea
then create another temp table to hold the results with the duplicate records. An easy way to copy the structure without having to use the create table command is:
Select * into #results from #temp where 1=2
since no records will match the 1=2 condition that table is created but without anything in it.
Now for the looping...
declare @loopCount INT
While Exists(Select * from #temp)
begin
Select top 1 @loopCount = quantity field from #temp
while @loopCount > 0
begin
insert #results
select top 1 * from #temp
set @loopCount = @loopCount -1
end
(now a hard part since there are multiple ways to delete)
(if you are sql 2005 use:)
DELETE TOP (1) FROM #temp
(else)
delete from #temp as t join (select top 1 * from #temp) as ss ON ss.uniquefield = t.uniquefield
end
this should take the first record from the temp table and replicate it X times in the results table and then remove the first record from the temp. You want to make sure that the field(s) in the 'On' statement are distinct enough to only delete the first record. It might be as simple as the item number, but it might be more complex, in which case you would add additional clauses using AND between the fields
Hopefully at this point we have a nice big results table, so let's see it...
SELECT * FROM #results
and that should give you the values that you want.
In crystal, you would find the stored proc that you just created and use that as a datasource. You might need to set up an ODBC connection on your computer so that Crystal knows how to find the stored proc...but you probably already have that if you are connecting to a database
There's a lot to do, but once you get used to the stored proc, it is way more versatile than just connecting to tables.
IP Logged
