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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 What script would return the expected final table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
42 Posts

Posted - 05/14/2014 :  09:52:13  Show Profile  Reply with Quote
In the following table what SQL2000 compatible script would return one row per CARE_ID which has an EVENT_TYPE of CP which is the most recent before the first instance of TR. Else return the most recent instance of CP. Most recent in terms of EVENT_DATE and the tie breaker is MAX(EVENT_ID). Below is the expected final table.


CARE_ID     EVENT_ID    EVENT_DATE          EVENT_TYPE
3           117         09/04/2010 00:00    CP
3           104         11/04/2010 00:00    TR
3           190         16/04/2010 00:00    TR
3           16          12/07/2010 00:00    TR
3           17          13/07/2010 00:00    TR
3           18          13/07/2010 00:00    TR
78          11          27/07/2009 00:00    TR
78          9           28/07/2009 00:00    TR
78          706         08/12/2010 00:00    CP
78          707         09/12/2010 00:00    CP
107         93          23/02/2010 00:00    CP
107         1474        21/09/2012 00:00    TR
206         84          28/07/2009 00:00    CP
206         85          21/08/2009 00:00    CP


CARE_ID     EVENT_ID    EVENT_DATE          EVENT_TYPE
3           117         09/04/2010 00:00    CP
78          707         09/12/2010 00:00    CP
107         93          23/02/2010 00:00    CP
206         85          21/08/2009 00:00    CP

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 05/15/2014 :  02:30:55  Show Profile  Reply with Quote

SELECT DISTINCT
		A.CARE_ID
		,(select EVENT_ID from udf_EventDate_FinalCP_GET(A.CARE_ID) ) as EVENT_ID
		,(select EVENT_DATE from udf_EventDate_FinalCP_GET(A.CARE_ID) ) as EVENT_DATE
		,(select EVENT_TYPE from udf_EventDate_FinalCP_GET(A.CARE_ID) ) as EVENT_TYPE

	FROM
		tblEventDAte A

output:

CARE_ID	EVENT_ID	EVENT_DATE	EVENT_TYPE
3	117	2010-04-09 00:00:00.000	CP
78	707	2010-12-09 00:00:00.000	CP
107	93	2010-02-23 00:00:00.000	CP
206	85	2009-08-21 00:00:00.000	CP



CREATE FUNCTION [dbo].[udf_EventDate_FinalCP_GET]
(	
	@CARE_ID INT
)
RETURNS TABLE 
AS
RETURN 
(
			SELECT TOP 1
				 CARE_ID
				,EVENT_ID
				,EVENT_DATE
				,EVENT_TYPE
			FROM
				tblEventDate
			WHERE 
				CARE_ID=@CARE_ID
				AND EVENT_TYPE='CP'
				AND (EVENT_DATE<(SELECT EVENT_DATE FROM udf_EventDate_FirstTR_Get(@CARE_ID))
					  OR EVENT_DATE>'1900/01/01')
			ORDER BY 
				EVENT_DATE	DESC
					
)


and

CREATE FUNCTION [dbo].[udf_EventDate_FirstTR_GET]
(	
	@CARE_ID INT
)
RETURNS TABLE 
AS
RETURN 
(
	
	SELECT TOP 1
				CARE_ID
				,EVENT_DATE 
				,EVENT_ID
				,EVENT_TYPE
	FROM
		tblEventDate
	WHERE
		CARE_ID=@CARE_ID
		AND EVENT_TYPE='TR'
	ORDER BY EVENT_DATE ASC
)



sabinWeb MCP
Go to Top of Page

stamford
Starting Member

United Kingdom
42 Posts

Posted - 05/15/2014 :  07:32:18  Show Profile  Reply with Quote
quote:
Originally posted by stepson


SELECT DISTINCT
		A.CARE_ID
		,(select EVENT_ID from udf_EventDate_FinalCP_GET(A.CARE_ID) ) as EVENT_ID
		,(select EVENT_DATE from udf_EventDate_FinalCP_GET(A.CARE_ID) ) as EVENT_DATE
		,(select EVENT_TYPE from udf_EventDate_FinalCP_GET(A.CARE_ID) ) as EVENT_TYPE

	FROM
		tblEventDAte A

output:

CARE_ID	EVENT_ID	EVENT_DATE	EVENT_TYPE
3	117	2010-04-09 00:00:00.000	CP
78	707	2010-12-09 00:00:00.000	CP
107	93	2010-02-23 00:00:00.000	CP
206	85	2009-08-21 00:00:00.000	CP



CREATE FUNCTION [dbo].[udf_EventDate_FinalCP_GET]
(	
	@CARE_ID INT
)
RETURNS TABLE 
AS
RETURN 
(
			SELECT TOP 1
				 CARE_ID
				,EVENT_ID
				,EVENT_DATE
				,EVENT_TYPE
			FROM
				tblEventDate
			WHERE 
				CARE_ID=@CARE_ID
				AND EVENT_TYPE='CP'
				AND (EVENT_DATE<(SELECT EVENT_DATE FROM udf_EventDate_FirstTR_Get(@CARE_ID))
					  OR EVENT_DATE>'1900/01/01')
			ORDER BY 
				EVENT_DATE	DESC
					
)


and

CREATE FUNCTION [dbo].[udf_EventDate_FirstTR_GET]
(	
	@CARE_ID INT
)
RETURNS TABLE 
AS
RETURN 
(
	
	SELECT TOP 1
				CARE_ID
				,EVENT_DATE 
				,EVENT_ID
				,EVENT_TYPE
	FROM
		tblEventDate
	WHERE
		CARE_ID=@CARE_ID
		AND EVENT_TYPE='TR'
	ORDER BY EVENT_DATE ASC
)



sabinWeb MCP





Thank you!
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 05/15/2014 :  07:50:23  Show Profile  Reply with Quote
Your welcome!


sabinWeb MCP
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.12 seconds. Powered By: Snitz Forums 2000