Re: Count URLS in table?
am 28.11.2004 20:18:08 von jason
Thanks EvertJan,
But, actually I already have UserInquiry table which is capturing each page
the registered user is requesting...
I did have some succcess using the DTS to bring over the records into excel
and applying a shaky pivot table to group the totals for the url but I find
this in-elegant for the following reasons:
1. I should have a query inside sql server doing this already and use the
Pivot table for other purposes
2. Additionally, there is a further caveat....I need to exclude urls
selected by users inside our company...that is, any users which contain the
following string in user email field:
'@catamarans.com'
3. A third requirement is that I include a date range in the query to bring
back all user url inquiries for e..g the month of November.
Thus my table contains the following:
UserEmail URL
jason@catco.com /catamaranco/charter/Inquiry.asp
jason@llll /catamaranco/broker/christine/index.asp
jason@catco.com /catamaranco/sales/Inquiry.asp
jason@aaa /catamaranco/saleschristine/index.asp
jason@ddd /catamaranco/sales/Inquiry.asp
jason@pet /catamaranco/sales/christine/index.asp
The query should return the following:
URL COUNT
/catamaranco/charter/Inquiry.asp = 1 (minus 2
@catco.com)
/catamaranco/broker/christine/index.asp = 2 (minus 1
@catco.com)
Thanks
Jason
"Evertjan." wrote in message
news:Xns95AD63EA9D830eejj99@194.109.133.29...
> wrote on 26 nov 2004 in microsoft.public.inetserver.asp.db:
>
> > Could someone help me with a query to Count the number of times a url
> > is selected by a registered user:
> >
> > I need to return the results in this fashion:
> > URL COUNT
> > /catamaranco/charter/Inquiry.asp 2
> > /catamaranco/broker/christine/index.asp 4
> >
>
> Insert a include in those files:
>
>
>
> that uses request.servervariables("URL")
>
> to add one to the specified table record
>
> sql = "update countTbl ... count=count+1 where url=..."
>
> [if that record does not exist, you will have to do an insert]
>
> ====================
>
> Returning the result is
> a simple showing of that db-table in a html-
>
> sql= "select * from countTbl"
>
> Show us the code you came up with sofar.
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress,
> but let us keep the discussions in the newsgroup)
>
Re: Count URLS in table?
am 29.11.2004 12:35:53 von Gary Jones
SELECT [Page] = [URL], [Hits] = COUNT(*)
FROm countTbl WHERE CHARINDEX('@catamarans.com',[UserEmail]) = 0
GROUP BY [URL]
wrote in message
news:ecb077X1EHA.2876@TK2MSFTNGP12.phx.gbl...
> Thanks EvertJan,
>
> But, actually I already have UserInquiry table which is capturing each
page
> the registered user is requesting...
>
> I did have some succcess using the DTS to bring over the records into
excel
> and applying a shaky pivot table to group the totals for the url but I
find
> this in-elegant for the following reasons:
>
> 1. I should have a query inside sql server doing this already and use the
> Pivot table for other purposes
> 2. Additionally, there is a further caveat....I need to exclude urls
> selected by users inside our company...that is, any users which contain
the
> following string in user email field:
>
> '@catamarans.com'
>
> 3. A third requirement is that I include a date range in the query to
bring
> back all user url inquiries for e..g the month of November.
>
>
> Thus my table contains the following:
>
> UserEmail URL
> jason@catco.com /catamaranco/charter/Inquiry.asp
> jason@llll
/catamaranco/broker/christine/index.asp
> jason@catco.com /catamaranco/sales/Inquiry.asp
> jason@aaa /catamaranco/saleschristine/index.asp
> jason@ddd /catamaranco/sales/Inquiry.asp
> jason@pet /catamaranco/sales/christine/index.asp
>
> The query should return the following:
>
> URL COUNT
> /catamaranco/charter/Inquiry.asp = 1 (minus 2
> @catco.com)
> /catamaranco/broker/christine/index.asp = 2 (minus 1
> @catco.com)
>
>
> Thanks
> Jason
>
> "Evertjan." wrote in message
> news:Xns95AD63EA9D830eejj99@194.109.133.29...
> > wrote on 26 nov 2004 in microsoft.public.inetserver.asp.db:
> >
> > > Could someone help me with a query to Count the number of times a url
> > > is selected by a registered user:
> > >
> > > I need to return the results in this fashion:
> > > URL COUNT
> > > /catamaranco/charter/Inquiry.asp 2
> > > /catamaranco/broker/christine/index.asp 4
> > >
> >
> > Insert a include in those files:
> >
> >
> >
> > that uses request.servervariables("URL")
> >
> > to add one to the specified table record
> >
> > sql = "update countTbl ... count=count+1 where url=..."
> >
> > [if that record does not exist, you will have to do an insert]
> >
> > ====================
> >
> > Returning the result is
> > a simple showing of that db-table in a html-
> >
> > sql= "select * from countTbl"
> >
> > Show us the code you came up with sofar.
> >
> > --
> > Evertjan.
> > The Netherlands.
> > (Please change the x'es to dots in my emailaddress,
> > but let us keep the discussions in the newsgroup)
> >
>
>
Re: Count URLS in table?
am 29.11.2004 21:09:29 von jason
Gary, you are the man! This query works perfectly....!!!
I can now use a pivot table with much more effectiveness if I can get the
damn DTS to work when I save the package.
"Gary Jones" wrote in message
news:utS25dg1EHA.3448@TK2MSFTNGP09.phx.gbl...
> SELECT [Page] = [URL], [Hits] = COUNT(*)
> FROm countTbl WHERE CHARINDEX('@catamarans.com',[UserEmail]) = 0
> GROUP BY [URL]
>
>
> wrote in message
> news:ecb077X1EHA.2876@TK2MSFTNGP12.phx.gbl...
> > Thanks EvertJan,
> >
> > But, actually I already have UserInquiry table which is capturing each
> page
> > the registered user is requesting...
> >
> > I did have some succcess using the DTS to bring over the records into
> excel
> > and applying a shaky pivot table to group the totals for the url but I
> find
> > this in-elegant for the following reasons:
> >
> > 1. I should have a query inside sql server doing this already and use
the
> > Pivot table for other purposes
> > 2. Additionally, there is a further caveat....I need to exclude urls
> > selected by users inside our company...that is, any users which contain
> the
> > following string in user email field:
> >
> > '@catamarans.com'
> >
> > 3. A third requirement is that I include a date range in the query to
> bring
> > back all user url inquiries for e..g the month of November.
> >
> >
> > Thus my table contains the following:
> >
> > UserEmail URL
> > jason@catco.com /catamaranco/charter/Inquiry.asp
> > jason@llll
> /catamaranco/broker/christine/index.asp
> > jason@catco.com /catamaranco/sales/Inquiry.asp
> > jason@aaa /catamaranco/saleschristine/index.asp
> > jason@ddd /catamaranco/sales/Inquiry.asp
> > jason@pet /catamaranco/sales/christine/index.asp
> >
> > The query should return the following:
> >
> > URL COUNT
> > /catamaranco/charter/Inquiry.asp = 1 (minus 2
> > @catco.com)
> > /catamaranco/broker/christine/index.asp = 2 (minus 1
> > @catco.com)
> >
> >
> > Thanks
> > Jason
> >
> > "Evertjan." wrote in message
> > news:Xns95AD63EA9D830eejj99@194.109.133.29...
> > > wrote on 26 nov 2004 in microsoft.public.inetserver.asp.db:
> > >
> > > > Could someone help me with a query to Count the number of times a
url
> > > > is selected by a registered user:
> > > >
> > > > I need to return the results in this fashion:
> > > > URL COUNT
> > > > /catamaranco/charter/Inquiry.asp 2
> > > > /catamaranco/broker/christine/index.asp 4
> > > >
> > >
> > > Insert a include in those files:
> > >
> > >
> > >
> > > that uses request.servervariables("URL")
> > >
> > > to add one to the specified table record
> > >
> > > sql = "update countTbl ... count=count+1 where url=..."
> > >
> > > [if that record does not exist, you will have to do an insert]
> > >
> > > ====================
> > >
> > > Returning the result is
> > > a simple showing of that db-table in a html-
> > >
> > > sql= "select * from countTbl"
> > >
> > > Show us the code you came up with sofar.
> > >
> > > --
> > > Evertjan.
> > > The Netherlands.
> > > (Please change the x'es to dots in my emailaddress,
> > > but let us keep the discussions in the newsgroup)
> > >
> >
> >
>
>