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

Author

Topic

Freddie
Starting Member

29 Posts

Posted - 2007-11-09 : 16:11:33

Hello everyone:

this procedure resets the business day to one for every month. It works fine except for the month of October. any idea or suggestions?

DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@DtTimeD DATETIME,
@LASTDAY DATETIME
DECLARE c CURSOR
FOR
--
-- LASTDAY is the last day of the month
-- the counter will reset to 1 on the first of each month
--
SELECT DateID, DtTimeD, DATEADD(dd, -DAY(DATEADD(m,1,DtTimeD)), DATEADD(m,1,DtTimeD)) 'LASTDAY'
FROM D_DATE
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
WHILE @@FETCH_STATUS = 0
--
-- update the business day in D_DATE
--
BEGIN
UPDATE D_DATE
SET BusDay = @i
WHERE DateID = @DateID
--
-- reset the counter to 1 if it's the last day of the month
--
IF @DtTimeD = @LASTDAY
SET @i = 1
ELSE
SET @i = @i + 1
--
IF @@ROWCOUNT = 500
COMMIT
--
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
END
CLOSE c
DEALLOCATE c
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:14:12

Are you using SQL Server 2005?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:16:37

Because there are 30 days in November and 31 days in October?
Because last day of October was Wednesday, neither weekend nor holiday?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:19:34

You can also use UPDATE .. WHERE CURRENT OF .. to speed you cursor solution up.
But I think you do not need a cursor-based solution.

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

Freddie
Starting Member

29 Posts

Posted - 2007-11-09 : 16:23:08

i am using SQL Server 2000 and the counter works for other months that have 31 days....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:27:47

Do you have a clustered index on the D_DATE table for DtTimeD column?
Or possible the DateID column? And if so, do DateID column correspond to the DtTimeD column?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 16:36:16

Try this one

CREATE TABLE	#Stage
		(
			RowID INT IDENTITY(1, 1),
			DateID INT,
			theMonth INT,
			theSequence INT
		)

INSERT		#Stage
		(
			DateID,
			theMonth
		)
SELECT		DateID,
		DATEDIFF(MONTH, '19000101', DtTimeD)
FROM		D_DATE
WHERE		WkDayIn = 'Yes'
		AND HolidIn = 'No'
ORDER BY	DtTimeD

UPDATE		s
SET		s.theSequence = 1 + s.RowID - r.RowID 
FROM		#Stage AS s
INNER JOIN	(
			SELECT		theMonth,
					MIN(RowID) AS RowID
			FROM		#Stage
			GROUP BY	theMonth
		) AS r ON r.theMonth = s.theMonth

UPDATE		w
SET		w.BusDay = y.theSequence
FROM		#Stage AS w
INNER JOIN	D_DATE AS y ON y.DateID = w.DateID

DROP TABLE	#Stage

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

Freddie
Starting Member

29 Posts

Posted - 2007-11-12 : 09:17:29

Thank you very much...works much better than my proc.

Have a nice day!!

Freddie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 09:29:34

And much faster too, I can presume?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 02:55:26

quote:
Originally posted by Peso

And much faster too, I can presume?

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

Thats understood by default

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 03:13:00

Well, the original error was that it wouldn't work for the month of October.
That was the main priority.
If I also managed to get the code to run faster.... That's a bonus.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources