• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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

0
Jeff S
Asked:
Jeff S
  • 4
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now