December 21, 2011 at 7:02 am
I have two tables
1- Tracking
2- DomainsToExclude
Tracking table has following list of columns:
1- Id (int) Primary key identity
2- SessionId varchar(24) Not NULL
3- Domain varchar(100) NULL
4- pageHitTime datetime NOT NULL
5- Page varchar(100) NOT NULL
second table DomainsToExclude
1- Id int Primary key identity
2- DomainName varchar(100) NOT NULL
in tracking table there are billions of records with Domain IS NULL or empty '' or google.com, google.com.au, mydomain.com, subdomain1.mydomain.com, subdomain2.mydomain.com
we need a report with a check box to Stored Procedure as parameter from UI i.e. "Show Direct Hits" which means users comes to our site i.e. mydomain.com or any subdomain1, 2, 3 dot mydomain.com so the query would be
select all columns from Tracking where domain like '%mydomain.com%' which is easy and if this parameter is unchecked that means all domains even they are empty or NULL
select all columns from Tracking where domain is NOT NULL and Domain != ''
Now we would like to make it dymanic and put few other domains as our domains like mydomain we have, so i created a lookup table "DomainsToExclude" as mentioned above. if we but google.com and mydomain.com, the query will exclude both of these using like operator, How do i create such query? including like operator? Also we decided to put one another checkbox in report and pass this as parameter "Show Our domains". this will now require a join with lookup table
select columns from Tracking inner join DomainsToExclude and we will be giving a list which need a LIKE operator to work. How do i make like operator work here? and how to make one single query? Please help
we have thousands of subDomains i.e. subdomain1000.myDomain.com and we want to make only insert in DomainsToExclude table. the SP will take care and the reports will show data according to two parameters:
1- Show Direct Hits - check/uncheck - show all entries even NULL or empty data in Domain column
2- Show Our domains - check/uncheck - show all entries with LIKE operator from the table's list (domainsToExclude) which would be a list of our internal domains but we can but google, yahoo anything....
Can we make one query for these two checks ? any better suggestion, idea ?
Shamshad Ali.
December 21, 2011 at 7:34 am
Pretty sparse on details to come up with some code but something like this should work.
select [columns]
from tracking t
left join Exclusions e on t.domain like '%' + e.domain + '%'
where e.domain is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2011 at 7:48 am
or something like...
select [columns]
from tracking t
where not exists
(
select * from Exclusions where t.domain like '%' + e.domain + '%'
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2011 at 3:32 pm
Thanks, is there anyway to make a single query? For depending on parameter we put Cade in where clause
Where domain is not null or empty and domain like '%my domain.com%' or list of domains ...
Shamshad Ali
December 21, 2011 at 4:41 pm
Shamshad Ali (12/21/2011)
Thanks, is there anyway to make a single query? For depending on parameter we put Cade in where clauseWhere domain is not null or empty and domain like '%my domain.com%' or list of domains ...
Shamshad Ali
Both examples I showed are single queries. They by their nature will filter out null and empty string because they are like somevalue. I can help but you need to be more specific in what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply