Ken,
Sorry, I hadn't realised you were responding to Ted's code.
Frank.
Frank Cazabon
On 22/06/2018 01:11 PM, Frank Cazabon wrote:
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]