Need Clever Way To Count Outside Hits In Sql Table
Dec 19, 2007
hi . would greatly appreciate a clever way to make an internal "counter" on an sql table that contains 6000 articles.(one in each row). these are being accessed from a web page and i would like to know which are the most read ones. so i thought of adding a column that would in some way count each time that the row is being accessed. is there a way for this to be done?? please be detailed since i am quite new to all this.
thanks beforehands.
View 3 Replies
ADVERTISEMENT
Jan 22, 2008
Using MSSQL (pre 2005):
I have a Link table:
int id (primary key/identity)
varchar(50) linkName
varchar(255) linkHref
//some other stuff
and my Hits table:
int id (primary key/identity)
int linkId (foreign key to Link)
datetime dateCreated
//some other stuff
Hits gets an insert whenever a link is clicked. (All this works just fine)
I'm trying to create a report that shows each link by its name and href, a counter and the last date each link was visited. What I currently have is an accurate listing for those that have been clicked on, but it does not show anything for links that haven't been clicked on. Any suggestions as to how I can modify the following SQL to return "0" and "never" (or DBNULL) if no entries are found in Hits that have the same id? Or do I have to do this in a couple queries?
SELECT COUNT(h.id) as counter, MAX(h.dateCreated) as lastVisited,
l.id as id, l.linkName as linkName, l.linkHref as linkHref
FROM Link as l INNER JOIN Hits as h ON l.id=h.moduleId
GROUP BY l.linkName, l.id, l.linkHref
View 6 Replies
View Related
Feb 4, 2008
Hello friends
I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more.
Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter.
And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master
how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
View 1 Replies
View Related
Jul 20, 2005
Unfortunately I can't use the excuse of being a SQL newbie, but drawn ablank on the following "simple" problem.I have a table containing the following (extract) :+----+-----------+------------+---------+| id | fk_MainID | fk_GroupID | Visible |+----+-----------+------------+---------+| 4 | 158 | 1 | 1 || 3 | 148 | 1 | 1 || 5 | 150 | 1 | 1 || 6 | 146 | 1 | 1 || 7 | 127 | 1 | 1 || 8 | 125 | 1 | 1 || 9 | 117 | 1 | 1 || 10 | 119 | 1 | 1 || 11 | 128 | 1 | 1 || 12 | 118 | 1 | 1 || 13 | 105 | 1 | 1 || 14 | 99 | 1 | 1 || 15 | 102 | 1 | 1 || 16 | 153 | 1 | 1 || 17 | 157 | 1 | 1 || 18 | 152 | 1 | 1 || 19 | 149 | 1 | 1 || 28 | 162 | 10 | 0 || 25 | 160 | 1 | 1 || 27 | 162 | 1 | 1 || 26 | 160 | 10 | 0 || 29 | 151 | 1 | 1 |+----+-----------+------------+---------+I need to find the "fk_MainID" where there is a "Visible=1" value, but NOT a"Visible=0".For example:SELECT fk_MainID FROM GRP WHERE Visible=1is no good because it will select fk_MainID=162, which also has a Visible=0row.I can do it with a sub-select, eg.SELECT fk_MainID FROM GRP WHERE fk_MainID NOT IN (SELECT fk_MainID FROM GRPWHERE Visible=0)but unfortunately the query has to be "cross platform" at least to theextent that it will also work on MySQL, which doesn't support sub-selects inthe release 3.x versions.Very grateful in advance for any help.Thanks
View 2 Replies
View Related
Oct 11, 2006
Say I have 3 .bak files named:jamesB.bak, jamesG.bak, jamesW.bakIs there a clever way to find out which is the most recent of thesebackup files?? Using sql query analyzer preferably...
View 4 Replies
View Related
Feb 1, 2005
We have a SQL Server database that runs a website on our server, however the company that we are running the website for requested that they have a replicated local version at their business. Everything is working fine.
However they have requested that they have access to the replication logs. They want to have the details of the recent replication details so they can see if changes they have made on their side has been transferre across successfully they also want a table with all the details in of the changes that have ever happened. Can anyone suggest a good way of doing this?
Thanks Ed
View 3 Replies
View Related
Apr 29, 2005
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
View 1 Replies
View Related
Aug 24, 2006
This routine works in most cases, but fails when a bad date is enteredsuch as:19910631 -- there is no June 31st.Instead of ignoring the bad date the entire DTS job fails. Obviouslythis is something that should be validated at data entry, butunfortunately the only control I have is when appending to the tablewith these data quirks. Any suggestions appreciated!!!'************************************************* *********************' Visual Basic Transformation Script' Copy each source column to the' destination column'************************************************* ***********************Function Main()'DTSDestination("Col002") = DTSSource("Col002")if DTSSource("Col002") = "99999999" or DTSSource("Col002") =Null thenMain = DTSTransforStat_SkipRowelseDTSDestination("Col002") = mid(DTSSource("Col002"),1,4) & "/"&mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)End ifMain = DTSTransformStat_OKEnd FunctionRBollinger
View 3 Replies
View Related
Jul 23, 2005
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies
View Related
Sep 6, 2007
Hi.
This is my first attempt at a using stored procedures and I'm a bit confused.
I'm trying to follow as many best practices as I can to improve speed, security and scalability. However, I can't find a solution to what I think should be a simple problem.
I have a search page where users enter the criteria of properties they are interested in (bedrooms, price etc...). That takes them to a results page where the properties are displayed.
The problem is that I want the amount of times each property has been shown on the results page to be tracked so the property owner gets statistics.
The property details are all held in a single table along with the amount of times each property has be shown:
Table Name: zk_Property_USA
ID
INT
User_ID
INT
Property_Type
TINYINT
Market_Status
TINYINT
Price
INT
Bedrooms
TINYINT
Address_State
VARCHAR
Address_Location
VARCHAR
Property_Description
VARCHAR
Searched
INT
0
Contacted
INT
0
I'm trying to find a way to SELECT all the property details to be returned to my results page and UPDATE the "Searched" field by 1 without re-scanning the table for the UPDATE.
Is there a way to update "Searched" at the time when the record is chosen to be a result? I am using SQL Server 2005 and Visual Basic ASP.NET 2.0.
Many Thanks
View 4 Replies
View Related
Nov 8, 2007
In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?
View 1 Replies
View Related
Sep 27, 2007
In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?
View 10 Replies
View Related
Apr 20, 2006
Hello,
i am making a Fulltextsearch on MS SQL Server 2005 (indexed, with "Contains").
Because of performance reasons i am only showing the first 200 rows mssql finds ("select top 200...:").
Is there any possibility to get the estimated totalnumber of all rows? i have heard something that is possible to get this in mssql-server. The server then estimates how many rows with that searchword could be in the whole database. google i.e. makes the same thing....
is that true? what do i have to do to get this?
greetings and thx
cpt.oneeye
View 1 Replies
View Related
Apr 1, 2002
I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
Thank you,
Eoin
View 2 Replies
View Related
Feb 7, 2008
I am serving ad-units. In each ad-unit I show somewhere between 3 and 10 article headline. I track the headline impressions to get an idea of the headline click through rate. I save the output from the stats process in another table. I am currently evaluating the stats every hour, and then truncating the table every night at midnight. The problem is that I get lots of impressions and the database gets bogged down evaluating the data such as...
SELECT COUNT FROM articleimpressions WHERE articleid = x
But the issue isn't the reporting of the data...so much as it's the capture. I had to add caching on the ad-server because the database couldn't handle the number of inserts.
I thought about parsing the web server log files the next day, but the file sizes seem to be too large, and I can't process them all in one day. (At least not on the hardware that I am using.)
I've thought about splitting log files by hour, but was wondering if there may be a more "native" solution within SQL Server? Maybe a trigger, and/or multi-threaded SP that fires and forgets an insert statement to a linked server. But performance is the key here.
Thoughts?
View 2 Replies
View Related
Jul 23, 2005
In MSSQL, is there a way to count the number of instances of asubstring within a string, so that I can sort by that?For example:table tst contains one column: tst_dataif tst_data = "the man with the plan"I'd want a function that counts the occurances of "the"count_substring(tst_data,'the') = 2Basically, I'm making a search engine and I'd like to put the mostrelavent hits at the top of the page.
View 2 Replies
View Related
Sep 24, 2015
I'm trying to piece together some code on how to find members of our organization who have visited our website.
View 2 Replies
View Related
Apr 11, 2006
I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Thanks
View 1 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Jul 15, 2013
I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .
test99: tableName countRows
t1 20
t2 30
t3 25
View 2 Replies
View Related
Jun 11, 2007
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.
I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:
Imports System.Data.SqlClient
Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.
any idea?
thanks
View 16 Replies
View Related
Jan 17, 2006
I have 2 tables:
TableA:
Name
UserA
UserB
UserC
Table B:
Name Data
UserA xxx
UserB asdasd
UserB ewrsad
UserC dsafasc
UserA sdf
UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count
UserA 2
UserB 3
UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
View 5 Replies
View Related
Oct 28, 2015
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?
View 2 Replies
View Related
Aug 6, 2006
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 5 Replies
View Related
May 23, 2007
Hello all,
I would like to use MDX to identify "contracts" which are in a table A and not in a table B, and count them to have a total per "company".
It should be something like:
Contract / Flag contracts not in table B (1=yes, 0=not)
---------------------------------------------------------------
Contract 1 1
Contract 2 0
Contract 3 1
Contract 4 1
Contract 5 0
Contract 6 0
TOTAL 3
So far I know how to distinguish at contract level which records belong to table A and not table B, but I do not have the total for the company.
In SQL it should be something like that:
SELECT Company,count(*)
FROM tableA
left join tableB
on tableA.Contract = tableB.Contract
WHERE tableB.Contract is null
GROUP BY Company
Thanks,
Guillaume
View 3 Replies
View Related
Oct 11, 2000
I noticed a web query was running unreasonably slow, so I investigated
further and found out that query was searching on the primary key field
of a table, but instead of using the index seek, it acutually did a index
scan. I don't know why that query didn't use PK index, since the search
was on PK itself.
There is an other SQL server which has the exact same table(same data), I ran
that query against that table, and the query did what it suppose to do,
it used PK index, and was able to retrieve data very fast.
So I ran following commands and see if the systerm would use PK when
running the query:
(recreate all index(all non clusted))
sp_updatestat (all indexes)
sp_recompile (table)
But it didn't work, PK index was still not been used when that query was run.
Although I were able to slove the problem by force the web query to
use PK index, but I really don't like to force useage on any index.
So I thought there might be some corruptions in that table.
I didn't a dbcc showcontig, it showed that Page Density is 95%
and Frag is only 3.4% I also didn't some manual calculation
and only found very very small amount of unused space.
But when I ran sp_spaceused against that table,
and found out that the ROW COUNT for that table is 0!
but if I did select count (*), it returned the correct row count.
So I ran following commands
dbcc checkdb --- no error reported
dbcc checktable --- no error reported
dbcc updateusage (table) --- still got 0 row cound for sp_spaceused
(0 rows count in sysindexes for that table)
Question 1: Why PK index was not been used when the "where" of that query
was PK itself.
Question 2: Why that table has 0 row count?
Question 3: How can I fix the row count problem without introducing a
clustered index on that table or rebuilding that table?
Thanks guys!
Xiao
ps. that table has about 400,000 rows, and size of 20 MB
View 1 Replies
View Related
Jul 19, 2005
select singer.id,singer.singer,count(albums.id) from singer inner join albums on albums.singer=singer.id where singer.country='England' order by singer.singer
doesnt't work because of the count(albums.id)
how can I do ?
View 3 Replies
View Related
Feb 23, 2007
hi all,
how do we sum up all the quantity in table A, and save the summary quantity into table B? can we do it live? i mean evtime quantities in table A change, the total sum in table B also change accordingly.. pliz help... thanks in advance
~~~Focus on problem, not solution~~~
View 4 Replies
View Related
Mar 15, 2007
Is it possible to directly get the row count of a different table in a RS2005 report?
I'd like to use this row count number in a calculation in another table...
This would be a lot cleaner than creating a new dataset to provide me with this value.
Thanks!
View 1 Replies
View Related
Mar 28, 2008
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
View 7 Replies
View Related
Mar 13, 2006
HelloI have this stored procedure:SELECT @openissue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND DateDIFF( day, TOpenIssue .DateStart, GETDATE() ) >= 0SELECT @oiclosed=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code = 'CLOSED' and Project=@project SELECT @oipastdue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND TOpenIssue .DateEnd<getdate()Is there away to optimize it in only one select statement?Thanks
View 2 Replies
View Related
Nov 3, 2006
Okay I wanted to get this to work right so I am wondering what I am doing wrong here.
"SELECT COUNT(A.Status) AS TOTAL FROM tts_tickets A,tts_reporters B WHERE A.Ref_Reporter_ID="123"AND A.Status=1"
I need to count the status options but only where from table b is the ID equal to the ID in question.
How do I do this?
View 3 Replies
View Related
Feb 3, 2005
I am inserting data from one table into another. To simplify:
Table 1 has columns:
1.A, 1.B, 1.C, 1.D
Table 2 has columns
2.A, 2.B, 2.C, 2.D, 2.Tag
where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
Not a count of how many records for each combination.
Therefore, if Table 1 is
x - x - x - x
x - x - x - x
x - x - x - x
x - x - x - y
x - x - x - z
then, table 2 is
x - x - x - x - 1
x - x - x - y - 2
x - x - x - z - 3
anyone help me w/ the sql on that ?
i was thinking of putting a variable in the select statement, but was unable to increment it.
thanks
View 14 Replies
View Related
