On Wed, Jun 19, 2013 at 4:05 PM, Patti Jakusz <[email protected]> wrote:
> I'm not far enough along for that command yet. I'm getting illegal select
> function on the first half of your solution. Naturally, my actual tables
> are not as simple as what I wrote. My ClientId is a computed field.
>
> This is a little more complicated than what I had been doing - projecting
> a temp table with my dates sorted in ascending order. Then I just do a
> regular update command and I skip the "max" specification. It will update
> each client record a bunch of times (however many times I have a HomeVisit
> record), but since the records are already sorted, the last update will be
> the latest date.
>
> It just seems like the "max" spec should work in the update command, and
> it never has for me.
>
> Thanks though,
> Patti
>
Patti,
Your syntax error is my fault for not proofreading my own code. Let me try
again, from the top: It takes two commands, and one clean-up command:
CREATE TEMP VIEW MaxVisitDates +
(ClientID, MaxVisitDate) AS +
SELECT +
ClientID, MAX(VisitDate) As MaxVisitDate +
FROM HomeVisit +
GROUP BY ClientID
UPDATE ClientMaster +
SET LastHomeVist = MaxVisitDate +
FROM ClientMaster, MaxVisitDates h2 +
WHERE ClientMaster.ClientID = h2.ClientID
DROP VIEW MaxVisitDates
