Sequential Line Count in a field

I need to be able to create a report that has a line counter on each row:

information RowCnt
datastuff 1
datastuff 2
datastuff 3

Then, I need to change the rowcount back to zero when a break occurs based on a change in a field:

information RowCnt
datastuff 1
datastuff 2
datastuff 3

stuffdata 1
stuffdata 2
stuffdata 3


Any ideas?

A rather quick (and dirty approach). But it works if you tweak it to match your data.

1. The table:

CREATE TABLE [stuff] ([data] [varchar] (50) )
GO



2. Insert some sample data:


INSERT INTO stuff (data) VALUES ('datastuff')
INSERT INTO stuff (data) VALUES ('datastuff')
INSERT INTO stuff (data) VALUES ('datastuff')
INSERT INTO stuff (data) VALUES ('datastuff')
INSERT INTO stuff (data) VALUES ('stuffdata')
INSERT INTO stuff (data) VALUES ('stuffdata')
INSERT INTO stuff (data) VALUES ('stuffdata')
INSERT INTO stuff (data) VALUES ('stuffdata')
INSERT INTO stuff (data) VALUES ('otherstuff')




3. query to retrieve your report: Note that you may decide to use table variables instead of temporary tables. Hope you can make sense out of this mess.


CREATE TABLE #temp (rcount int ,data varchar(50))
Select row=IDENTITY(int,1,1),data into #actualdata from stuff order by data
Select row=IDENTITY(int,1,1),count(data) as records,data into #data from stuff group by data
SELECT * FROM #data
SELECT * FROM #actualdata
DECLARE @i int
DECLARE @numitems int
DECLARE @data varchar(50)
DECLARE @itemcount int
DECLARE @j int
SET @itemcount=0
SET @j=0
WHILE @itemcount<(SELECT MAX(row) FROM #data)
BEGIN
SET @itemcount=@itemcount+1
SET @numitems=(SELECT records FROM #data WHERE row=@itemcount)
SET @i=0
WHILE @i<@numitems
BEGIN
SET @i=@i+1
SET @j=@j+1
SET @data=(SELECT data from #actualdata where row=@j)
INSERT into #temp(rcount,data) values (@i,@data)
END

END

SELECT * FROM #temp

drop table #temp
drop table #data
drop table #actualdata





Nathan H. Omukwenyi

quote:Originally posted by Madhivanan

Isnt this enough?

But In Crystal Reports there is an option for running total which can be set and Count and reset to every group. See if you are able to do the same in Reporting services also

Madhivanan

Failing to plan is Planning to fail

The Crystal Reports running counter would be a wonderful option, but I am not able to replicate that function in SQL RS. If anyone out there has a method (preferably without having to build and retrieve and remove temp tables), it would be most appreciated.

Thanks

Madhivanan's solution works the exact same way using a TABLE variable instead of a #TEMP table to avoid TEMPDB if that was your goal:

-- Create the TABLE variable and populate it with your values
declare @tblVar table (id int IDENTITY(1,1), data varchar(50) )
insert into @tblVar (data)
select data from [stuff]

Select data,(select count(*) from @tblVar where id<=T.id and data=T.data) from @tblVar T

A totall different solution would involve using a cursor for the selects, and simply having a @Cntr value, and a @OldValue variable so that you would check the previous data value and if it matches then you increment the counter, if it doesn't match you set the @OldValue to the new value and reset the @Cntr.