Select latest date, and other data from same row

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

Select latest date, and other data from same row

Author

Topic

MC Planner
Starting Member

3 Posts

Posted - 2013-06-25 : 16:12:16

Hi all, first SQL post.

Have just started writing SQL Select statements to retrieve info from our database but have yet to get a good understanding so please pardon the newb-ness! (and probable inability to insert a table into my post?!)

I'm trying to get the latest 'End' date for each project, along with the start date from the same row. However, if there is more than one 'End' date with the same date for the same project, then chose the row with the latest 'Start' date as the second criteria - hopefully I've clarified with the data below.

Starting data:

Project Start End
1 01/01/2013 04/03/2013
1 13/02/2013 04/03/2013
1 07/02/2013 27/02/2013
2 03/03/2013 04/06/2013
3 03/05/2013 21/11/2013
3 17/02/2013 18/08/2013
3 09/04/2013 30/07/2013
3 09/06/2013 21/11/2013

Desired Result:

Project Start End
1 13/02/2013 04/03/2013
2 03/03/2013 04/06/2013
3 09/06/2013 21/11/2013

I thought I'd got there earlier today with a
Select Project, Max(End) as End, Start
from table
group by project,end,start

but as you can imagine it didn't do as expected....help please!!!

Matt

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-25 : 16:25:31

What version of SQL server are you using?

Here is one that shoudl work on 2008+

SET DATEFORMAT DMY;

DECLARE @FOO TABLE (Project INT, Start DATE, [End] DATE)

INSERT @Foo
VALUES
(1, '01/01/2013', '04/03/2013'),
(1, '13/02/2013', '04/03/2013'),
(1, '07/02/2013', '27/02/2013'),
(2, '03/03/2013', '04/06/2013'),
(3, '03/05/2013', '21/11/2013'),
(3, '17/02/2013', '18/08/2013'),
(3, '09/04/2013', '30/07/2013'),
(3, '09/06/2013', '21/11/2013')


SELECT
	*
FROM
	(
		SELECT 
			*
			,ROW_NUMBER() OVER (PARTITION BY Project ORDER BY [End] DESC, Start DESC) AS RowNum
		FROM
			@Foo
	) AS T
WHERE
	RowNum = 1

MC Planner
Starting Member

3 Posts

Posted - 2013-06-25 : 17:00:25

Thanks for your response. We're on Server 2008. Not sure I can do what you've stated - I only have permissions to run select statements. Will I be able to carry out Set, Declare & Inserts - I'm thinking not?!

Robowski
Posting Yak Master

101 Posts

Posted - 2013-06-25 : 17:00:26

CREATE TABLE date
(
[ID] [int] NOT NULL
,[StartDate] [date] NOT NULL
,[EndDate] [date] NOT NULL
)
GO

INSERT INTO dbo.date
VALUES (1, '2013-01-01', '2013-03-04')
INSERT INTO dbo.date
VALUES (1, '2013-02-13', '2013-03-04')
INSERT INTO dbo.date
VALUES (1, '2013-02-07', '2013-02-27')
INSERT INTO dbo.date
VALUES (2, '2013-03-03', '2013-06-04')
INSERT INTO dbo.date
VALUES (3, '2013-05-03', '2013-11-21')
INSERT INTO dbo.date
VALUES (3, '2013-02-17', '2013-08-18')
INSERT INTO dbo.date
VALUES (3, '2013-04-09', '2013-07-30')
INSERT INTO dbo.date
VALUES (3, '2031-06-09', '2013-11-21')

SELECT
DISTINCT ID,
MAX(StartDate) OVER (PARTITION BY ID) as 'MinDate',
MAX(EndDate) OVER (PARTITION BY ID) as 'MaxDate'
FROM
dbo.date

the 'ID' refers to the 1, 2, 3 value in your example you I believe you refer to as Project

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-25 : 17:11:28

quote:
Originally posted by MC Planner

Thanks for your response. We're on Server 2008. Not sure I can do what you've stated - I only have permissions to run select statements. Will I be able to carry out Set, Declare & Inserts - I'm thinking not?!

The other "stuff" before the query is how you setup the data so that people can run queries against your code. I'm using the SET DATEFIRST because tje date strings are in a specific format that would cuase errors due to my regional settings (US). If you date string that are in an unambigious format, like ANSI or ISO, then you wouldn't need to use that command. But, the query itself should work for your purposes.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-25 : 17:13:17

quote:
Originally posted by Robowski

CREATE TABLE date
(
[ID] [int] NOT NULL
,[StartDate] [date] NOT NULL
,[EndDate] [date] NOT NULL
)
GO

INSERT INTO dbo.date
VALUES (1, '2013-01-01', '2013-03-04')
INSERT INTO dbo.date
VALUES (1, '2013-02-13', '2013-03-04')
INSERT INTO dbo.date
VALUES (1, '2013-02-07', '2013-02-27')
INSERT INTO dbo.date
VALUES (2, '2013-03-03', '2013-06-04')
INSERT INTO dbo.date
VALUES (3, '2013-05-03', '2013-11-21')
INSERT INTO dbo.date
VALUES (3, '2013-02-17', '2013-08-18')
INSERT INTO dbo.date
VALUES (3, '2013-04-09', '2013-07-30')
INSERT INTO dbo.date
VALUES (3, '2031-06-09', '2013-11-21')

SELECT
DISTINCT ID,
MAX(StartDate) OVER (PARTITION BY ID) as 'MinDate',
MAX(EndDate) OVER (PARTITION BY ID) as 'MaxDate'
FROM
dbo.date

the 'ID' refers to the 1, 2, 3 value in your example you I believe you refer to as Project

Although that produces the results the OP asked for, based on the sample data set. It may not return the right results if the real data has differrent date; meaning the MAX of the Start and the MAX of the END may be on different rows for a given Project.

MC Planner
Starting Member

3 Posts

Posted - 2013-06-25 : 17:13:31

cheers for the replies, I'll try this tomorrow.

Robowski
Posting Yak Master

101 Posts

Posted - 2013-06-25 : 17:16:29

quote:
Originally posted by Lamprey

quote:
Originally posted by Robowski

CREATE TABLE date
(
[ID] [int] NOT NULL
,[StartDate] [date] NOT NULL
,[EndDate] [date] NOT NULL
)
GO

INSERT INTO dbo.date
VALUES (1, '2013-01-01', '2013-03-04')
INSERT INTO dbo.date
VALUES (1, '2013-02-13', '2013-03-04')
INSERT INTO dbo.date
VALUES (1, '2013-02-07', '2013-02-27')
INSERT INTO dbo.date
VALUES (2, '2013-03-03', '2013-06-04')
INSERT INTO dbo.date
VALUES (3, '2013-05-03', '2013-11-21')
INSERT INTO dbo.date
VALUES (3, '2013-02-17', '2013-08-18')
INSERT INTO dbo.date
VALUES (3, '2013-04-09', '2013-07-30')
INSERT INTO dbo.date
VALUES (3, '2031-06-09', '2013-11-21')

SELECT
DISTINCT ID,
MAX(StartDate) OVER (PARTITION BY ID) as 'MinDate',
MAX(EndDate) OVER (PARTITION BY ID) as 'MaxDate'
FROM
dbo.date

the 'ID' refers to the 1, 2, 3 value in your example you I believe you refer to as Project
Although that produces the results the OP asked for, based on the sample data set. It may not return the right results if the real data has differrent date; meaning the MAX of the Start and the MAX of the END may be on different rows for a given Project.

I thought that was what he was going for but re-read it and see your point!

my bad :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources