Find similarities in 1 column and display

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

Transact-SQL (2005)

Find similarities in 1 column and display

Author

Topic

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-17 : 17:56:35

Over all I’m trying to write an SQL statement and can’t find the correct operator to use. Here’s what I want to accomplish:

In my table (Table76) I want to filter on TableCode and display the Dates they have in common.

here is what I am trying but obliviously its not working:

SELECT Date
FROM Table76
Where TableCode = 196 AND TableCode = 20

IE:

Dates TableCode
1/1/09 20
1/1/09 196
1/2/09 20

So my results would be 1/1/09 because that’s the date the two TableCodes have in common.

Thanks in advance for any help.

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-17 : 22:11:04

I think this is what you are after:


select top 1 d.date
from (   select date, count(*) cnt
         from   Table76
         where  TableCode in (196, 20)
         group 
         by     date
     ) d
order
by   d.cnt desc

Please post back if this is close. How are you passing in the TableCode values?

Nathan Skerl

q123126
Starting Member

1 Post

Posted - 2009-01-17 : 22:16:26

create Function T(
date datetime
)returns varchar(1000)
declare @s varchar(1000)
set @s=''
Select @s=@s+','+tablecode from table76 where dates=@date
return @s

and then
declare @s varchar(1000)
set @s='196,20'
Select date from table 76 where charindex(@s,dbo.T(date))>0

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-17 : 23:05:27

Nathan

Thanks for the help but the results are showing all the records not the dates they have in common.

also im getting the TableCodes from an array but i was hard coding it into the SQL table to make sure im getting the correct results before i put the query in my cfc.

so heres another snid-bit of what i have and what i need.

Dates TableCode
1/9/09 20
1/9/09 196
1/10/09 20
1/11/09 196

and my results should only be 1/9/09 because thats the only date that the two TableCodes have in common.

thanks again for your help

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-17 : 23:28:04

Heres a test case. Does this look correct? I added another common date in the test data


declare @table76 table (date datetime, TableCode int)
insert into @table76
	select '1/9/09', 20 union
	select '1/9/09', 196 union
	select '1/10/09', 20 union
	select '1/11/09', 196 union
	select '2/2/02', 196 union
	select '2/2/02', 20

select  [date] 
from    @Table76
where   TableCode in (196, 20)
group 
by      [date]
having  count(*)=2 and
        sum(case when TableCode in (196, 20) then 1 else 0 end)= 2

Nathan Skerl

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-17 : 23:44:37

ok heres EXACTLY whats going on:
i tried your first post and i only got back 1 record because you used (TOP 1 d.date) so i tried mixing it up and i got back every row. so i guess your first post was good and worked but i need all of the common dates not just the 'TOP 1'.

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-18 : 00:14:16

Try the 2nd query. It will return all common dates.

Nathan Skerl

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-18 : 00:16:22

the format you displayed it in is confusing me. your first post is the way im more comfortable with.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-18 : 00:24:37

or simply this:


select  [date] 
from    Table76
group by [date]
having Count(Case when TableCode in (196, 20........n) then TableCode else 0 end)= 2 or .....(n)
Order by [date]

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-18 : 00:42:54

here is my statement and it returns nothing:

SELECT Date
FROM Table76
GROUP BY Date
HAVING (COUNT(CASE WHEN TableCode IN (196, 20) THEN TableCode ELSE 0 END) = 2)
ORDER BY Date

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-18 : 00:47:06

I am getting expected output.


Declare @t table (date datetime, TableCode int)
insert into @t
	select '1/9/09', 20 union all
	select '1/9/09', 196 union all
	select '1/10/09', 20 union all
	select '1/11/09', 196 

SELECT Date
FROM @t
GROUP BY Date
HAVING (COUNT(CASE WHEN TableCode IN (196, 20) THEN TableCode ELSE 0 END) = 2)
ORDER BY Date

Output:
2009-01-09 00:00:00.000

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-18 : 01:01:44

i already have the dates in the table and i just get an empty 'Date' column back.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-18 : 09:07:21

[code]
select [date]
from @Table76
where TableCode in (196, 20)
group
by [date]
having count(distinct case when TableCode in (196, 20) then TableCode else null end)= 2
[/code]

also make sure your date field doesnt have time part value

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-18 : 12:09:50

The two queries are similar, both leveraging the having clause to make sure that the grouped count is equal to the number of distinct TableCode values in the array.

Will the number of TableCode values always be 2?

And, as Visakh mentioned, your dates probably include time so they will not group. If thats the case then you will need to modify the query to group by the date (1/1/2001) and not the datetime (1/1/2001 12:00:00:000).

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-01-18 : 12:11:41

Post real sample data from your table using the test case we used above so we can see where the query is going wrong.

Nathan Skerl

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-18 : 13:10:29

you guys are right, my dataType on the date field is datetime. But i cant change that because other people use the table. can i get around that in my query?

IronTiger
Starting Member

4 Posts

Posted - 2009-01-18 : 13:22:25

How about this.

SELECT A.DATE
FROM TABLE76 A
JOIN TABLE76 B
ON A.DATE = B.DATE
WHERE A.TABLECODE = 196
AND B.TABLECODE = 20

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-18 : 13:27:37

Post real sample data from your original table as Nathan said.

KuzzieE
Starting Member

8 Posts

Posted - 2009-01-18 : 13:35:20

YEAHHHHHH!!!

Iron Tiger, your post worked!!!!!