Max Datetime - SQL Server Forums

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

Max Datetime

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-22 : 11:20:21

Hi,

Please help, I want to check the maximum entrydt in this select statement, presently it isnt taking the maximum time..

SELECT     TOP (100) PERCENT a.ProjectPracticeID, MAX(b.BatchID) AS [Batch ID], a.ProjectID, MAX(a.EntryDt) AS MaxEntryDt
FROM         dbo.tblProjectPractices AS a INNER JOIN
                      dbo.tblBatchID AS b ON a.ProjectID = b.ProjectID
GROUP BY a.ProjectPracticeID, a.ProjectID, a.EntryDt
ORDER BY [Batch ID]

Example,

BatchID ProjectID MaxEntryDt ProjectPracticeID
1 22 21/07/10 14:01:00 1
1 22 21/07/10 14:01:00 2
1 22 22/07/10 15:01:00 3
1 22 22/07/10 15:01:00 4

I want the select query to bring forward

BatchID ProjectID MaxEntryDt ProjectPracticeID
1 22 22/07/10 15:01:00 3
1 22 22/07/10 15:01:00 4

Presently, it is bringing everything as it is any help please!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-22 : 11:30:32

I you want to find the max of EntryDt, you should not group by EntryDt.

CODO ERGO SUM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-22 : 11:31:17

Take the EntryDt out from group by because you are already using an aggregate on it.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-22 : 11:31:47

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-22 : 11:35:21

this did not work

SELECT TOP (100) PERCENT MAX(b.BatchID) AS [Batch ID], a.ProjectID, MAX(a.EntryDt) AS MaxEntryDt, a.ProjectPracticeID
FROM dbo.tblProjectPractices AS a INNER JOIN
dbo.tblBatchID AS b ON a.ProjectID = b.ProjectID
GROUP BY a.ProjectID, a.ProjectPracticeID
ORDER BY [Batch ID]

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-22 : 11:59:46

COME ON WEBFRED ANYHELP PLS

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-22 : 12:12:06

Haha

What means "did not work"?
Errors?

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-22 : 12:16:02

No errors but it doesnt exclude EntryDt which are not maximum, as for the example above it still brings

BatchID ProjectID MaxEntryDt ProjectPracticeID
1 22 21/07/10 14:01:00 1
1 22 21/07/10 14:01:00 2

as an example from #1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-22 : 12:22:28

Yes it can't because ProjectPracticeID is different so there is no grouping possible.
In this case you can only group by ProjectId and take max(ProjectPracticeID).
But then it would give :
BatchID ProjectID MaxEntryDt ProjectPracticeID
1 22 22/07/10 15:01:00 4

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-22 : 13:59:52

Here are a couple ways that might work for you (untested):

SELECT 
	a.ProjectPracticeID, 
	b.BatchID, 
	a.ProjectID, 
	a.EntryDt
FROM
	dbo.tblProjectPractices AS a 
INNER JOIN
	dbo.tblBatchID AS b 
	ON a.ProjectID = b.ProjectID
INNER JOIN
	(
		SELECT MAX(a.EntryDt) AS MaxEntryDt
		FROM dbo.tblProjectPractices
	) AS T
	ON a.EntryDt = T.MaxEntryDt
GROUP BY 
	a.ProjectPracticeID, 
	a.ProjectID, 
	a.EntryDt
ORDER BY 
	b.BatchID

-- Using Ranking Funtion

SELECT 
	ProjectPracticeID,
	BatchID,
	ProjectID,
	EntryDt
FROM
	(
		SELECT 
			a.ProjectPracticeID, 
			b.BatchID, 
			a.ProjectID, 
			a.EntryDt,
			DENSE_RANK() OVER (ORDER BY a.EntryDt DESC) AS RowNum
		FROM
			dbo.tblProjectPractices AS a 
		INNER JOIN
			dbo.tblBatchID AS b 
			ON a.ProjectID = b.ProjectID
		GROUP BY 
			a.ProjectPracticeID, 
			a.ProjectID, 
			a.EntryDt
	) AS T
WHERE 
	RowNum = 1
ORDER BY 
		BatchID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources