I have two tables:
SamplesTblcontains samples every 5 seconds of a bike ride
RxTblcontains the prescribed stages and other values of a bike ride
SamplesID int SessionNum int StageNum int RunTime int
RxID int RxNum int RxStageNum int RxRunTime int
In certain cases (not all) a prescribed bike ride is broken up into more stages than prescribed (5 stages are turned into 20 stages). Currently I use cursors and temporary tables to take these 20 stages to map them back to the 5 prescribed stages based on the number of stages it takes from the samples table to equal the prescribed run time.
For example, stage 1 is prescribed at 5 minutes but the actual exercise data would contain stages 1 through 5 that equal the 5 minutes (1-4: 65 seconds, 5: 40 seconds), stage 2 is prescribed at 3 minutes but the actual exercise data would contain stages 6 through 8 that equal the 3 minutes (1-2: 65 seconds, 3: 50 seconds), etc.
Is there a way to retrieve the result set without using cursors and/or temporary tables?