Help on scheduling a data change ??

Dear all,
I have a table with a certain amount of data. One of the data column is
named STATUS. by default all entry have a status of 5 (which means for the
client application a waiting states)
I need to schedule the change of this status from 5 to 1 automatically.
For example I would like that status value for all records with 5 is chaged
to 1 on a particular date and time.
How can I do that ?
regards
sergeHi
UPDATE Table SET status=1 WHERE status =1
AND GETDATE()>='20050101' AND GETDATE() <'20050201'
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Correction
Should be
UPDATE Table SET status=5 WHERE status =1
......
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> Hi
> UPDATE Table SET status=1 WHERE status =1
> AND GETDATE()>='20050101' AND GETDATE() <'20050201'
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
the
> chaged
>|||May be you should check out "Scheduling Jobs" in BOL.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:BDA2B6B7-B9EF-4D79-A836-AAD0EE84B783@.microsoft.com...
> Dear all,
> I have a table with a certain amount of data. One of the data column is
> named STATUS. by default all entry have a status of 5 (which means for the
> client application a waiting states)
> I need to schedule the change of this status from 5 to 1 automatically.
> For example I would like that status value for all records with 5 is
chaged
> to 1 on a particular date and time.
> How can I do that ?
> regards
> serge|||Thnaks for the tip.
Where should I store that SQL querry ?
Does it have to be in a store procedure and then run that store procedure
from a schedule task ?
"Uri Dimant" wrote:

> Correction
> Should be
> UPDATE Table SET status=5 WHERE status =1
> .......
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OkOhaZQjFHA.3148@.TK2MSFTNGP09.phx.gbl...
> the
>
>|||>Does it have to be in a store >procedure and then run that store >procedure
>from a schedule task ?
Yes.
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> Thnaks for the tip.
> Where should I store that SQL querry ?
> Does it have to be in a store procedure and then run that store procedure
> from a schedule task ?
>
> "Uri Dimant" wrote:
>
is
for
automatically.|||Hi,
If I use the task scheduler to run the store procedure, I do not need the
GETDATE function, the UPDATE is enough right ?
"Uri Dimant" wrote:

> Yes.
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EE63176F-C87F-4BEA-A8FB-4D18A47C7959@.microsoft.com...
> is
> for
> automatically.
>
>|||You could just store the creation date in the table and then derive the
Status from the date when you query the data.
David Portas
SQL Server MVP
--|||That was the idea I get at first.
Something like storing the desire date/Time when the status needs to be
changed and then update teh status when the date/time field is corresponding
to current date.
But then if I do that I can use different methode to check this date.
Either I can do it in my client VB application or on the server itself.
But if I do it on the server side what shoudl I use to pol the current date
and check if it correspond to the one store in the table and then if it is
update the field.
Can we make some loop in server procedure ?
Sorry I am new n that and could not get the proper way
thnaks for your help
serge
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>|||What do you mean by derive the status '
by the way I have try to run a script from a new job that I schedule at a
certain time. If I run the script from the querry analyser it works fine, bu
t
when I put it as a new job and load the script file to execute, the set the
time to schedule it, it is not updating or may be not runing at all.
Script is :
EXEC dbo.UpdateStatus
GO
and UpdateStatus is a store procedure
Any idea?
thaks
"David Portas" wrote:

> You could just store the creation date in the table and then derive the
> Status from the date when you query the data.
> --
> David Portas
> SQL Server MVP
> --
>

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

Help me write my first Update Trigger (sql svr 2000)

can someone help me write a Trigger? I have never written a trigger. This is for SQL Server 2000

Table FOO:
----
ID (numberic counter)
Status (Char)
etc..

Table BAR:
----
ID
Status
DateUpdated (getdate())

Whenever the Status in Table FOO is updated, I need to INSERT a new record into BAR with the ID and Status

~LeCREATE TRIGGER FOO_Update ON [FOO]
FOR Insert, Update
AS
Insert into Bar
(ID,
Status,
DateUpdated)
Select ID,
Status,
Getdate()
From inserted

...but you should really think of just adding the DateUpdated field to FOO with a default of getdate() for new records and having the trigger update it:

CREATE TRIGGER FOO_Update ON [FOO]
FOR Update
AS
Update FOO
set DateUpdated = Getdate()
From FOO
inner join inserted on FOO.ID = inserted.ID

blindman|||B.E.A.U.-tiful

Works Perfectly!

Thank you very very much!

~Le