Latest record but with extra info from previous one

  • Hi,

    I have the following table and has some data like below (not necessarily in date order):

    Audit table

    ID | Modified on | Previous Delivery Date | New Delivery Date

    123 | 01/01/2011 | 01/12/2010 | 01/02/2011

    123 | 01/02/2011 | 01/02/2011 | 01/03/2011

    123 | 01/03/2011 | 01/03/2011 | 01/04/2011

    206 | 01/12/2011 | 01/11/2011 | 30/12/2011

    206 | 01/01/2012 | 30/12/2011 | 01/04/2012

    328 | 01/03/2011 | 15/03/2011 | 01/04/2011

    I want to be able to get the following 3 records:

    ID | Modified on | Previous Delivery Date | New Delivery Date

    123 | 01/03/2011 | 01/12/2010 (the date from first record) | 01/04/2011 (last date from last record)

    206 | 01/01/2012 | 01/11/2011 (the date from first record)| 01/04/2012 (last date from last record)

    328 | 01/03/2011 | 15/03/2011 | 01/04/2011

    The whole idea is for multiple records in the table, I need to be able to get only one record for each ID that includes the first/initial date captured then last date from latest record combined as a single record.

    Many thanks in advance.

    A

  • GROUP BY should do the trick:

    -- SAMPLE DATA

    DECLARE @Audit TABLE (

    [ID] int,

    [Modified on] datetime,

    [Previous Delivery Date] datetime,

    [New Delivery Date] datetime

    )

    SET DATEFORMAT dmy

    INSERT INTO @Audit

    SELECT 123, '01/01/2011', '01/12/2010', '01/02/2011'

    UNION ALL SELECT 123, '01/02/2011', '01/02/2011', '01/03/2011'

    UNION ALL SELECT 123, '01/03/2011', '01/03/2011', '01/04/2011'

    UNION ALL SELECT 206, '01/12/2011', '01/11/2011', '30/12/2011'

    UNION ALL SELECT 206, '01/01/2012', '30/12/2011', '01/04/2012'

    UNION ALL SELECT 328, '01/03/2011', '15/03/2011', '01/04/2011'

    -- SOLUTION

    SELECT [ID],

    MAX([Modified on]) AS [Modified on],

    MIN([Previous Delivery Date]) AS [Previous Delivery Date],

    MAX([New Delivery Date]) AS [New Delivery Date]

    FROM @Audit

    GROUP BY [ID]

    -- Gianluca Sartori

  • Hi Gianluca Sartori,

    Thanks for your kind response but it didnt work when I added the extra column (Owner) and may be additional columns in the future. So, I want to be able to pull the first new value and last new value regardless of number of changes (BTW 02/01/1900 means it was entered for the first time and no previous value).

    i.e.

    Project NameOwnerModified onOld Value New Value

    ProjAOwnerB11/07/2011 16:2223/05/201130/03/2012

    but

    Proj4OwnerC10/10/2011 15:4501/06/201109/08/2011

    ===== current structure =====

    Project NameOwnerModified onOld Value New Value

    ProjAOwnerA20/05/2011 17:4502/01/190023/05/2011

    ProjAOwnerB11/07/2011 16:2223/05/201130/03/2012

    ProjBOwnerB21/06/2011 09:3202/01/190020/06/2011

    ProjBOwnerB21/06/2011 09:3720/06/201108/05/2011

    ProjBOwnerB21/06/2011 09:4008/05/201120/06/2011

    ProjCOwnerC26/07/2010 15:0002/01/190031/03/2011

    ProjCOwnerB21/06/2011 09:5831/03/201101/04/2010

    ProjCOwnerC21/06/2011 10:0502/01/190001/04/2011

    Proj4OwnerC25/01/2011 10:5402/01/190001/06/2011

    Proj4OwnerB06/10/2011 12:3101/06/201103/10/2011

    Proj4OwnerB09/10/2011 10:2003/10/201111/03/2012

    Proj4OwnerB10/10/2011 12:0811/03/201203/10/2011

    Proj4OwnerC10/10/2011 15:4503/10/201109/08/2011

  • Gianluca Sartori (1/25/2012)


    GROUP BY should do the trick:

    -- SAMPLE DATA

    DECLARE @Audit TABLE (

    [ID] int,

    [Modified on] datetime,

    [Previous Delivery Date] datetime,

    [New Delivery Date] datetime

    )

    SET DATEFORMAT dmy

    INSERT INTO @Audit

    SELECT 123, '01/01/2011', '01/12/2010', '01/02/2011'

    UNION ALL SELECT 123, '01/02/2011', '01/02/2011', '01/03/2011'

    UNION ALL SELECT 123, '01/03/2011', '01/03/2011', '01/04/2011'

    UNION ALL SELECT 206, '01/12/2011', '01/11/2011', '30/12/2011'

    UNION ALL SELECT 206, '01/01/2012', '30/12/2011', '01/04/2012'

    UNION ALL SELECT 328, '01/03/2011', '15/03/2011', '01/04/2011'

    -- SOLUTION

    SELECT [ID],

    MAX([Modified on]) AS [Modified on],

    MIN([Previous Delivery Date]) AS [Previous Delivery Date],

    MAX([New Delivery Date]) AS [New Delivery Date]

    FROM @Audit

    GROUP BY [ID]

    Most of this is already answered from Gianluca Sartori.

    just use a CTE like this:

    WITH TestTable AS

    (

    SELECT [ID],

    [Modified on] AS [Modified on],

    [Previous Delivery Date] AS [Previous Delivery Date],

    [New Delivery Date] AS [New Delivery Date]

    FROM @Audit

    )

    SELECT [ID],

    MAX([Modified on]) AS [Modified on],

    MIN([Previous Delivery Date]) AS [Previous Delivery Date],

    MAX([New Delivery Date]) AS [New Delivery Date]

    FROM TestTable

    GROUP BY [ID]

    i think that will do the work 🙂

  • Does this help?

    -- SAMPLE DATA

    DECLARE @Audit TABLE (

    [ID] varchar(50),

    [Owner] varchar(50),

    [Modified on] datetime,

    [Previous Delivery Date] datetime,

    [New Delivery Date] datetime

    )

    SET DATEFORMAT dmy

    INSERT INTO @Audit

    SELECT 'ProjA','OwnerA','20/05/2011 17:45','02/01/1900','23/05/2011'

    UNION ALL SELECT 'ProjA','OwnerB','11/07/2011 16:22','23/05/2011','30/03/2012'

    UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:32','02/01/1900','20/06/2011'

    UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:37','20/06/2011','08/05/2011'

    UNION ALL SELECT 'ProjB','OwnerB','21/06/2011 09:40','08/05/2011','20/06/2011'

    UNION ALL SELECT 'ProjC','OwnerC','26/07/2010 15:00','02/01/1900','31/03/2011'

    UNION ALL SELECT 'ProjC','OwnerB','21/06/2011 09:58','31/03/2011','01/04/2010'

    UNION ALL SELECT 'ProjC','OwnerC','21/06/2011 10:05','02/01/1900','01/04/2011'

    UNION ALL SELECT 'Proj4','OwnerC','25/01/2011 10:54','02/01/1900','01/06/2011'

    UNION ALL SELECT 'Proj4','OwnerB','06/10/2011 12:31','01/06/2011','03/10/2011'

    UNION ALL SELECT 'Proj4','OwnerB','09/10/2011 10:20','03/10/2011','11/03/2012'

    UNION ALL SELECT 'Proj4','OwnerB','10/10/2011 12:08','11/03/2012','03/10/2011'

    UNION ALL SELECT 'Proj4','OwnerC','10/10/2011 15:45','03/10/2011','09/08/2011';

    -- SOLUTION

    WITH AuditData AS (

    SELECT *,

    RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Modified On] DESC),

    MIN([Previous Delivery Date]) OVER(PARTITION BY ID) AS [MIN Previous Delivery Date]

    FROM @Audit

    )

    SELECT [ID], [Owner], [Modified on], [MIN Previous Delivery Date], [New Delivery Date]

    FROM AuditData AS AD

    WHERE RN = 1

    -- Gianluca Sartori

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply