Reputation: 870
I have this current select:
SELECT MIN(CONVERT(DateTime,SUBSTRING(NameAndDate,14,8))),
SUBSTRING(NameAndDate,1,12)
FROM MyData
WHERE pName IN (SELECT SUBSTRING(NameAndDate,1,12)
FROM MyData
GROUP BY SUBSTRING(NameAndDate,1,12)
HAVING COUNT(*) > 1)
GROUP BY SUBSTRING(NameAndDate,1,12)
Where SUBSTRING(NameAndDate,1,60)
is the person's ID name and SUBSTRING(NameAndDate,61,8)
is the date they came in.
There are many times where this data shows up multiple times in the table which is why I want to select the MIN date.
The problem is that there is another column in the table (ID) that I need to be added, but I don't want to group by it because it adds duplicates to the Person's ID.
Is there a way I can do the following:
SELECT ID,
MIN(CONVERT(DateTime,SUBSTRING(NameAndDate,14,8))),
SUBSTRING(NameAndDate,1,12)
FROM MyData
WHERE pName IN (SELECT SUBSTRING(NameAndDate,1,12)
FROM MyData
GROUP BY SUBSTRING(NameAndDate,1,12)
HAVING COUNT(*) > 1)
GROUP BY SUBSTRING(NameAndDate,1,12)
EDIT:
There could be multiple times a person comes through:
ID | NameAndDate
----+-----------------------
1 | J60047238486 08162013
2 | J60047238486 08182013
3 | J60047238486 08242013
4 | J60047238486 09032013
5 | J60047238486 10102013
6 | C40049872351 05302013
7 | C40049872351 07212013
8 | C40049872351 07252013
My current select pulls:
Name | Date
--------------+---------------------
J60047238486 | 08/16/2013 00:00:00
C40049872351 | 05/30/2013 00:00:00
But I want to add the ID column for those specific rows:
ID | Name | Date
----+--------------+---------------------
1 | J60047238486 | 08/16/2013 00:00:00
6 | C40049872351 | 05/30/2013 00:00:00
Upvotes: 0
Views: 127
Reputation: 27467
Try this
SELECT * FROM (
SELECT id,
CONVERT(DateTime,right (SUBSTRING(NameAndDate,14,8),4)
+ SUBSTRING(NameAndDate,14,4)) D,
SUBSTRING(NameAndDate,1,12) N,
COUNT(*) OVER (PARTITION BY SUBSTRING(NameAndDate,1,12)) Cnt,
ROW_NUMBER() OVER (PARTITION BY SUBSTRING(NameAndDate,1,12)
ORDER By SUBSTRING(NameAndDate,14,8)) rn
FROM
mydata
) v WHERE CNT > 1 and rn = 1;
Upvotes: 1
Reputation: 117636
with cte as (
-- first cte - parsing data
select
ID,
left(NameAndDate, 12) as Name,
convert(date,
right(NameAndDate, 4) +
substring(NameAndDate, 14, 2) +
substring(NameAndDate, 16, 2),
112) as Date
from Table1
), cte2 as (
-- second cte - create row_number
select
ID, Name, Date,
row_number() over(partition by Name order by Date) as rn
from cte
)
select
ID, Name, Date
from cte2
where rn = 1
Upvotes: 0
Reputation: 8758
You can do this, but it aint' pretty. You have to run your original query to get the minimum date for each name, and then join that back to your MyData table. It's particularly ugly because of how you store the data. Converting your MMDDYYYY string to a data was really fun.
select
MyData.[ID],
t1.theName,
t1.theDate
from
Mydata
inner join
(
select
SUBSTRING(NameAndDate,1,12) as theName,
min (
convert(datetime,
right (SUBSTRING(NameAndDate,14,8),4) + '-' +
left (SUBSTRING(NameAndDate,14,8),2) + '-' +
SUBSTRING((SUBSTRING(NameAndDate,14,8)),3,2)
))as theDate
from
mydata
where
SUBSTRING(NameAndDate,1,12) in
(SELECT SUBSTRING(NameAndDate,1,12)
FROM MyData
GROUP BY SUBSTRING(NameAndDate,1,12)
HAVING COUNT(*) > 1)
group by
SUBSTRING(NameAndDate,1,12) ) t1
ON SUBSTRING(mydata.NameAndDate,1,12) = t1.theName
AND (convert(datetime,
right (SUBSTRING(NameAndDate,14,8),4) + '-' +
left (SUBSTRING(NameAndDate,14,8),2) + '-' +
SUBSTRING((SUBSTRING(NameAndDate,14,8)),3,2))) = t1.theDate
Upvotes: 1