grouping / reporting question - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

grouping / reporting question

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-10-11 : 11:11:20

Hi,

I've written a series of procedures that are writing output lines into a common table that I'm then reporting on.

I've included some simplified code to show waht I've done.

I was hoping someone could tell me if it is possible to remove the loop that I've created to get the report headings.

I've tried to write this as a with statement, but can't quite get it right.

--------

CREATE TABLE JobReport(
JobRunId int NOT NULL,
ReportDate datetime NOT NULL,
Process] varchar(200) NOT NULL,
Line varchar(200) NOT NULL
)

INSERT INTO jobreport(jobrunid,reportdate,process,line)
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A1' union
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A2' union
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A3' union
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b1' union
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b3' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c1' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c3' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c4' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d1' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d3'

SELECT IDENTITY(INT,1,1) id,headings.Process INTO #headings from
(
SELECT Process FROM jobreport
WHERE jobrunid = 1
GROUP BY Process
) headings

BEGIN
DECLARE @max INT, @COUNT INT,@heading AS VARCHAR(200),@report VARCHAR(MAX), @jobrunid INT
SET @jobrunid = 1
set @report = ''

SELECT @max = COUNT(*) FROM #headings
SET @count = 0

WHILE @count < @max
BEGIN
SET @count = @count + 1

SELECT @heading = h.Process FROM #headings h WHERE h.id = @count

SELECT @report = @report + ' ----' + @heading + '----' + CHAR(13)
SELECT @report = @report + line + CHAR(13)
FROM jobcontrol.dbo.jobreport rep
WHERE rep.jobrunid = @jobrunid
AND rep.Process = @heading
END

--------

any help would be appreciated.

thanks

Sean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-11 : 11:24:07

[code]CREATE TABLE #Headings
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Process VARCHAR(200)
)

DECLARE @Heading VARCHAR(200),
@Report VARCHAR(MAX),
@CurrentRow INT,
@MaxRow INT

INSERT #Headings
(
Process
)
SELECT Process
FROM JobReport
WHERE JobRunID = 1
GROUP BY Process

SELECT @CurrentRow = 1,
@MaxRow = @@ROWCOUNT,
@Report = ''

WHILE @CurrentRow <= @MaxRow
BEGIN
SELECT @Heading = Process
FROM #Headings
WHERE ID = @CurrentRow

SELECT @Report = @Report + ' ----' + @Heading + '----' + CHAR(13)

SELECT @Report = @Report + Line + CHAR(13)
FROM JobControl.dbo.JobReport
WHERE JobRunID = 1
AND Process = @Heading

SET @CurrentRow = @CurrentRow + 1
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-10-11 : 11:46:12

I was wondering if it was possible to produce the same report without using a loop (while) statemnt, but I guessthe while loop is needed.

Sean

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-11 : 11:50:52

you cna probably use a method under point 8 here:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

you can "loop update" another table with your report and then select from it.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-10-12 : 05:37:41

Thanks for the article, very useful

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources