Get last case ID??? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Get last case ID???

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-05-03 : 13:42:26

I have a table with 3 fields Case_Id,DOC_ReleaseDt, and Case_StatusCd

I need to bring back the latest Case_Id, a Case_StatusCd
of 80040 means that it is active, so the last one would be GD2298. BUT It could be like the second set where 80041 is inactive and the all have a release date. How can I make a query to bring back the last one taking account both instances

GD2298 NULL 80040
KA6691 4/2/2012 12:00:00 AM 80041
EY9153 3/7/2004 12:00:00 AM 80041
DU7666 4/9/2002 12:00:00 AM 80041

GD2298 5/26/2011 12:00:00 AM 80041
KA6691 4/2/2012 12:00:00 AM 80041
EY9153 3/7/2004 12:00:00 AM 80041
DU7666 4/9/2002 12:00:00 AM 80041

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 14:51:58

whats the basis on which you do the grouping? I cant see any field on basis of which you're doing the grouping here

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

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-05-03 : 14:58:29

Exactly. The release date would be it, but some will have null if they have not been released they would have an active Case_StatusCd
of 80040. I need to account if there are no active ids and just take the last DOC_ReleaseDt

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:04:55

quote:
Originally posted by helixpoint

Exactly. The release date would be it, but some will have null if they have not been released they would have an active Case_StatusCd
of 80040. I need to account if there are no active ids and just take the last DOC_ReleaseDt

Dave
Helixpoint Web Development
http://www.helixpoint.com

Nope it cant be release date as they're having different values within group
My question is whats the rule/rationale behind regarding below as a group

GD2298 NULL 80040
KA6691 4/2/2012 12:00:00 AM 80041
EY9153 3/7/2004 12:00:00 AM 80041
DU7666 4/9/2002 12:00:00 AM 80041

there's no field with common values among them so as it is grouping makes no sense to me

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

dwm
Starting Member

2 Posts

Posted - 2012-05-03 : 15:34:44

Hi Dave,

Maybe these will help?

IF
	(SELECT COUNT(*)
	FROM YourTable
	WHERE Case_StatusCd = '80040')
>= 1
	BEGIN
		SELECT TOP 1 Case_Id
		FROM YourTable
		WHERE Case_StatusCd = '80040'
		ORDER BY DOC_ReleaseDt DESC
	END
	ELSE
		SELECT TOP 1 Case_Id
		FROM YourTable
		WHERE Case_StatusCd = '80041'
		ORDER BY DOC_ReleaseDt DESC;

---------

SELECT
	(SELECT TOP 1 Case_Id
	FROM YourTable
	WHERE Case_Id = '80040'
	ORDER BY DOC_ReleaseDt DESC) AS 'Latest Active',
	(SELECT TOP 1 Case_Id
	FROM YourTable
	WHERE Case_Id = '80041'
	ORDER BY DOC_ReleaseDt DESC) AS 'Latest Inactive';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources