all rows plus min / max values using a single stored procedure

Question

I have a custom data source which pulls out data form a flat file. The flat file contains a timestamp , source and data. I can use sp_execute to execute a select query against the flat file.

I'm currently using 2 stored procedures . - one which runs a select * from flat_file into a temp table - the other which does a select min/max from flat_file grouping by source into another temp file

Im using the data retrieved using the stored procedures in a SSRS report

Is is possible in a a single stored procedure to retrieve all the rows from the file within a date range and also identify the min/max values for each group retrieved ?e

Answer 1

yes, combine all logic into one procedure and return a join of your two temp tables. you don't give any code, column names, etc, so this is a guess:

CREATE PROCEDURE AllInOne
(
    @param1....

)

--populate temp table 1
...

--populate temp table 2
...

SELECT
    t1.*, t2.*
    FROM #Temp1            t1
        INNER JOIN #temp2  t2 ON t1.PK=t2.PK
    ORDER BY ....

go

Answer 2

SSRS can only handle one resultset from a dataset.

Without using a temp table (like KM's answer), this is 2 calls to the database.

However, if i read you correct, the 2 resultsets are fundamentally different: the min/max is an operation on the 1st result set after filtering and not on the original data.

So, you could do this in SSRS if you are grouping in a table control by using Min and setting the Scope parameter as your grouping