May 26, 2006 at 12:51 pm
Here's my problem.
input = 'mgr' and/or 'company'
'company has a record for each year 1996,1997...2006 but may contain a different mgr for each year.
I need to find the latest company record based on year in the file. Note, the latest year may not be 2006. it could vary from company to company.
using this record I would go against a detail file for that company and mgr.
I need to know how to determine the latest record for company and then use it as input to find the detail records.
I hope I explained this so that it makes sense.
Thanks,
May 26, 2006 at 1:29 pm
You don't give table/column names nor data types. And you don't give sample data.......so......
SELECT *
FROM MyTable
WHERE MyDate = (SELECT MAX(MyDate)
FROM MyTable
WHERE mgr = 'mgr's name'
OR company = 'company name'
GROUP BY MyDate)
-SQLBill
May 26, 2006 at 1:49 pm
Believe it or not but that was a big help. I needed to use the SELECT max statement but I didn't know enough SQL to know it existed. I modified my query and it looks like it is woring the way I need it to.
Thanks a lot!!!
May 30, 2006 at 8:21 am
I thought I had it right but I need more help.
I tested my query as you suggested and it worked great, but when I added another level of complexity to it, it doesn't work.
I am trying to find a mgr from the list brought back from the subquery.
I receive a message about having an EXISTS with subquery.
My main problem is with the WHERE c.mgr in portion.
If you can provide any further help I would appreciate it.
I have included a portion of the code:
declare @varmgr char(6)
set @varmgr = 'abc123'
SELECT a.PROVNO as PROVNO, b.provNo as provNo
FROM COMPARE a
left outer JOIN tblpc c on a.PROVNO = c.provno and a.FYE = c.dteFYE
join tblProv1 b on a.PROVNO = b.provno
WHERE c.mgr in (select t.provNo, t.dteFYE, t.Mgr
from tblpc t
where dteFYE = (SELECT MAX(dteFYE)
from tblpc
where provno = t.provno) and t.Mgr = @varmgr )
Order by a.PROVNO, a.FYE
May 31, 2006 at 7:03 am
Couple of thoughts.....
1. Have you run the subquery by itself to ensure it returns something that matches c.mgr?
2. This portion of the WHERE could be a problem: and t.Mgr = @varmgr )
t.Mgr only exists in the subquery. So you can compare c.mgr to t.mgr since that's where the subquery exists. But at this point in the WHERE the subquery isn't part of it, so you can't compare t.mgr to @Varmgr. In other words... t.mgr only exists here:
WHERE c.mgr in (select t.provNo, t.dteFYE, t.Mgr from tblpc t where dteFYE = (SELECT MAX(dteFYE) from tblpc
where provno = t.provno)
-SQLBill
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply