April 23, 2007 at 10:32 am
Hi guys,
i'm trying to write a script tht will update a specific column if the time between col last "Date Seen" and Getdate() is greater than 12 months. I tried the following query but it didn't work.
Update
Registry_Dates
Set
Lost_to_Followup = 'Yes'
where
Exists(select Patient_Demographics.KPAIDS_NO, Max(Date_Seen)as DS,
datediff
(mm,Max(Date_Seen),Getdate()) as Months from Patient_Demographics inner join
Registry_Measurements
on Patient_Demographics.KPAIDS_NO = Registry_Measurements.KPAIDS_NO
inner
join Registry_Dates
on
Patient_Demographics.KPAIDS_NO = Registry_Dates.KPAIDS_NO
group
by Patient_Demographics.KPAIDS_NO
Having
datediff(mm,Max(Date_Seen),Getdate())> 12)
go
can you guys say where i'm going wrong?, thx much
April 23, 2007 at 11:39 am
The update has to apply to specific rows. You are using a where exists() clause, which returns a true or false, not a set of rows.
What you need to do is return the PK of the table in the subquery and then match that.
Update Registry_Dates
Set Lost_to_Followup = 'Yes'
from ( select TablePK, ...
group by ....
having ...
) a
where Registry_Dates.TablePK = a.TablePK
April 23, 2007 at 12:34 pm
oooh my bad
thx a lot man
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply