Solved

Update query with MAX and GROUP BY with other fields

Posted on 2009-07-01
16
324 Views
Last Modified: 2012-05-07
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 ?
0
Comment
Question by:Dhaest
  • 8
  • 8
16 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752640
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752653
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
 
LVL 53

Author Comment

by:Dhaest
ID: 24752690
>> 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
 
LVL 53

Author Comment

by:Dhaest
ID: 24752698
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752711
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
 
LVL 53

Author Comment

by:Dhaest
ID: 24752714
>> 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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752721
ok. try the query and let me know whether it is giving proper results or not.
0
 
LVL 53

Author Comment

by:Dhaest
ID: 24752738
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752759
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
 
LVL 53

Author Comment

by:Dhaest
ID: 24752941
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752953
it all depends on your need, but yes, you can do something like that also.
0
 
LVL 53

Author Comment

by:Dhaest
ID: 24752970
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24753014
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
 
LVL 53

Author Comment

by:Dhaest
ID: 24753078
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24753095
not getting you
0
 
LVL 53

Author Closing Comment

by:Dhaest
ID: 31598694
Thanks for helping me figure this out !
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now