Microsoft, SQL, 2005, Update Script Help Needed

Experts,

I have a field in my PatientProfile table  - 'ReleaseOfInformationIndicatorDate' I wish to update. The only twist is I want to set this date equal to the patients last visit. That table is Patientvisit. How can I tell my update script to give me the date of last visit in this field right? If no visit, leave Null.
update PatientProfile
inner join patientvisit pv on pp.PatientProfileId = pv.PatientProfileId
Set ReleaseOfInformationIndicatorDate = max(pv.visit)

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
tigin44Commented:
This is tried...

update pp
Set ReleaseOfInformationIndicatorDate = t.VisitDate
from PatientProfile pp
        inner join (SELECT PatientProfileId, ISNULL(max(pv.visit), GETDATE()) AS VisitDate
                    FROM patientvisit pv
                    GROUP BY pv.PatientProfileId
                                    ) t ON t.PatientProfileId = pp.PatientProfileId
0
 
tigin44Commented:
try this

update pp
Set ReleaseOfInformationIndicatorDate = max(pv.visit)
from PatientProfile pp
                          inner join patientvisit pv on pp.PatientProfileId = pv.PatientProfileId
0
 
Jeff SAuthor Commented:
I am so sorry, If no visit then set equal to current date.

So, I need it to first look for a date ... if none, set to todays date. Make sense?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tigin44Commented:
then try this
update pp
Set ReleaseOfInformationIndicatorDate = ISNULL(max(pv.visit), GETDATE())
from PatientProfile pp
                          inner join patientvisit pv on pp.PatientProfileId = pv.PatientProfileId
0
 
Jeff SAuthor Commented:
I get this back:

An Aggregate may not appear in the set list of an update statement
0
 
tigin44Commented:
sory for my carefullnes... this does what you want

update pp
Set ReleaseOfInformationIndicatorDate = t.VisitDate
from PatientProfile pp
        inner join (SELECT PatientProfileId, ISNULL(max(pv.visit), GETDATE()) AS VisitDate
                              FROM patientvisit pv
                              WHERE pp.PatientProfileId = pv.PatientProfileId) t ON t..PatientProfileId = pv.PatientProfileId
0
 
Jeff SAuthor Commented:
Server: Msg 107, Level 16, State 2, Line 3
The column prefix 'pp' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 3
The column prefix 'pv' does not match with a table name or alias name used in the query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.