Selecting Only the Latest Date in Group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting Only the Latest Date in Group

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-10-24 : 14:55:30

I need to group by customer id and visit date, but I need to select only the row within that group with the latest run date. But the catch is, I also need to return the status code. The problem I'm having is that if I include the status code in the group, it splits it into two groups. Of course, if a group has only one row, then I want to include that. So I always want to return one row per group.

Yes, if you'll notice, the latest run date row in a group would always have a StatusCode of "D", but if there is only one row in a group, then it would not have a StatusCode of "D".

Here is the table:



TblCust_Test table:

CustID--VisitDate--StatusCode--RunDate
--------------------------------------------------
AAAAAA--2012-11-05-P-----------2012-08-20
AAAAAA--2012-11-05-D-----------2012-10-17
AAAAAA--2012-12-17-P-----------2012-12-19
BBBBBB--2011-03-17-P-----------2011-03-21
BBBBBB--2011-07-27-P-----------2011-07-30
CCCCCC--2010-05-21-P-----------2010-05-01
CCCCCC--2010-05-21-D-----------2010-05-07


What I would like to see returned:

CustID--VisitDate--StatusCode--RunDate
--------------------------------------------------
AAAAAA--2012-11-05-D-----------2012-10-17
AAAAAA--2012-12-17-P-----------2012-12-19
BBBBBB--2011-03-17-P-----------2011-03-21
BBBBBB--2011-07-27-P-----------2011-07-30
CCCCCC--2010-05-21-D-----------2010-05-07


Here is a script to create the test table:

USE [Northwind]
GO
/****** Object:  Table [dbo].[tblCust_Test]    Script Date: 10/24/2012 13:48:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCust_Test](
	[CustID] [nvarchar](6) NULL,
	[VisitDate] [datetime] NULL,
	[StatusCode] [nvarchar](1) NULL,
	[RunDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'AAAAAA', CAST(0x0000A10000000000 AS DateTime), N'P', CAST(0x0000A0B300000000 AS DateTime))
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'AAAAAA', CAST(0x0000A10000000000 AS DateTime), N'D', CAST(0x0000A0ED00000000 AS DateTime))
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'AAAAAA', CAST(0x0000A12A00000000 AS DateTime), N'P', CAST(0x0000A12C00000000 AS DateTime))
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'BBBBBB', CAST(0x00009EA900000000 AS DateTime), N'P', CAST(0x00009EAD00000000 AS DateTime))
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'BBBBBB', CAST(0x00009F2D00000000 AS DateTime), N'P', CAST(0x00009F3000000000 AS DateTime))
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'CCCCCC', CAST(0x00009D7D00000000 AS DateTime), N'P', CAST(0x00009D6900000000 AS DateTime))
INSERT [dbo].[tblCust_Test] ([CustID], [VisitDate], [StatusCode], [RunDate]) VALUES (N'CCCCCC', CAST(0x00009D7D00000000 AS DateTime), N'D', CAST(0x00009D6F00000000 AS DateTime))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 15:05:07

[code]
SELECT t.*
FROM [dbo].[tblCust_Test] t
INNER JOIN (SELECT [CustID], [VisitDate],MAX([RunDate]) AS Latest
FROM [dbo].[tblCust_Test]
GROUP BY [CustID], [VisitDate])t1
ON t1.[CustID] = t.[CustID]
AND t1.[VisitDate] = t.[VisitDate]
AND t1.Latest = t.[RunDate]

or if you're on sql 2005 and above use

SELECT [CustID], [VisitDate],[StatusCode],[RunDate]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY [CustID], [VisitDate] ORDER BY [RunDate] DESC) AS Seq,*
FROM [dbo].[tblCust_Test]
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-10-24 : 15:25:47

Thank you so much, visakh16 - that works beautifully!

quote:

Originally posted by visakh16


SELECT t.*
FROM [dbo].[tblCust_Test] t
INNER JOIN (SELECT [CustID], [VisitDate],MAX([RunDate]) AS Latest
            FROM [dbo].[tblCust_Test]
            GROUP BY [CustID], [VisitDate])t1
ON t1.[CustID] = t.[CustID]
AND t1.[VisitDate] = t.[VisitDate]
AND t1.Latest = t.[RunDate]




or if you're on sql 2005 and above use

SELECT [CustID], [VisitDate],[StatusCode],[RunDate]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY [CustID], [VisitDate] ORDER BY [RunDate] DESC) AS Seq,*
FROM [dbo].[tblCust_Test]
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 16:25:45

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources