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 2012 Forums
 Transact-SQL (2012)
 What script would extract.........
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
37 Posts

Posted - 10/11/2013 :  12:03:56  Show Profile  Reply with Quote

.....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



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

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/11/2013 :  12:09:56  Show Profile  Reply with Quote
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;
Go to Top of Page

stamford
Starting Member

United Kingdom
37 Posts

Posted - 10/11/2013 :  12:21:16  Show Profile  Reply with Quote
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 ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/11/2013 :  13:04:25  Show Profile  Reply with Quote
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
...
Go to Top of Page

stamford
Starting Member

United Kingdom
37 Posts

Posted - 10/11/2013 :  13:21:53  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/11/2013 :  14:51:13  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/11/2013 :  14:57:21  Show Profile  Reply with Quote
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.
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.06 seconds. Powered By: Snitz Forums 2000