Did you run it as part of the first set of code that I gave you?
It runs here with no errors in VFP9 with the latest hot fix and SPs. ;)
What error is it giving you? Maybe a line split somewhere due to the
email formatting?
This is the full code:
CREATE CURSOR Consumers;
(ConPK int)
INSERT INTO consumers (ConPK ) VALUES (1)
INSERT INTO consumers (ConPK ) VALUES (2)
INSERT INTO consumers (ConPK ) VALUES (3)
*!* 1
*!* 2
*!* 3
*!* Status
CREATE CURSOR Status;
(StatPK int,;
ConFK Int,;
Date date,;
Type c(10))
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(1,1,{^2018/1/15},"Pending")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(2,1,{^2018/4/15},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(3,2,{^2018/6/1},"Closed")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(4,2,{^2018/4/15},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(5,3,{^2017/11/22},"Pending")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(6,3,{^2018/2/3},"Approved")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(7,3,{^2018/3/12},"Suspended")
INSERT INTO Status (StatPK , ConFK, Date, Type) VALUES
(8,3,{^2018/5/11},"Approved")
CREATE CURSOR Hours;
(HourPK int,;
conFK int,;
Date d,;
Apphours int)
*!* PK,ConFK,,Date,,AppHours
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES
(1,2,{^2017/7/3},10)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES
(2,2,{^2018/3/12},14)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES
(3,3,{^2018/4/1},20)
INSERT INTO Hours (HourPK , ConFK, Date, AppHours) VALUES
(4,3,{^2018/5/1},12 )
SELECT Status.ConFK, LastHours.AppHours;
FROM Status ;
INNER JOIN (SELECT ConFK, MAX(Date) AS MaxDate;
FROM Status;
GROUP BY ConFK) M ON Status.ConFK = M.ConFK AND Status.Date =
M.MaxDate ;
AND Type = "Approved";
LEFT JOIN (SELECT Hours.ConFK, Hours.AppHours;
FROM Hours;
INNER JOIN (SELECT ConFK, MAX(date) AS MaxHoursDate;
FROM Hours;
GROUP BY ConFK) MH ;
ON Hours.ConFK = MH.ConFK AND Hours.Date =
MH.MaxHoursDate ) LastHours ;
ON STatus.ConFK = LastHours.ConFK
Frank.
Frank Cazabon
On 22/06/2018 12:21 PM, Ken Dibble wrote:
>
>> SELECT Consumer.*, Status.*, Hours.AppHours
>> FROM Consumer
>> JOIN Status on Consumer.ConPK=Status.ConFK
>> LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
>> WHERE Status.Date =
>> (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
>> Stat2.ConFK = Consumer.ConPK GROUP BY Stat2.ConFK)
>> AND Status.Type = "Approved"
>> AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE
>> Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
>
> I actually can't get this to run without errors. Interpolating, I
> **think** you meant:
>
>> SELECT Consumer.*, Status.*, Hours.AppHours
>> FROM Consumer
>> JOIN Status on Consumer.ConPK=Status.ConFK
>> LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
>> WHERE Status.Date =
>> (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
>> Stat2.ConFK = Consumer.ConPK AND Status.Type = "Approved" GROUP BY
>> Stat2.ConFK)
>> AND Hours.Date = (SELECT MAX(Hours2.Date) FROM Hours Hours2 WHERE
>> Hours2.ConFK = Consumer.ConPK GROUP BY Hours.PK)
>
> However, I get "Function missing )" on the last subquery on this this,
> and neither intellisense nor I can find any unmatched parens.
>
> Also, the first subquery is grouping on the Status table FK, while the
> second subquery groups on the Hours table PK. I think they should both
> group on the same key, but I don't understand which one that should be.
>
> Thanks.
>
> Ken
>
>
[excessive quoting removed by server]