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 |
dbonneau
Yak Posting Veteran
50 Posts |
Posted - 2014-03-03 : 07:18:03
|
Hi, My current code returns account_number with multiple start_date regardless of the value is same or not.However, I would like to get only the account number when the value on start_date is different within same account_number. Could you please help me how to do that? Thank you!selectacct_numbercount(start_date) from table_Agroup by acct_number, start_datehaving(count(start_date) > 1) |
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2014-03-03 : 07:59:29
|
plz provide sample data. what is the expected result.....Veera |
|
|
dbonneau
Yak Posting Veteran
50 Posts |
Posted - 2014-03-03 : 08:08:52
|
Hi, Here is the sample data set. I just want to extract 12345 and 23456 ( when there is different start_date), but not 98765 ( start_date are same ). Thanksacct_number,start_date12345,02/01/200012345,02/01/200012345,09/01/200398765,10/11/200298765,10/11/200298765,10/11/200223456,6/26/201123456,7/20/2011 |
|
|
kennejd
Starting Member
11 Posts |
Posted - 2014-03-03 : 08:49:38
|
Are you looking for something like this?/*create table #a(acct varchar(10),sdate date)insert into #a(acct, sdate)values('12345','02/01/2000'),('12345','02/01/2000'),('12345','09/01/2003'),('98765','10/11/2002'),('98765','10/11/2002'),('98765','10/11/2002'),('23456','6/26/2011'),('23456','7/20/2011')*/with a as(select acct ,sdate from #agroup by acct ,sdatehaving count(*) > 1)select acct from a |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-03-03 : 13:16:17
|
quote: Originally posted by dbonneau Hi, Here is the sample data set. I just want to extract 12345 and 23456 ( when there is different start_date), but not 98765 ( start_date are same ). Thanksacct_number,start_date12345,02/01/200012345,02/01/200012345,09/01/200398765,10/11/200298765,10/11/200298765,10/11/200223456,6/26/201123456,7/20/2011
just this small tweakselectacct_numbercount(start_date) from table_Agroup by acct_number, start_datehaving(count(distinct start_date) > 1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2014-03-03 : 13:47:22
|
quote: Originally posted by visakh16
quote: Originally posted by dbonneau Hi, Here is the sample data set. I just want to extract 12345 and 23456 ( when there is different start_date), but not 98765 ( start_date are same ). Thanksacct_number,start_date12345,02/01/200012345,02/01/200012345,09/01/200398765,10/11/200298765,10/11/200298765,10/11/200223456,6/26/201123456,7/20/2011
just this small tweakselectacct_numbercount(start_date) from table_Agroup by acct_number, start_datehaving(count(distinct start_date) > 1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
quote: However, I would like to get only the account number when the value on start_date is different within same account_number.
A little change to what Visakh propose, I think this is what you're looking for .. note: excluded start_date from the group by clause. selectacct_numberfrom @tablegroup by acct_numberhaving count(distinct start_date) > 1I believe, including Start_Date in group by would result in 0 recordsCheersMIK |
|
|
|
|
|
|
|