September 13, 2013 at 4:49 am
I need to get the last modified value for each project and each owner
-- SAMPLE DATA
DECLARE @Audit TABLE (
[ID] varchar(50),
[Owner] varchar(50),
[Modified on] datetime,
[Value] money
)
SET DATEFORMAT dmy
INSERT INTO @Audit
SELECT 'ProjA','OwnerA','20/05/2011 17:45','1256'
UNION ALL SELECT 'ProjA','OwnerA','11/07/2011 16:22','2540'
UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:32','25610'
UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:37','30000'
UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:40','45000';
-- SOLUTION
WITH AuditData AS (
SELECT *,
RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),
max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]
FROM @Audit
)
SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]
FROM AuditData AS AD
I need the following result:
[ID], [Owner], [Modified on], [Value], [Max Modified On]
ProjAOwnerA2011-07-11 16:22:00.0002540.002011-07-11 16:22:00.000
ProjBOwnerB2011-06-21 09:40:00.00045000.002011-06-21 09:40:00.000
Instead I get the following:
ProjAOwnerA2011-05-20 17:45:00.0001256.002011-07-11 16:22:00.000
ProjAOwnerA2011-07-11 16:22:00.0002540.002011-07-11 16:22:00.000
ProjBOwnerB2011-06-21 09:32:00.00025610.002011-06-21 09:40:00.000
ProjBOwnerB2011-06-21 09:37:00.00030000.002011-06-21 09:40:00.000
ProjBOwnerB2011-06-21 09:40:00.00045000.002011-06-21 09:40:00.000
What am I doing wrong?
Your help is appreciated.
Many Thanks
A
September 13, 2013 at 5:09 am
WITH AuditData AS (
SELECT *,
RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),
max([Modified on]) OVER(PARTITION BY ID) AS [Max Modified On]
FROM @Audit
)
SELECT [ID], [Owner], [Modified on], [Value], [Max Modified On]
FROM AuditData AS AD
WHERE RN = 1;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2013 at 5:09 am
Or this maybe:
with MaxDates as (select id, MaxMod = max([Modified on]) from @Audit a group by id)
select a.id,a.Owner, a.[Modified on],a.Value
from @Audit a
join MaxDates m on a.id = m.id and a.[Modified on] = m.MaxMod
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply