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.
| 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 |
|
|
|
|
|
|
What script would return the expected final table?