How To Delete A Counter In A Field ?
Aug 28, 2006I want to delete a counter using an SQL query (alter, drop...)
What could be the SQL query
ADVERTISEMENT
Unique Counter Field In A Table - Best Way?
Oct 11, 2005I need to to have a table that has a counter field (used to generate an id code for a record). This is not the primary id for the field but just a counter to label a record in another table, similar to a registration code for an event. This is a sequential counter that will start 1 and go up on each new record inserted into other tables. What I want to do is be able to query this counter table to get the next valid number and increment the count for the next time. This is a multi user web app so needs to prevent duplicate use of same number.How best should this be handled? A transaction to query and update the field?
View 6 Replies View RelatedAppend Counter Variable To Field Name
Jun 14, 2004Hi all,
I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!
Here's the proc:
WHILE @Counter < 4
BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @DateFrom) AND (tbl_SurgerySlot.StartTime < @DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)
SET @FieldName = 'Days' + CONVERT(VARCHAR(20),@Counter)
INSERT INTO tmp_Availability (@FieldName)
VALUES (@AppointmentsCount)
SET @DateTo = DATEADD(Day,1,@DateTo)
--Increment the loop counter
SET @Counter = @Counter + 1
When I run the above the follwoing message is displayed:
Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.
The object IS valid so I'm lost....
How Create An Automatique Counter Field With SQL Server ?
Nov 16, 1998Hello,
I would like to create an automatique counter Field like in ACCESS from Microsoft.
How can i do that ?
Delete A Field Help?
Apr 28, 2004I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?
How To Delete Records With A Field Containing NULL
Feb 9, 2008Hi,
All records of a table must be deleted if a field (field1) contains NULL or is made empty by a previous update command.
I did this but it only works when the field is made empty, not when it contains NULL:
dim field1 as string = ""...sql = "delete from mytable WHERE (field1=@field1)" comd = New SqlCommand(sql, oConnection) comd.Parameters.AddWithValue("@field1", field1) comd.ExecuteNonQuery()
How can i do that?
Thanks
Tartuffe
Resetting Value Of Identity Field After A Delete
Mar 29, 2000Can anyone tell me of a way to reset the value of an Identity field back to its seed value after all the rows in a given table have been deleted and I try to re-populate it? There are foreign key relationships so I cannot just drop and recreate the table, and I really need to address this issue from within sql, rather than from the application.
Thanks, any and all guidance would be great :)
How To Delete Duplicate Data From Field
Oct 22, 2007
my table structure is
id field1
1 i am from india
2 i am from usa
3 i am from delhi
So i want to remove common data from field1 , means after run the query table should be like
id field1
1 india
2 usa
3 delhi
thanks in advance
saumitra tamrakar
Delete Interval Of Values Of A Certain Field From A Table...
Dec 4, 2007
How do i use delete to perform a delete action of a value from a table using an interval of values?
--
delete [table] from [field]='[value]' to '[value]'
-- or
delete [table] where [field]='[value]' to '[value]'
--
i can't delete an interval of values (from...to) from a field of a certain table...
What is the right expression to do this?
Resetting The Id (key Field) To 1 When Using DELETE To Clear Records From A Table
Feb 17, 2008Is there a way that I can reset the key field to 1 when using DELETE to clear a table?
(Note: if there is a separate command that I could use after the DELETE, that is fine too.)
Thank you for your help with this,
-DJ
SQL Counter In Statement
Jul 5, 2006Hi
I'm using a gridview to show a list of high scores in a c#.net page. The position of the score is not stored in the database, just the score, name and id.
In the gridview however I would like to indicate the position of the scores, the SQL statement sorts the records by ascending scores so the are already in the correct order.
Does anybody know how to include a counter in the SQL statement that I can refer to vai the gridview so that it is able to display it as the position?
Thanks in advance for any help!
Multipage Hit Counter
Jun 21, 2004SQL Databased Multipage Hit Counter
# 1 - You want a Multipage Hit Counter to keep track of the number of Hits each individual webpage gets in your website.
# 2 - You also want to store the Webpage Hit Count Values into a SQL Database, using Stored Procedures.
Using 2 Webpages as an example:
--------------------------------------------------------------------------------
Inside webpage #1 put insert the following:
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim objCon As New SQLConnection("server=yourServerName;User id=idName;password=yourPassword;database=HitsCounter")
Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter1", objCon)
objCon.Open()
Dim r as SQLDataReader
r = cmd.ExecuteReader()
r.read()
strcounter3.text = "Hits : " & r.item(0)
end sub
</script>
Also insert the following inside the body tags of webpage #1
<asp:Label id="strcounter3" font-size="X-Large" font-bold="True" bordercolor="Silver" width="300px" borderstyle="Inset" forecolor="Lime" visible="True" runat="server"></asp:Label>
Marked in Red webcounter1 is the Stored Procedures Name.
ALso marked in<B> Red counter3 is the name of the Database.
This is where you would make your changes inside the Webpages.
--------------------------------------------------------------------------------
Use the following Stored Procedure with Webpage #1
CREATE PROCEDURE dbo.webcounter1
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
DECLARE @hits INT
SELECT Hit FROM counter3
WHERE
ID = 1
update counter3 set hit = hit + 1
EXEC sp_recompile counter3
END
GO
--------------------------------------------------------------------------------
The Database Table has 2 columns which consists of an ---ID & Hit Column -- The Table Name is (counter3)
The best way to explain is by showing so posted below are Webpages 1 & 2 along with there Stored Procedures.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Inside webpage #2 put insert the following:
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SQLClient" %>
<script runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim objCon As New SQLConnection("server=yourServerName;User id=idName;password=yourPassword;database=HitsCounter")
Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter2", objCon)
objCon.Open()
Dim r as SQLDataReader
r = cmd.ExecuteReader()
r.read()
strcounter3.text = "Hits : " & r.item(0)
end sub
</script>
Also insert the following inside the body tags of webpage #2
<asp:Label id="strcounter3" font-size="X-Large" font-bold="True" bordercolor="Silver" width="300px" borderstyle="Inset" forecolor="Lime" visible="True" runat="server"></asp:Label>
--------------------------------------------------------------------------------
Use the following Stored Procedure with Webpage #2
CREATE PROCEDURE dbo.webcounter2
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
DECLARE @hits INT
SELECT Hit FROM counter3
WHERE
ID = 2
update counter3 set hit = hit + 1
EXEC sp_recompile counter3
END
GO
--------------------------------------------------------------------------------
Now this type of Hit Counter works fine to a point.
Visiting Webpage #1 accumilates 1 Hit & Visiting Webpage #2 Accumilates 2 Hits.
And each time there 's a revisit to these pages they acquire 2 Hits each.
I'm Guessing the problem is in the Stored Procedure storing there values in Memory.
If Someone can Help Figure Out how to fix this. This could be a nice Website Multipage Hit Counter. I can use some help Guys feel free to advise.
Record Counter
Nov 7, 2000I'm trying to write a select that returns a result set with a counter added on. I want to do it without using an identity column or cursor. Something along the lines of select col1, max(col2)+1 from tab1.
I get (as expected)
col1 col2
------- ------
a 25
b 25
c 25
I want
col1 col2
------- ------
a 25
b 26
c 27
Thanks,
Jim
Thread Counter
Oct 23, 2003Hi Everyone,
The sqlserv.exe takes too much CPU utilization on my SQL 2000 on W2K production machine. I am tring to use System Monitor to monitor the Thread/%process time with all Sqlservr instances, and then match the sqlservr instance number to the KPID in sysprocesses table to find out which user is causing the problem. but I can only see the instance number from Sqlservr0 to sqlservr99. From the table sysprocesses table, the KPID is all 3 or 4 digits number. Any one has any idea about this?
Thanks in Advance.
Jason
Record Counter
Oct 11, 2006I need to add a ClaimCount column to a table. I am having a bit of trouble with the SQL to make sure it populates correctly.
Current Table
claim#namePaid
123George Washington50
123 George W Washingon50
124Sam Adams100
126George Washington75
128Rich Cheney69
128Dick Cheney69
128Richard CHeney69
Want to add a column (ClaimCount) as follows
Claim#namePaidClaimCount
123George Washington501
123 George W Washingon502
124Sam Adams1001
126George Washington751
128Rich Cheney691
128Dick Cheney692
128Richard CHeney693
Rules for ClaimCount.
1) there is no order by, It doesn't matter which record with the same Claim# is 1 or 2 or 3...
Appreciate the help.
Ray
Incremental Counter
Nov 16, 2007I am looking to create a incremental value based on the resulting insert that I am using. There already is another field being used as an identity field. I have a beginning value that I just want to add the row number to for the insert.
insert into lineitem select substring(group_id,4,len(ltrim(rtrim(group_id)))-3) as co_code,
0,0,(case when enddate < cast(month(getdate()) as varchar(10))+cast(day(getdate()) as varchar(10))
then 'Prior' else 'Current' end ),
left(acct_type,2) as bene_type, convert(smalldatetime,left(ltrim(rtrim(eff_date)), 8)),
0,trans_amt,0,0,convert(smalldatetime,left(ltrim(r trim(sett_date)),8)),
ltrim(rtrim(b.fname)) + ' ' + ltrim(rtrim(b.lname)) as payee,0,0,a.ssn,'Y',999+count(*),
(case when isnull(b.location,'') = '' then '' else b.location end) as location
from mbi_tran_temp a
left join enrollees b on a.ssn = b.ssn and
ltrim(rtrim(a.group_id)) = ltrim(rtrim(b.mbicode))
The '999+count(*)' is where I would like to have the incremental value.
Simple Row Counter
Mar 9, 2004It sound like a simple task to perform but I just can't seem to get it. I've built a search function on the site and after each search request, a log is kept of the search word and the date, so the table looks a little like this:
ID SearchTerm DateSearched
============================
1 home 2004/03/09
2 fred 2004/03/08
3 cup 2004/03/08
4 home 2004/03/08
5 home 2004/03/08
6 fred 2004/03/07
I want to pull out each of the search terms as well as how many times they've been searched on so I could display it in the format like so:
Word Qty
============================
home 3
fred 2
cup 1
Any help would be appreciated. Thanks.
Goran (GoMo)
T-SQL (SS2K8) :: Way To Add A Counter At End Of Each Row
Aug 18, 2015My client wants IN_PUNCH to be the first in punch of the day and the OUT_PUNCH to be the last out punch of the day based on the TransactionDate as it is showing in the desired results table for 07/29 and 07/30 (Changes are highlighted in Bold).
I tried using MIN and MAX at different areas but to no avail.Also, is there a way to add a counter at the end of each row so in case if there were two shifts in a day then it be a '1' for row 1 and a '2' for row 2?
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS,PAYCODE,SHIFT_LABEL,DOW) AS
(
SELECT 1234,'07/27/2015','07/27/2015','07/27/2015', '12:00','12:00','8', 'Hol', 'NULL', 'Monday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '08:00','','4.00', 'Absent', 'Batax 1st','Tuesday' UNION ALL
SELECT 1234,'07/28/2015','07/28/2015','07/28/2015', '12:15','14:00','3.75', 'Regular', 'Batax 1st','Tuesday' UNION ALL
[code]...
Getdate Day Counter
Dec 13, 2006Hello,
I need to count the days (24 hours)from the (GETDATE()),returning an integer in a table column, automatically, all the time , under the scene until after a specified number of days (usually 20 for example), the relevant table row is be automatically deleted, replaced by another insert (row) to start again and the process repeated many times.
I have a database which works automatically. The data need not to be queried or manipulated until the row is deleted automatically.
I use SQL Server 2000.
Any ideas of doing this in a simple way ?
Regards and thanks in advance.Yves.
Sequence Counter
Nov 5, 2007I'm looking for a query that will look at an Id field and if it occurs more than once then returns the count of the times it occurs. For Example,
ID Code GetSequence
4 239 1
4 241 2
4 3243 3
RESET COUNTER
Nov 9, 2007Hello everyone:
this procedure resets the business day to one for every month. It works fine except for the month of October. any idea or suggestions?
DECLARE @i INT
SET @i = 1
DECLARE @DateID INT,
@DtTimeD DATETIME,
@LASTDAY DATETIME
DECLARE c CURSOR
FOR
--
-- LASTDAY is the last day of the month
-- the counter will reset to 1 on the first of each month
--
SELECT DateID, DtTimeD, DATEADD(dd, -DAY(DATEADD(m,1,DtTimeD)), DATEADD(m,1,DtTimeD)) 'LASTDAY'
FROM D_DATE
WHERE WkDayIn = 'Yes' AND HolidIn = 'No'
OPEN c
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
WHILE @@FETCH_STATUS = 0
--
-- update the business day in D_DATE
--
BEGIN
UPDATE D_DATE
SET BusDay = @i
WHERE DateID = @DateID
--
-- reset the counter to 1 if it's the last day of the month
--
IF @DtTimeD = @LASTDAY
SET @i = 1
ELSE
SET @i = @i + 1
--
IF @@ROWCOUNT = 500
COMMIT
--
FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY
END
CLOSE c
DEALLOCATE c
GO
Record Counter
Feb 15, 2006I have a form that I turned off the nav buttons. I then create my own.The reason I do is that I cna then move the nav buttons any where I needto on the form.The only thing I can not figure out is on the default ones, it shows thetotal records. I can not figure out how to do this. The form is based ona table and when I add a record or delete a record I want to make ssurethe total displayed is correct.How can I display the total records, for the table, on the form on thefly?Does that make sense?Michael Charneym charney at dunlap hospital dot org*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHow To Use Variables As A Counter?
Aug 8, 2006While inserting data into a target table, I'm trying to populate a primary key ID field sequentially. For each record, the value of the primary key field needs to be incremented by one (a counter).
I've tried to use the RowCount transformation to store the values in a variable. I'm able to successfully do that; however, I don't know how to read or update the variable incrementally.
If someone knows how to perform this task, please let me know. I would greatly appreciate it.
Counter By Group
Jul 18, 2007How can I create a counter (line#) by group were it resets = 1 whenever a new group starts.
Customer Line#
A 1
A 2
A 3
B 1
C 1
C 2
Insert Counter
Apr 8, 2006when a new record is inserted in to table, i want to increment the variable called counter by 1
how can i implement this?
what is the IDENTITY field in a table? is it something like a counter variable
pls let me know
Database Page Counter
Aug 6, 2004Hi All,
I need some comments regarding my Hit Counter that I have created for my sites. Here is my case:
I have developed several websites which I host along with MS SQL server. I created a class that calls a stored procedure to add a count to the database field. The way that I am using it is that every page in the page_load event will call the class indicating which counter to use (i.e. LoginPage, HomePage, ProductPage, etc.). The class in turn will perform the database route to increment the counter. In the Store procedure before incrementing the counter, I first check to see if a record for the current date has been created, if not I add the record with a date stamp with all the different counter values as zero. I then will add 1 to the desired counter for the date.
Is this a good solution?
What I now want to do is create a page that allows the different website owners to access a page with the relevant page counters.
Any ideas will be greatly appreciated.
Thanks
Can't Start Performance Log Counter
Jul 22, 2005I'm a local admin on a SQL Server with dba permissions. I can view that SQL Server in System Monitor and I can create a performance log counter. When I try to start the counter, I get an error: ---------------------------
Performance Logs and Alerts
---------------------------
The SQLSever log or alert has not started. Refresh the log or alert list to view current status, or see the application event log for any errors. Some logs and alerts might require a few minutes to start, especially if they include many counters.
---------------------------
OK
---------------------------
Does anyone have ideas why this isn't working?
Line Count Counter
Jun 13, 2008CREATE TABLE#Info
(
DbName SYSNAME,
ROUTINE_TYPE SYSNAME,
ROUTINE_NAME SYSNAME,
Lines INT
)
EXECsp_msforeachdb'
INSERT#Info
(
DbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
)
SELECT''?'',
d.ROUTINE_TYPE,
d.ROUTINE_NAME,
SUM(CASE WHEN d.c10 < d.c13 THEN d.c13 ELSE d.c10 END)
FROM(
SELECTROUTINE_TYPE,
ROUTINE_NAME,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(13), '''')) AS c13,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(10), '''')) AS c10
FROM.INFORMATION_SCHEMA.ROUTINES
) AS d
GROUP BYd.ROUTINE_TYPE,
d.ROUTINE_NAME
'
SELECTDbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
FROM#Info
ORDER BYDbName,
ROUTINE_TYPE,
ROUTINE_NAME
DROP TABLE#Info
E 12°55'05.25"
N 56°04'39.16"
How To Get Sum Of Counter With Time Difference
Dec 9, 2013Table
DateField--------------CNT-1 CNT-2 CNT-3
2013-12-09 09:00:00.000 8 1 2
2013-12-09 09:01:00.000 3 7 1
2013-12-09 09:02:00.000 1 2 3
2013-12-09 09:03:00.000 2 5 2
2013-12-09 09:04:00.000 1 1 7
2013-12-09 09:05:00.000 2 3 2
My requirement is to get the output in the following format if i take the time with 2 minutes difference
DateField ------------------ Sum-CNT-1 Sum-CNT-2 Sum-CNT-3
09:00:00.000 - 09:02:00.000 12 10 6
09:02:00.000 - 09:04:00.000 3 6 9
Is there anyway i can write down a query to get the sum of the counter with 2 minutes difference. I may take time slot between 9 AM to 10 AM so i should get the sum of the counter with 2 minutes gap in a single query.
Performance Counter Problem
Oct 5, 2005I'm installing SQL2005 September CTP on a fresh W2K3 SP1 machine running on Virtual Server.
View 1 Replies View RelatedThis Should Be The Easist Thing To Do... Have A Counter...
Apr 25, 2007This should be so easy.
All I want to do is have a cumalative counter that counts from 1 to whatever.
any ideas how i can do this?
Group Counter In Condition
Oct 16, 2007Hi All,
I have a tricky issue and plesae help:
My dataset likes this:
Level people
1 A1
1 A2
1 A1
2 A3
2 A3
2 A4
2 A4
2 A4
I want to create a summary report and get the results like:
Records People
Level 1 3 2
Level 2 5 3
I only two levels: Level 1 and level 2 and don't need consider the lable of level.
How can I implement this in the Table or Metrix?
TIA
Micror
Counter Inside Select Statement?
Nov 14, 2007Hi, can you add a counter inside a select statement to get a unique id line of the rows?
In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.
This is the normal way:
SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements
This is what i want:
DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1)
Anyone know if this can be done?