dynamic query

  • I am trying to do a dynamic query, right now it is static because i have to manually input the clientuserid to get the results. how can i get the data to be pulled automatically from the top 3 clientuserid?

    --算上網時間Count internet usage in Minutes

    --1. 抓出要算的retrieve useful record olny

    Select * into #ProxyLog_record from WebProxyLog Where

    GmtLogTime between (select dbo.F_START_OF_WEEK(getdate(),default)) and (Select Dateadd(Day, 6, (select dbo.F_START_OF_WEEK(getdate(),default)))) and

    convert(char(12), Logtime, 108) not between '12:00:00' and '13:00:00' and

    ClientUserName not in (

    'iec\IHC070705',

    'iec\IHC070202',

    'iec\IHC061108',

    'iec\IHC060809',

    'iec\IHC060808',

    'iec\ihc041019',

    'iec\hit080102',

    'iec\IHC070305',

    'iec\IHC061202',

    'iec\ihc030302',

    'iec\IHC050907',

    'iec\IHC080102',

    'iec\IHC090801',

    'iec\IHC100601',

    'iec\IHC100601',

    'iec\IHC010501',

    'IEC\IEC880329',

    'IEC\IEC780762',

    'IEC\ihc110502',

    'IEC\hit110502',

    'iec\HIT010501',

    'anonymous') and

    DestHost not like '%.microsoft%' and

    DestHost not like '%.citibank%' and

    DestHost not like '%.windowsupdate%' and

    DestHost not like '%.inventec%' and

    DestHost not like '%.trendmicro.com%' and

    DestHost not like '%.google%' and

    DestHost not like '%.symantec%' and

    DestHost not like '10.%' and

    DestHost not like '16.%' and

    DestHost not like '%.gov' and

    DestHost not like '%.hp.com%' and

    DestHost not like '%.dell.com%' and

    DestHost not like '%.ups%' and

    DestHost not like '%.usps.com%' and

    DestHost not like '%.fedex.com%' and

    DestHost not like '%.ibm.com%' and

    DestHost not like '%toshiba%' and

    DestHost not like '%.intel.com%' and

    DestHost not like '%.seagate.com%' and

    DestHost not like '%.hp-ww.com%' and

    DestHost not like '%.ta-america.com%' and

    DestHost not like '%.conexis.com%' and

    DestHost not like '%.www.employeebenefitswebsite.com%' and

    DestHost not like '%.secure.ucci.com%' and

    DestHost not like '%.ajg.adobeconnect.com%' and

    DestHost not like '%.jpfic.com%' and

    DestHost not like '%.avesis.com%' and

    DestHost not like '%.aetna.com%' and

    DestHost not like '%.cbs-companies.com%' and

    DestHost not like '%.sre.gob.mx%' and

    DestHost not like '%.800notes.com%' and

    DestHost not like '%.chinahouston.org%' and

    DestHost not like '%.n-o-v-a.com%' and

    DestHost not like '%.openonline.com%' and

    DestHost not like '%.weather.com%' and

    DestHost not like '%.weatherbug.com%' and

    DestHost not like '%.myfoxhouston.com%' and

    DestHost not like '%.andadvantus.com%' and

    DestHost not like '%.1800flowers.com%' and

    DestHost not like '%.nhc.noaa.gov%' and

    DestHost not like '%.pbconferences.com%' and

    DestHost not like '%.amazon.com%' and

    DestHost not like '%.wxbug.com%' and

    DestHost not like '%.kwe.com%' and

    DestHost not like '%.fedex.com%' and

    DestHost not like '%.forwarding.ups-scs.com%' and

    DestHost not like '%.usps.com%' and

    DestHost not like '%.yml.com.tw%' and

    DestHost not like '%.dhl-usa.com%' and

    DestHost not like '%.expeditors.com%' and

    DestHost not like '%.morrisonexpress.com%' and

    DestHost not like '%.nykline.com%' and

    DestHost not like '%.pandora.com%' and

    DestHost not like '%.ob10.com%' and

    DestHost not like '%.skype.com%' and

    DestHost not like '%.citibank.com%' and

    DestHost not like '%.auo.com%' and

    DestHost not like '%.clocklink.com%' and

    DestHost not like '%.fujitsu.com%' and

    DestHost not like '%.hitachigst.com%' and

    DestHost not like '%.ta-retirement.com%' and

    DestHost not like '%.businessaccess.citibank.citigroup.com%'and

    DestHost not like '%dell.webportal.eaglegl.com%' and

    DestHost not like '%.citibank.com/us/citibusinessonline/%' and

    DestHost not like '207.46.%' and

    DestHost not like '207.114.197.%' and

    DestHost not like '64.4.34.%' and

    DestHost not like '64.4.36.%' and

    DestHost not like '65.54.228.%' and

    DestHost not like '65.54.171.%' and

    DestHost not like '65.54.189.%' and

    DestHost not like '64.4.16.%' and

    DestHost not like '68.142.201.%' and

    DestHost not like '68.180.219.%' and

    DestHost not like '72.2.117.%' and

    DestHost not like '76.13.6.%' and

    DestHost not like '98.136.113.%' and

    DestHost not like '72.246.30.199%' and

    DestHost not like '68.142.122.198%' and

    DestHost not like '98.136.112.%' and

    DestHost not like '216.155.194.%' and

    DestHost not like '98.136.112.%' and

    DestHost not like '209.239.112.%' and

    DestHost not like '71.244.82.%' and

    DestHost not like '88.49.113.%' and

    DestHost not like '210.7.74.%' and

    DestHost not like 'ihs-12345%' and

    DestHost not like 'ms_proxy_auth_query' and

    DestHost not in ('msnbcmedia.msn.com', 'www.projectreader.com') and

    uri not like '%.gif'

    --2. 抓需要的Columns and Convert Time into (MM/DD/YYYY hh:mm)

    Select ClientIP, ClientUserName, Convert(nvarchar(30), logTime, 100) as logTime, servername into #ProxyLog_info from #ProxyLog_record

    --3. 同一個人同日期時間(Hour:Minute)只算一次 the same person

    Select distinct logTime, ClientUserName, ClientIP into #ProxyLog_count from #ProxyLog_info order by ClientUserName

    --4. 算上網總時間Count total internet usage minutes

    Select ClientUserName, count(ClientUserName) counts from #ProxyLog_count Group by ClientUserName order by Counts desc

    --5. Top 3 user internet usage

    Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record

    where ClientUserName ='IEC\IHC010202' Group by ClientUserName, DestHost order by counts desc

    --6. 算瀏覽網頁次數 Count website access times

    Select DestHost, count(DestHost) counts from #ProxyLog_record Group by DestHost order by counts desc

    Select (530/60)

    --drop Table #ProxyLog_record

    --drop Table #ProxyLog_info

    --drop Table #ProxyLog_count

    as you can see in section 5. i have to search for a specific one. but how can i get it to pull the top 3 data from the proxylog automatically?

    what im trying to do is get the top 3 users from the log, who uses the most internet so i need it to display the websites they use, along with the amount of times they visited the website.

  • ... select top 3?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply