Query working only on alternate runs

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 2000 Forums

Transact-SQL (2000)

Query working only on alternate runs

Author

Topic

kadrove
Starting Member

13 Posts

Posted - 2003-09-05 : 17:58:56

Help, please!

With my code I am trying to update a column only when there are nulls. It is set to run every hour. The problem is it works on the first run, and next time it runs it inserts all nulls to the entire column, then it works the following run...It doesn't appear that it is just amending new data.

I have two tables:

tblIncidents
(IncidentID,IncidentDate, IncidentDispatchedTime,IncidentPlatoon)

tblKDTestShift
(DayNumber, Shift)

I am attempting to insert the correct Shift into tblIncidents.IncidentPlatoon. tblKDTestShift contains the shift pattern. example:

DayNumber Shift
1 A
2 B
3 A
4 C
and so forth...

My code takes the IncidentDate and IncidentDispatchedTime to determine the correct shift:

Update tblIncidents
set tblIncidents.IncidentPlatoon =
(select tblKDTestShift.Shift
From
(select IncidentID, convert(nvarchar(10),IncidentDate,101) as IncidentDate, IncidentDispatchedTime, (DateDiff(d,'01/01/2003', tblIncidents.IncidentDate) + (case
When (tblIncidents.IncidentDispatchedTime >= '080000') then '1'
else '0'
end )) %24 as DayNum
From tblIncidents) a
left outer join
tblKdTestShift
on tblKDTestShift.DayNumber = a.DayNum

where tblIncidents.incidentID = a.incidentid
and tblIncidents.IncidentDispatchedTime is not null
and tblIncidents.IncidentDispatchedTime <> ' '
and tblIncidents.IncidentPlatoon is null)

I've been on this for hours, can anyone spot what I am missing?

Thanks in advance!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-05 : 19:03:11

You are updating every row on every run.
The where clause should be outside the brackets to only update those which are null.
on tblKDTestShift.DayNumber = a.DayNum )
where ....

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kadrove
Starting Member

13 Posts

Posted - 2003-09-05 : 19:07:51

I have tried moving the where clause outside of the brackets, but when I do that I move 'a' outside of its scope and it doesn't recognize a.incidentid

Thanks.
kadrove

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-08 : 21:35:06

Something is slightly off here:


Update tblIncidents
set tblIncidents.IncidentPlatoon = 
(select tblKDTestShift.Shift
From 
(select IncidentID, convert(nvarchar(10),IncidentDate,101) as IncidentDate, IncidentDispatchedTime, (DateDiff(d,'01/01/2003', tblIncidents.IncidentDate) + (case 
When (tblIncidents.IncidentDispatchedTime >= '080000') then '1' 
else '0'
end )) %24 as DayNum
From tblIncidents) a 
left outer join 
tblKdTestShift
on tblKDTestShift.DayNumber = a.DayNum 
where tblIncidents.incidentID = a.incidentid 
and tblIncidents.IncidentDispatchedTime is not null 
and tblIncidents.IncidentDispatchedTime <> ' '
and tblIncidents.IncidentPlatoon is null)

I am not sure if this works, but it should point you in the right direction:


Update tblIncidents
set tblIncidents.IncidentPlatoon = tblKDTestShift.Shift FROM
(select IncidentID, convert(nvarchar(10),IncidentDate,101) as IncidentDate, IncidentDispatchedTime, (DateDiff(d,'01/01/2003', tblIncidents.IncidentDate) + (case 
When (tblIncidents.IncidentDispatchedTime >= '080000') then '1' 
else '0'
end )) %24 as DayNum
From tblIncidents) a 
left outer join 
tblKdTestShift
on tblKDTestShift.DayNumber = a.DayNum 
(Type Of Join) JOIN tblIncidents.incidentID = a.incidentid 
WHERE tblIncidents.IncidentDispatchedTime is not null 
and tblIncidents.IncidentDispatchedTime <> ' '
and tblIncidents.IncidentPlatoon is null

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources