What script would extract.........

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

Transact-SQL (2012)

What script would extract.........

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2013-10-11 : 12:03:56

.....the PLAN_ID from this table with the earliest plan agreed date please? I also need to have the MIN(PLAN_ID) value as a tie-breaker like for CARE_ID 22.
I hope to end up with a table like the one below.


PLAN_ID	CARE_ID	N5_3_PLAN_AGREE_DATE
1833	1	20/08/2011 00:00
65	4	27/11/2009 00:00
42	5	01/09/2009 00:00
55	5	29/10/2009 00:00
54	5	15/11/2009 00:00
162	5	07/07/2011 00:00
68	7	22/09/2009 00:00
69	8	15/12/2009 00:00
64	9	18/11/2009 00:00
95	9	04/02/2010 00:00
99	9	08/03/2010 00:00
2929	9	29/01/2013 00:00
3	22	21/07/2009 00:00
7	22	21/07/2009 00:00
123	68	18/06/2010 00:00
60	69	05/11/2009 00:00
40	70	23/03/2010 00:00
1706	77	25/07/2011 00:00
706	78	08/12/2010 00:00
707	78	09/12/2010 00:00
118	79	05/05/2010 00:00
410	84	18/10/2010 00:00
724	84	08/12/2010 00:00
725	84	09/12/2010 00:00
2181	84	19/10/2011 00:00

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-11 : 12:09:56

[code]SELECT PLAN_ID FROM
(
SELECT PLAN_ID,
ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY AGREE_DATE, PLAN_ID) AS RN
FROM YourTable
) s WHERE RN = 1;[/code]

stamford
Starting Member

47 Posts

Posted - 2013-10-11 : 12:21:16

quote:

Originally posted by James K

SELECT PLAN_ID FROM
(
	SELECT PLAN_ID,
		ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY AGREE_DATE, PLAN_ID) AS RN
	FROM YourTable
) s WHERE RN = 1;

I'm not so sure that works, because when I run the script it doesn't return, for instance, PLAN_ID 42 which has the earliest PLAN_AGREED_DATE of 01/09/2009 for CARE_ID 5 ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-11 : 13:04:25

What is the data type of the AGREE_DATE column? You can find using this:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE tablename = 'YourTableNameHere' AND COLUMN_NAME = 'N5_3_PLAN_AGREE_DATE'

If it is anything other that one of the DATE/DATETIME types, then you will need to cast it as DATE as in

...
ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY CAST(AGREE_DATE AS DATE), PLAN_ID) AS RN
...

stamford
Starting Member

47 Posts

Posted - 2013-10-11 : 13:21:53

quote:
Originally posted by James K

What is the data type of the AGREE_DATE column? You can find using this:
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE tablename = 'YourTableNameHere' AND COLUMN_NAME = 'N5_3_PLAN_AGREE_DATE'
If it is anything other that one of the DATE/DATETIME types, then you will need to cast it as DATE as in
...
ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY CAST(AGREE_DATE AS DATE), PLAN_ID) AS RN
...

The DATA_TYPE is smalldatetime.

I've had some success with this script but it doesn't allow for duplicate values in the combination of CARE_ID and N5_3_PLAN_AGREE_DATE - it excludes them


SELECT COUNT(*) FROM tblMAIN_CARE_PLAN AS f
WHERE f.CARE_ID = tblMAIN_CARE_PLAN.CARE_ID 
AND f.N5_3_PLAN_AGREE_DATE <= tblMAIN_CARE_PLAN.N5_3_PLAN_AGREE_DATE) = 1

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-11 : 14:51:13

The code I posted earlier should work if the data type is smalldatetime. If it does not, that means there is something else in the data/tables/problem that is not in your description/sample data.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-11 : 14:57:21

quote:
Originally posted by stamford

quote:
Originally posted by James K

SELECT PLAN_ID FROM
(
SELECT PLAN_ID,
ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY AGREE_DATE, PLAN_ID) AS RN
FROM YourTable
) s WHERE RN = 1;

I'm not so sure that works, because when I run the script it doesn't return, for instance, PLAN_ID 42 which has the earliest PLAN_AGREED_DATE of 01/09/2009 for CARE_ID 5 ?

I created test data with the sample in your original posting, and used the code I posted earlier. See the results in green below. It does pick up 42 for care_id = 5

create table #tmp (PLAN_ID INT, CARE_ID INT, N5_3_PLAN_AGREE_DATE SMALLDATETIME);

SET DATEFORMAT DMY;

insert into #tmp values ('1833','1','20/08/2011 00:00');
insert into #tmp values ('65','4','27/11/2009 00:00');
insert into #tmp values ('42','5','01/09/2009 00:00');
insert into #tmp values ('55','5','29/10/2009 00:00');
insert into #tmp values ('54','5','15/11/2009 00:00');
insert into #tmp values ('162','5','07/07/2011 00:00');
insert into #tmp values ('68','7','22/09/2009 00:00');
insert into #tmp values ('69','8','15/12/2009 00:00');
insert into #tmp values ('64','9','18/11/2009 00:00');
insert into #tmp values ('95','9','04/02/2010 00:00');
insert into #tmp values ('99','9','08/03/2010 00:00');
insert into #tmp values ('2929','9','29/01/2013 00:00');
insert into #tmp values ('3','22','21/07/2009 00:00');
insert into #tmp values ('7','22','21/07/2009 00:00');
insert into #tmp values ('123','68','18/06/2010 00:00');
insert into #tmp values ('60','69','05/11/2009 00:00');
insert into #tmp values ('40','70','23/03/2010 00:00');
insert into #tmp values ('1706','77','25/07/2011 00:00');
insert into #tmp values ('706','78','08/12/2010 00:00');
insert into #tmp values ('707','78','09/12/2010 00:00');
insert into #tmp values ('118','79','05/05/2010 00:00');
insert into #tmp values ('410','84','18/10/2010 00:00');
insert into #tmp values ('724','84','08/12/2010 00:00');
insert into #tmp values ('725','84','09/12/2010 00:00');
insert into #tmp values ('2181','84','19/10/2011 00:00');

SELECT PLAN_ID FROM
(
	SELECT PLAN_ID,
		ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY N5_3_PLAN_AGREE_DATE, PLAN_ID) AS RN
	FROM #tmp
) s WHERE RN = 1;


DROP TABLE #tmp;

 RESULTS 
PLAN_ID
1833
65
42
68
69
64
3
123
60
40
1706
706
118
410

BTW, when you post your question if you can include DDL statements for creating sample data like I have here, that makes it easier for someone to respond. So you will get more accurate and faster responses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources