SQL Server Performance Forum – Threads Archive

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 @[email protected]+1
SET @numitems=(SELECT records FROM #data WHERE [email protected])
SET @i=0
WHILE @i<@numitems
BEGIN
SET @[email protected]+1
SET @[email protected]+1
SET @data=(SELECT data from #actualdata where [email protected])
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

---

Isnt this enough?
CREATE TABLE [stuff] ([data] [varchar] (50) )
GO 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’) Go Select id=identity(int, 1,1),data into #t from [stuff] Select data,(select count(*) from #t where id<=T.id and data=T.data) from #t T drop table #t
Drop table [stuff]
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.

---

]]>