What script would return the expected final table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

What script would return the expected final table?

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-05-14 : 09:52:13

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
Aged Yak Warrior

545 Posts

Posted - 2014-05-15 : 02:30:55

[code]
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
[/code]
output:
[code]
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
[/code]

[code]
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

)
[/code]

and
[code]
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
)
[/code]

sabinWeb MCP

stamford
Starting Member

47 Posts

Posted - 2014-05-15 : 07:32:18

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!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-15 : 07:50:23

Your welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources