Re: Count URLS in table?
jason_at_catamaranco.com
Date: 11/29/04
- Next message: Stuart Carnie: "Re: ODBC/OLE DB Connection Pool"
- Previous message: jason_at_catamaranco.com: "DTS sql 7 ERROR -exporting query into excel 2000"
- In reply to: Gary Jones: "Re: Count URLS in table?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 15:09:29 -0500
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" <gary@vegasolutions.co.uk> 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]
>
>
> <jason@catamaranco.com> 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." <exjxw.hannivoort@interxnl.net> 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:
> > >
> > > <!-- #include virtual ="/counter.asp"-->
> > >
> > > 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-<table>
> > >
> > > 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)
> > >
> >
> >
>
>
- Next message: Stuart Carnie: "Re: ODBC/OLE DB Connection Pool"
- Previous message: jason_at_catamaranco.com: "DTS sql 7 ERROR -exporting query into excel 2000"
- In reply to: Gary Jones: "Re: Count URLS in table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
