Update query with MAX and GROUP BY with other fields

I have the following table-structure (which I cannot change)
qPortfolioAndValue (PK: ObjID_Cportfolio) - Account (PK: ObjID, FK: ObjID_Cportfolio)
     qPortfolioAndValue has 0 or more records in Account
Account (PK: ObjID) - cashMovement (PK: ObjID, FK: ObjID_CAccount)
   an Account has 0 or more record(s) in cashMovement
Account (PK: ObjID) - securityMovement (PK: ObjID, FK: ObjID_CAccount)
   an Account has 0 or more record(s) in cashMovement

What I need:
I want to update the record in qPortfolioAndValue (fields LatestMove, LatestPrice) with the following record:
The record of cashMovement or SecurityMovement with the most recent date (= max date)

The problem is that I  manage to get the latest record of CashMovement with the following query:
select QP.ObjID_Cportfolio, Max(CM.Date)
from cashMovement CM
   left join Account A ON CM.ObjID_CAccount = A.ObjID
   left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
GROUP BY QP.ObjID_Cportfolio

But I don't know how to get the other field (CM.Price) to use it in an update query
And if I got that field, how about the latest of cashMovement or securityMovement ?
LVL 53
DhaestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
can you please try this one?



select QP.ObjID_Cportfolio as id, Max(CM.Date) as dt,price=
(
	select c1.price
	from cashMovement C1 
	   left join Account Aa ON C1.ObjID_CAccount = A1.ObjID
	   left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
		where q1.objID_Cportfolio=qp.objID_Cportfolio and c1.date=cm.dt
)
	from cashMovement CM 
	   left join Account A ON CM.ObjID_CAccount = A.ObjID
	   left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
	GROUP BY QP.ObjID_Cportfolio

Open in new window

0
RiteshShahCommented:
or may be this one
select id,dt,price=
(
	select top 1 c1.price
	from cashMovement C1 
	   left join Account Aa ON C1.ObjID_CAccount = A1.ObjID
	   left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
		where q1.objID_Cportfolio=t.id and c1.date=t.dt
)
from
(
	select QP.ObjID_Cportfolio as id, Max(CM.Date) as dt
		from cashMovement CM 
		   left join Account A ON CM.ObjID_CAccount = A.ObjID
		   left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
		GROUP BY QP.ObjID_Cportfolio
) as t	

Open in new window

0
DhaestAuthor Commented:
>> c1.date=cm.dt
The uvw_qPortfolioAndValue does not contain that Date-field.
I want to update a date-field in uvw_qPortfolioAndValue  with the latest date (= Max(CM.Date) from cashMovement)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DhaestAuthor Commented:
I think that you're second query returns what I want (I need to verify some  items).
How can I use this query to update the values in uvw_qPortfolioAndValue  (id, dt and code)

select id,dt,code=
(
	select top 1 c1.code
	from cashMovement C1 
	   left join Account A1 ON C1.ObjID_CAccount = A1.ObjID
	   left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
		where q1.objID_Cportfolio=t.id and c1.date=t.dt
)
from
(
	select QP.ObjID_Cportfolio as id, Max(CM.Date) as dt
		from cashMovement CM 
		   left join Account A ON CM.ObjID_CAccount = A.ObjID
		   left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
		GROUP BY QP.ObjID_Cportfolio
) as t	

Open in new window

0
RiteshShahCommented:
I didn't use date field of uvw_qPortfolioAndValue, I have tried to get latest price based on the max date comming in base query to cashMovement of inner query.
0
DhaestAuthor Commented:
>> I didn't use date field of uvw_qPortfolioAndValue, I have tried to get latest price based on the max date comming in base query to cashMovement of inner query.

I saw it, I was too quick with posting my comment !
0
RiteshShahCommented:
ok. try the query and let me know whether it is giving proper results or not.
0
DhaestAuthor Commented:
As far as I saw it quickly, it seems to be ok. How can I use this query to update the values in uvw_qPortfolioAndValue  (dt and code)
0
RiteshShahCommented:
here you go,

check on test DB first and do check ON condition of update, mainly field name, I may made mistake in that.



update uvw_qPortfolioAndValue set uvw_qPortfolioAndValue.date=tempdb.t.dt, uvw_qPortfolioAndValue.code=t.code from
 
(
	select id,dt,code=
	(
			select top 1 c1.code
			from cashMovement C1 
			   left join Account A1 ON C1.ObjID_CAccount = A1.ObjID
			   left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
					where q1.objID_Cportfolio=t.id and c1.date=t.dt
	)
	from
	(
			select QP.ObjID_Cportfolio as id, Max(CM.Date) as dt
					from cashMovement CM 
					   left join Account A ON CM.ObjID_CAccount = A.ObjID
					   left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
					GROUP BY QP.ObjID_Cportfolio
	) as tt  
) as t
join uvw_qPortfolioAndValue on uvw_qPortfolioAndValue.ObjID_Cportfolio=t.id and uvw_qPortfolioAndValue.date=t.dt

Open in new window

0
DhaestAuthor Commented:
What if I need an extra field
select id,dt,code=
      (  select top 1 c1.code

Can I just alter it to
select id,dt,code,anotherfield=
      (
                  select top 1 c1.code, c1.anotherfield

0
RiteshShahCommented:
it all depends on your need, but yes, you can do something like that also.
0
DhaestAuthor Commented:
When I try that, I got an error:

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
update uvw_qPortfolioAndValue 
   set uvw_qPortfolioAndValue.LatestMovementDate=t.dt, 
       uvw_qPortfolioAndValue.LatestMovementType=t.code,
       uvw_qPortfolioAndValue.LatestMovementProductObjID = t.objid_CProductMainInfo
from
(
	select id,dt,code=
	(
		select top 1 c1.code, c1.objid_Cproductmaininfo
		from securityMovement C1 
			left join Account A1 ON C1.ObjID_CAccount = A1.ObjID
			left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
		where q1.objID_Cportfolio=tt.id and c1.date=tt.dt
	)
	from
	(
			select QP.ObjID_Cportfolio as id, Max(CM.Date) as dt
					from securityMovement CM 
					   left join Account A ON CM.ObjID_CAccount = A.ObjID
					   left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
					GROUP BY QP.ObjID_Cportfolio
	) as tt  
) as t
join uvw_qPortfolioAndValue on uvw_qPortfolioAndValue.ObjID_Cportfolio=t.id

Open in new window

0
RiteshShahCommented:
TRY THIS ONE:



update uvw_qPortfolioAndValue 
   set uvw_qPortfolioAndValue.LatestMovementDate=t.dt, 
       uvw_qPortfolioAndValue.LatestMovementType=t.code,
       uvw_qPortfolioAndValue.LatestMovementProductObjID = t.objid_CProductMainInfo
from
(
        select id,dt,code=
        (
                select top 1 c1.code, c1.objid_Cproductmaininfo
                from securityMovement C1 
                        left join Account A1 ON C1.ObjID_CAccount = A1.ObjID
                        left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
                where q1.objID_Cportfolio=tt.id and c1.date=tt.dt
        ), objid_CProductMainInfo=
        (
			        select top 1 c1.objid_Cproductmaininfo
                from securityMovement C1 
                        left join Account A1 ON C1.ObjID_CAccount = A1.ObjID
                        left join uvw_qPortfolioAndValue Q1 ON A1.ObjID_Portfolio = Q1.ObjID_Cportfolio
                where q1.objID_Cportfolio=tt.id and c1.date=tt.dt
        )
        from
        (
                        select QP.ObjID_Cportfolio as id, Max(CM.Date) as dt
                                        from securityMovement CM 
                                           left join Account A ON CM.ObjID_CAccount = A.ObjID
                                           left join uvw_qPortfolioAndValue QP ON A.ObjID_Portfolio = QP.ObjID_Cportfolio
                                        GROUP BY QP.ObjID_Cportfolio
        ) as tt  
) as t
join uvw_qPortfolioAndValue on uvw_qPortfolioAndValue.ObjID_Cportfolio=t.id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DhaestAuthor Commented:
That's working, now I only have still to figure this out:
The record of cashMovement or SecurityMovement with the most recent date (= max date)
(now the query is retrieving the maxdate from cashMovement or securityMovement, but not the latest date from the union of both tables)
0
RiteshShahCommented:
not getting you
0
DhaestAuthor Commented:
Thanks for helping me figure this out !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.