Help with query - SQL Server Forums

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

SQL Server Development (2000)

Help with query

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-09 : 09:20:43

I have 1 table:
ID pagename datevisited
1 default 01/01/2006
1 info 01/01/2006
1 default 02/01/2006
1 info 02/01/2006
1 summary 02/01/2006
2 default 02/01/2006
2 info 02/01/2006
2 summary 02/01/2006

I need to run query for ID who didn't visited a summary page(per day)
How do I do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-09 : 09:26:58

use GROUP BY ID, datevisited and check for not exists of summary page

select 	ID, datevisited
from	table t
where	not exists (select * from table x where x.datevisited = t.datevisited and pagename = 'summary')
group by ID, datevisited

----------------------------------
'KH'

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-09 : 09:55:24

When I run your query I also getting a ID for those who visited that page. I did a little bit more modifications to your original query:
select ID, convert(varchar(10),datevisited,101)
from table t
where not exists (select * from table x where x.datevisited = t.datevisited and pagename = 'summary' and GId='12' and datevisited between '01/01/2006' and '02/01/2006')
and datevisited between '01/01/2006' and '02/01/2006'
and GId='12'
group by ID, convert(varchar(10),datevisited,101)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-09 : 10:01:21

"When I run your query I also getting a ID for those who visited that page"
Can you post some sample data of this scenario ?

----------------------------------
'KH'

sardinka
Posting Yak Master

142 Posts

Posted - 2006-03-09 : 10:27:15

ID pagename datevisited
1 default 01/01/2006
1 info 01/01/2006
1 default 02/01/2006
1 info 02/01/2006
1 summary 02/01/2006
2 default 02/01/2006
2 info 02/01/2006
2 summary 02/01/2006
3 info 03/02/2006
1 default 03/02/2006
1 info 03/02/2006
1 default 03/02/2006
1 info 03/02/2006

Result:
ID datevisited
1 01/01/2006
3 03/02/2006
1 03/02/2006

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-09 : 10:47:23

Isn't the query only return these 3 records ?

quote:
Result:
ID datevisited
1 01/01/2006
3 03/02/2006
1 03/02/2006

Unless you are only interested with date between 01/01 and 02/01. In this case, you can remove the between in the subquery

select ID, convert(varchar(10),datevisited,101)
from table t
where not exists (select * from table x where x.datevisited = t.datevisited and pagename = 'summary' and GId='12' and datevisited between '01/01/2006' and '02/01/2006')
and datevisited between '01/01/2006' and '02/01/2006'
and GId='12'
group by ID, convert(varchar(10),datevisited,101)

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources