CODEWITHSUNDEEP
sqlsql-servert-sql
xpluffy
xpluffy

Reputation: 79

TSQL Querying ONLY rows that appeared in specified multiple dates?

So say we have rows 

ID | Date 
1 1/20 
2 1/20 
1 1/21 
7 1/21 
4 1/22 
5 1/22

So say I only want to see an ID that appeared in BOTH 1/20 and 1/21. This should only give me ID 1 since that is the only rows that appears in 1/20 and 1/21. What s the simplest way to achieve this?

I tried doing this:

Select ID, [date]
FROM t1
INNER JOIN (
SELECT ID, Count(*) countRow
FROM t1
Where [date] in(1/20, 1/21)
GROUP BY ID
having count(DISTINCT [Date]) > 1
) aa on t1.id = aa.id

I feel like there's a simple way to achieve this. Any thoughts?

Upvotes: 1

Views: 62

Answers (4)

paparazzo
paparazzo

Reputation: 45096

Not sure it is better
The way you are doing it is pretty standard
Not sure why you need to list the date when that is you where condition

If ID, date is unique then you can use count(*)

Select ID, [date]
FROM t1
INNER JOIN ( SELECT ID
             FROM t1
             Where [date] = 1/20
             intersection
             SELECT ID
             FROM t1 
             Where [date] = 1/21
           ) aa 
  on t1.id = aa.id

Select ID, [date]
FROM t1
INNER JOIN ( SELECT ID
             FROM t1 t1a 
             join t1 t1b 
               on t1a.ID = t1b.ID
              and t1a.[date] = 1/20
              and t1b.[date] = 1/21
           ) aa 
  on t1.id = aa.id

If id, value is unique 

select id, value 
from (select id, value
           , count(*) over (partition by id order by value) as cnt 
       from t 
       where value in ('a', 'b')
     ) td 
where cnt = 2 
order by id, value

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

One method uses group by and having:

select id
from t1
where date in ('1/20', '1/21')
group by id
having count(distinct date) = 2;

Of course, if date is really stored as a date, you should fix the format for the date constant to by YYYY-MM-DD.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

The way you have now is pretty simple. An alternative using exists():

select 
    t.id
  , t.date
from t
where t.date in (1/20, 1/21)
  and exists (
    select 1
    from t as i
    where i.id = t.id
      and i.date <> t.date
      and i.date in (1/20, 1/21)
  )

Upvotes: 1

Jeffrey Bane
Jeffrey Bane

Reputation: 592

SELECT ID FROM T1 WHERE Date = '1/20' AND EXISTS (
SELECT ID FROM T1 AS T2 WHERE T2.Date = '1/21' AND 
T1.ID = T2.ID)

Upvotes: 0

Related Questions