SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 MAX record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mango87
Starting Member

1 Posts

Posted - 05/28/2014 :  06:11:34  Show Profile  Reply with Quote
Hi,

I have the following select statement and I want to export the details (ValueDate and FundValue) for the maximum id_ValuationRequest per [PLAN].PlanNumber. Can anyone help? Sorry, I am quite new to SQL and I am trying to teach myself...


SELECT dbo.[PLAN].PlanNumber, dbo.VALUATIONREQUEST.ValueDate, dbo.VALUATIONREQUEST.FundValue,
dbo.VALUATIONREQUEST.id_ValuationRequest
FROM dbo.VALUATIONREQUEST INNER JOIN
dbo.VALUETYPE ON dbo.VALUATIONREQUEST.id_ValueType = dbo.VALUETYPE.id_ValueType INNER JOIN
dbo.[PLAN] ON dbo.VALUATIONREQUEST.UniquePlanID = dbo.[PLAN].UniquePlanID INNER JOIN
dbo.VALUESTATUS ON dbo.VALUATIONREQUEST.id_ValueStatus = dbo.VALUESTATUS.id_ValueStatus
WHERE (dbo.VALUETYPE.ValueTypeName LIKE 'Contractual%') AND (dbo.VALUESTATUS.ValueStatusName = 'Complete and Checked' OR
dbo.VALUESTATUS.ValueStatusName = 'Checked') AND (dbo.VALUATIONREQUEST.FundValue <> 0)
ORDER BY dbo.[PLAN].PlanNumber

EXRORT DETAILS....

PlanNumber ValueDate FundValue id_ValuationRequest
A 31/03/2010 100 113
A 30/04/2010 200 114
A 30/04/2010 100 185
B 01/10/2010 1000 125
B 01/01/2014 2000 178
B 06/05/2014 300 268
C 01/01/2013 6000 758
C 05/04/2011 7000 687
C 20/02/2009 800 249

I would expect to see three rows of results for id's 185(plan A), 268 (plan B) & 758 (plan C).

Also, how would I change the date format to YYYY-MM-DD?

Thanks so much in advance. So happy I found this website!

gbritton
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 05/28/2014 :  07:51:03  Show Profile  Reply with Quote
basically something like:


select max(id_ValuationRequest)
from ...
group by [PLAN].PlanNumber
order by [PLAN].PlanNumber

Edited by - gbritton on 05/28/2014 07:51:17
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 05/28/2014 :  10:22:33  Show Profile  Visit webfred's Homepage  Reply with Quote
please try this:

SELECT dbo.[PLAN].PlanNumber, dbo.VALUATIONREQUEST.ValueDate, dbo.VALUATIONREQUEST.FundValue, 
 dbo.VALUATIONREQUEST.id_ValuationRequest
FROM dbo.VALUATIONREQUEST INNER JOIN
 dbo.VALUETYPE ON dbo.VALUATIONREQUEST.id_ValueType = dbo.VALUETYPE.id_ValueType INNER JOIN
 dbo.[PLAN] ON dbo.VALUATIONREQUEST.UniquePlanID = dbo.[PLAN].UniquePlanID INNER JOIN
 dbo.VALUESTATUS ON dbo.VALUATIONREQUEST.id_ValueStatus = dbo.VALUESTATUS.id_ValueStatus
 
inner join
 (
SELECT dbo.[PLAN].PlanNumber, max(dbo.VALUATIONREQUEST.id_ValuationRequest) as id_ValuationRequest
FROM dbo.VALUATIONREQUEST INNER JOIN
 dbo.VALUETYPE ON dbo.VALUATIONREQUEST.id_ValueType = dbo.VALUETYPE.id_ValueType INNER JOIN
 dbo.[PLAN] ON dbo.VALUATIONREQUEST.UniquePlanID = dbo.[PLAN].UniquePlanID INNER JOIN
 dbo.VALUESTATUS ON dbo.VALUATIONREQUEST.id_ValueStatus = dbo.VALUESTATUS.id_ValueStatus
WHERE (dbo.VALUETYPE.ValueTypeName LIKE 'Contractual%') AND (dbo.VALUESTATUS.ValueStatusName = 'Complete and Checked' OR
 dbo.VALUESTATUS.ValueStatusName = 'Checked') AND (dbo.VALUATIONREQUEST.FundValue <> 0)
 group by dbo.[PLAN].PlanNumber
) as dt
on dt.PlanNumber = dbo.[PLAN].PlanNumber and dt.id_ValuationRequest = dbo.VALUATIONREQUEST.id_ValuationRequest
 
WHERE (dbo.VALUETYPE.ValueTypeName LIKE 'Contractual%') AND (dbo.VALUESTATUS.ValueStatusName = 'Complete and Checked' OR
 dbo.VALUESTATUS.ValueStatusName = 'Checked') AND (dbo.VALUATIONREQUEST.FundValue <> 0)

ORDER BY dbo.[PLAN].PlanNumber



Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000