Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Last value entered based on last modified date Expand / Collapse
Author
Message
Posted Friday, September 13, 2013 4:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 13, 2013 8:33 AM
Points: 11, Visits: 66
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]
ProjA OwnerA 2011-07-11 16:22:00.000 2540.00 2011-07-11 16:22:00.000
ProjB OwnerB 2011-06-21 09:40:00.000 45000.00 2011-06-21 09:40:00.000

Instead I get the following:

ProjA OwnerA 2011-05-20 17:45:00.000 1256.00 2011-07-11 16:22:00.000
ProjA OwnerA 2011-07-11 16:22:00.000 2540.00 2011-07-11 16:22:00.000
ProjB OwnerB 2011-06-21 09:32:00.000 25610.00 2011-06-21 09:40:00.000
ProjB OwnerB 2011-06-21 09:37:00.000 30000.00 2011-06-21 09:40:00.000
ProjB OwnerB 2011-06-21 09:40:00.000 45000.00 2011-06-21 09:40:00.000


What am I doing wrong?

Your help is appreciated.

Many Thanks

A
Post #1494534
Posted Friday, September 13, 2013 5:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 13,046, Visits: 10,815
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;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1494538
Posted Friday, September 13, 2013 5:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 5,111, Visits: 11,915
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1494539
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse