January 25, 2012 at 5:02 am
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
January 25, 2012 at 5:55 am
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
January 26, 2012 at 9:13 am
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
January 26, 2012 at 12:23 pm
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 🙂
January 27, 2012 at 1:28 am
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