May 23, 2008 at 7:23 am
Hi all I really need some help.
Table is as follows:
ValueID GroupID ValueDate Amount
1 1 2008/05/19 100
2 1 2008/05/05 20
3 1 2008/04/03 30
4 2 2007/04/20 50
------------------------------------------------
I need to find the latest value per month per Group.
I can determine the latest value per group by using Max(ValueDate). Is it possible to identify the latest value per month per group?
For example a query for the table above should return ValueID 1 & 3
1 holds the latest amount for month of May.
3 holds the latest amount for April.
Any ideas on how to accomplish this would be greatly appreciated!
May 23, 2008 at 7:36 am
One alternative using CROSS APPLY might look something like:
select distinct
GroupID,
b.valueId,
b.valueDate,
b.amount
from theTable a
cross apply
( select top 1
ValueId,
ValueDate,
Amount
from theTable b
order by valueDate desc,
valueId desc
) b
There can also be done using the ROW_NUMBER() function.
EDIT:
A version using ROW_NUMBER might look something like:
select
valueId,
GroupId,
ValueDate,
Amount
from
( select
valueId,
GroupId,
row_number() over
( partition by GroupId
order by valueDate desc,
valueId desc
) as Seq,
ValueDate,
Amount
from theTable
) x
where seq = 1
May 23, 2008 at 7:52 am
Thanks I will be giving it a try ...
I suppose this will not be easy to do in SQL 2000? The db at work has not been converted to 2005 yet 🙁
May 23, 2008 at 7:58 am
I gave an SQL 2005 answer because we are in the 2005 forum; hang on and I'll review; sorry.
May 23, 2008 at 8:23 am
A SQL Server 2000 approach
DECLARE@Sample TABLE (ValueID INT, GroupID INT, ValueDate DATETIME, Amount INT)
INSERT@Sample
SELECT1, 1, '2008/05/19', 100 UNION ALL
SELECT2, 1, '2008/05/05', 20 UNION ALL
SELECT3, 1, '2008/04/03', 30 UNION ALL
SELECT4, 2, '2007/04/20', 50
SELECT*
FROM@Sample AS s
INNER JOIN(
SELECTGroupID,
MAX(ValueDate) AS ValueDate
FROM@Sample
GROUP BYGroupID,
DATEDIFF(MONTH, '19000101', ValueDate)
) AS x ON x.GroupID = s.GroupID
WHEREs.ValueDate = x.ValueDate
N 56°04'39.16"
E 12°55'05.25"
May 26, 2008 at 6:03 am
Thank you very much!
Sorry I did not mention that I intended to use the Query on a 2000 box. We are in the process of upgrading but 2005 is still a few months away here.
Thanks again, the input is greatly appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply