Solved

Update query with MAX and GROUP BY with other fields

Posted on 2009-07-01
16
335 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

932 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

11 Experts available now in Live!

Get 1:1 Help Now