SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select latest date, and other data from same row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MC Planner
Starting Member

3 Posts

Posted - 06/25/2013 :  16:12:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4364 Posts

Posted - 06/25/2013 :  16:25:31  Show Profile  Reply with Quote
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
Go to Top of Page

MC Planner
Starting Member

3 Posts

Posted - 06/25/2013 :  17:00:25  Show Profile  Reply with Quote
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?!
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 06/25/2013 :  17:00:26  Show Profile  Reply with Quote
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

Edited by - Robowski on 06/25/2013 17:01:39
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4364 Posts

Posted - 06/25/2013 :  17:11:28  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4364 Posts

Posted - 06/25/2013 :  17:13:17  Show Profile  Reply with Quote
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.
Go to Top of Page

MC Planner
Starting Member

3 Posts

Posted - 06/25/2013 :  17:13:31  Show Profile  Reply with Quote
cheers for the replies, I'll try this tomorrow.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 06/25/2013 :  17:16:29  Show Profile  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000