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
SELECT 1, 1, '2008/05/19', 100 UNION ALL
SELECT 2, 1, '2008/05/05', 20 UNION ALL
SELECT 3, 1, '2008/04/03', 30 UNION ALL
SELECT 4, 2, '2007/04/20', 50
SELECT *
FROM @Sample AS s
INNER JOIN (
SELECT GroupID,
MAX(ValueDate) AS ValueDate
FROM @Sample
GROUP BY GroupID,
DATEDIFF(MONTH, '19000101', ValueDate)
) AS x ON x.GroupID = s.GroupID
WHERE s.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