How to reset the count to 0 - 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

SQL Server 2008 Forums

Transact-SQL (2008)

How to reset the count to 0

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2014-01-09 : 03:14:10

I have a table with records as follows: Its just a sample table for understanding the scenario.

Create table resetcount
(
userid int,
totalhighprtjobs int,
[date] datetime
)

insert into resetcount Values (147,0,GETDATE())
insert into resetcount Values (169,1,GETDATE())
insert into resetcount Values (176,0,GETDATE())
insert into resetcount Values (187,1,GETDATE())
insert into resetcount Values (188,1,GETDATE())

Everytime when i assign a job to a userid the totalhighprtjobs gets increased by 1.it will assign to all the users randomly and equally. Suppose if all the count of totalhighprtjpbs becomes 1 then i want to reset all those to 0.if suppose today there are 3 jobs assigned to useids 169,187,188.tomorrow while assigning jobs since the count of highprtjobs of userid 147 and 176 are 0 first these two will be assigned a job.After assigning to these two the count ot totalhighprtjobs wil be 1 for all,then i want to reset the count to 0 for all after assigning to those two.

Can you guide me in this ..

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-09 : 04:31:34

do you mean that execute the following after assigning?

-----------------------------------------

--if all records are assigned, reset.
UPDATE resetcount
SET totalhighprtjobs = 0
WHERE NOT EXISTS(
SELECT *
FROM resetcount
WHERE totalhighprtjobs = 0)

From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 05:01:48

sounds like a simple update to me


update resetcount
set totalhighprtjobs = 0
where totalhighprtjobs > 1
and date < GETDATE()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources