RESET COUNTERS - 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

RESET COUNTERS

Author

Topic

Freddie
Starting Member

29 Posts

Posted - 2007-10-06 : 06:01:20

Hello All:

i am inserting a counter from a procedure based on the year/month.

I would like to reset the counter to 1 every month.

example of data:

count year/month
_______________

16 200711
17 200711
18 200711
19 200711
20 200711

21 200712 this counter should be reset to 1
22 200712
23 200712

MY PROCEDURE
____________

DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@YMN CHAR(6)
DECLARE c CURSOR
FOR

SELECT DateID,YMN FROM TEST
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID,@YMN
WHILE @@FETCH_STATUS = 0

BEGIN
UPDATE TEST
SET BusDay = @i
WHERE DateID = @DateID
SET @i = @i + 1
FETCH NEXT FROM c INTO @DateID,@YMN
END
CLOSE c
DEALLOCATE c

Thank You
F.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-06 : 06:16:50

Where do you want to show data?
If you use reports, group the report by year/month and use recordnumber and reset it for each group

If you use SQL Server 2005, then make use of Row_number() function

Madhivanan

Failing to plan is Planning to fail

Freddie
Starting Member

29 Posts

Posted - 2007-10-09 : 08:59:34

I want to insert into a table. I am using sql server 2000.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:09:56

[code]DECLARE @Sample TABLE (Counter INT, YearMonth INT)

INSERT @Sample
SELECT 16, 200711 UNION ALL
SELECT 17, 200711 UNION ALL
SELECT 18, 200711 UNION ALL
SELECT 19, 200711 UNION ALL
SELECT 20, 200711 UNION ALL
SELECT 21, 200712 UNION ALL-- this counter should be reset to 1
SELECT 22, 200712 UNION ALL
SELECT 23, 200712

SELECT d.Counter - x.Counter + 1,
d.YearMonth
FROM @Sample AS d
INNER JOIN (
SELECT YearMonth,
MIN(Counter) AS Counter
FROM @Sample
GROUP BY YearMonth
) AS x ON x.YearMonth = d.YearMonth[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-10 : 01:54:03


Select 
	(select count(*) from @Sample where counter<=t.counter and YearMonth=T.YearMonth) as counter,
	yearmonth 
from 
	@sample as T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources