Content-Transfer-Encoding: base64Did 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:
ÑSPÕÛÛÝ[Y\Ý]\ËÝ\Ë\Ý\ FROM Consumer
JOIN Status on Consumer.ConPK=Status.ConFK
QÕUTÒSÝ\ÈÛÛÛÝ[Y\ÛÛÏRÝ\ËÛÛK
ÒTHÝ]\Ë]HB
4TÄT5BÔ
7FC"äFFRe$ôÒStatus Stat2 WHERE
Ý]ÛÛÈHÛÛÝ[Y\ÛÛÈÔÕTBY Stat2.ConFK)
SÝ]\Ë\HH\ÝYSÀurs.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:
4TÄT5B6öç7VÖW"â , Status.*, Hours.AppHours
FROM Consumer
ÒSÝ]@s on Consumer.ConPK=Status.ConFK
LEFT OUTER JOIN Hours on Consumer.ConPK=Hours.ConFK
ÒTHÝ]\Ë]HB(SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE
Stat2.ConFK = Consumer.ConPK AND Status.Type = "Approved" GROUP BY
Ý]ÛÛÊBSÝ\Ë]HH
ÑSPÕPV
Ý\s2.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,
[Z]her 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.
@nks.
Ù[Ù^Ù\ÜÚ]H][Ý[È[[ÝYH[excessive quoting removed by server]
