Append Counter Variable To Field Name

Jun 14, 2004

Hi 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....

View 8 Replies


ADVERTISEMENT

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes

Oct 15, 2007

I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.

Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)

It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.

Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!

Here is the code of the function in the book:

/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0

-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO

View 1 Replies View Related

How To Delete A Counter In A Field ?

Aug 28, 2006

I want to delete a counter using an SQL query (alter, drop...)

What could be the SQL query

View 3 Replies View Related

Unique Counter Field In A Table - Best Way?

Oct 11, 2005

I 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 Related

How Create An Automatique Counter Field With SQL Server ?

Nov 16, 1998

Hello,
I would like to create an automatique counter Field like in ACCESS from Microsoft.
How can i do that ?

View 1 Replies View Related

Append A Field

Aug 27, 2004

Hello,

How do I append data on an update?
I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that?

Update table set fieldname = 'more data' where value = @variable

instead of

Update table set fieldname = 'initial data more data' where value = @variable

and the 'more data' appends to the initial data... hmmm

help please.

View 3 Replies View Related

Trying To Append A Field If An Insert Happens To Contain A Duplicate

Jan 9, 2004

I deliberately intend to add some duplicates to one of my tables. For eg

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5
JobID 235User ID 2
JobID 235User ID 9
JobID 235User ID 10
JobID 235User ID 1

I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5 True
JobID 235User ID 2
JobID 235User ID 9 True
JobID 235User ID 10
JobID 235User ID 1 True

Thanks

View 1 Replies View Related

How To Append Data To Ntext Field?

Feb 27, 2006

Hi,
Can anybody tell me how to append data to ntext field?
Joydeep

View 9 Replies View Related

Append String To Text Field....

Dec 10, 2007

I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work:

UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &"

I get an error saying:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text.

How can I append some text to the end of a field? Thanks for any help!

View 14 Replies View Related

Why Adox Can Not Append AdUnsignedBigInt Field ?

Jan 22, 2008

my code:



Code Block

void AppendTableTest()
{
// Define ADOX object pointers, initialize pointers. These are in ADOX namespace.
_CatalogPtr m_pCatalog = NULL;
_TablePtr m_pTable = NULL;
try {
HRESULT hr = S_OK;
TESTHR(hr = m_pCatalog.CreateInstance(__uuidof(Catalog)));
// Open the catalog
m_pCatalog->PutActiveConnection("Provider='Microsoft.JET.OLEDB.4.0';data source='c:\new.mdb';");
//m_pCatalog->Tables->Delete("MyTable");
TESTHR(hr = m_pTable.CreateInstance(__uuidof(Table)));
m_pTable->PutName("MyTable");
m_pTable->Columns->Append("Column1",adInteger,0);
m_pTable->Columns->Append("Column2",(DataTypeEnum)21,8);
m_pTable->Columns->Append("colBinary",adBinary,8);
//m_pTable->Columns->Append("Column3",adVarWChar,50);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
printf("Table 'MyTable' is added.");
// Delete the table as this is a demonstration.
//m_pCatalog->Tables->Delete("MyTable");
printf("Table 'MyTable' is deleted.");
}
catch(_com_error &e) {
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
TRACE(" Source : %s description : %s ", (LPCSTR)bstrSource, (LPCSTR)bstrDescription);
}





but i got a invalid type error. I cannot understand.
why adox can not append adUnsignedBigInt field ?
Somebody can give me a answer? Thanks.

View 1 Replies View Related

Append String To Field Value In Select List

Jul 26, 2004

How can I append a string to the field value in the select list


SELECT Code + '-20' FROM tb.....

I want to the above to return 2000-20 for example.

How can I do this?

Mike B

View 1 Replies View Related

SQL Server 2012 :: Looping Through Rows And Append Values To A Declared Variable

Apr 3, 2014

I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration.

declare @i int,@output varchar(max)
set @i = 1
while @i < 10
begin
set @output = @output + convert(varchar(max),@i) + ','
print @output
set @i = @i +1
end

View 6 Replies View Related

Variable In A Field

Sep 6, 2004

Could anyone tell me why this isn't working, or if it simply can't be done.

DECLARE @x varchar
SET @x = 'ITEMNMBR'

SELECT *
FROM IV00101
WHERE @x between '3530001' and '3530020'

View 4 Replies View Related

Passing A Column/field Name As A Variable?

Aug 24, 2005

Is it possible to use a table's fieldname as an SQL variable?

i.e. can the below be made to somehow work:


Code:

SELECT Firstname, Surname, myVariable
FROM ContactDetails
WHERE myVariable = [user input];



- or simply -


Code:

SELECT Firstname, Surname, [user input]
FROM ContactDetails;



---
The "user input" being any other chosen column/fieldname from the ContactDetails table (e.g. Street, City, Postcode, etc.).

i'm using Access and ASP - in case that makes a difference.

any help would be greatly appreciated.

View 1 Replies View Related

Variable As Field Name In CURSOR FOR UPDATE

Dec 10, 2005

I'm trying something like:

UPDATE tbl SET @varFieldName = @varValue

The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.

I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.

To get the field name, I :

SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'

Thanks,
Carl

View 2 Replies View Related

Running Date Variable On One Field

Sep 19, 2014

I have two tables that I am pulling data from: an item table and a sales table. Almost all of the information comes from the item table (item description, location, amount on hand). The last field wanted is Year-To-Date sales. I can pull the sales field from the sales table, which gives me all sales from the creation of the db. I need to be able to run a date variable of This Year on that sales field only. I have a date field I can reference off of in the sales table.

View 6 Replies View Related

Connecting Variable To Database Field

May 10, 2007



Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database??



here is the script itself

thanks

km



Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

View 3 Replies View Related

How To Add Variable To Sql Command Text Field

Sep 7, 2007

In the data flow task I have multiple OLE DB source task to retreive data from a cube using MDX. Below is an example of the query from sql command text field: I have a few questions on how to modify this statment to make it more robust.

1) The where clause [Calendar].[Quarter 2 (2007)] should actually be some kind of variable that will change each time
the user runs this project. So I am wondering how to make this a variable and get it into this field.

2) I had thought that I could use the SSIS variable. I have created one, but I can not figure how to get it into the field that
contains the given select statment.

3) I believe that once I get this variable part to work then I want a way to have user set this data value. Either by selecting data from a table in database or through a user interface where user enters data. I did do some resarch on creating a user interface, but I did not understand what I had to do, so if any one knows where to find a tutorial on how to do this let me know, or what they believe the best/easiest way is to get data from user to fill this where clause.
select * from OPENROWSET('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Adjusted Relative Weight],[Measures].[Adjusted Case Weight]} ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS) ON ROWS FROM [ESR]
WHERE [Calendar].[Quarter 2 (2007)]
')

View 10 Replies View Related

Passing An Array And/or Variable Field Name To An SProc

Jul 20, 2005

I have 2 questions.I am trying to write a stored procedure to update a table. I am tryingto pass a variable that represents the name of the column/field andanother for the value that I am changing.For example:@FieldName VARCHAR(100)@FieldValue VARCHAR(100)ASUPDATE tblTHETABLESET @FieldName = @FieldValueFirst is it possible to use a variable as the column/field name? Ifso, how do I go about it?Also, it would be nice if I could have the @FieldName and @FieldValuevariables as arrays. Is that possible?Thank-you for any assistanceBill

View 2 Replies View Related

How To Calculate The Size Of A VARBINARY(max) Field Or Variable

Jan 7, 2008



How can i do the following:


Calculate the size of a varbinary(max) field or variable

Calculate the average of a varbinary(max) table column
I am using SQL 2005
Thanks for your posting

View 3 Replies View Related

Can I Use A Variable In The Attachments Field On A Send Mail Task?

Dec 12, 2007

I need to email a report that contains a date in the report name. Can I build the file name with script and use a variable in the attachments field on the send mail task. Any advice or suggestions would be appreciated.

View 1 Replies View Related

Limit On SSIS String Variable Size, Trying To Get Big Xml File Into Xml Data Field

Nov 28, 2007

I have a SSIS package that opens an xml file, puts the contents into a string, then runs a stored procedure that dumps it into an xml column in a table. One of the xml files is huge. Putting the data into a ssis string causes an error. The length of the string variable is 58,231,886. The file will only get bigger.

How else can I get this data into a SQL Server XML field.

View 1 Replies View Related

SQL Counter In Statement

Jul 5, 2006

Hi
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!

View 2 Replies View Related

Multipage Hit Counter

Jun 21, 2004

SQL 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.

View 3 Replies View Related

Record Counter

Nov 7, 2000

I'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

View 2 Replies View Related

Thread Counter

Oct 23, 2003

Hi 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

View 2 Replies View Related

Record Counter

Oct 11, 2006

I 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

View 10 Replies View Related

Incremental Counter

Nov 16, 2007

I 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.

View 13 Replies View Related

Simple Row Counter

Mar 9, 2004

It 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&nbsp;&nbsp;SearchTerm DateSearched
============================
1&nbsp;&nbsp;&nbsp;home&nbsp;&nbsp;&nbsp;2004/03/09
2&nbsp;&nbsp;&nbsp;fred&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2004/03/08
3&nbsp;&nbsp;&nbsp;cup&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2004/03/08
4&nbsp;&nbsp;&nbsp;home&nbsp;&nbsp;&nbsp;2004/03/08
5&nbsp;&nbsp;&nbsp;home&nbsp;&nbsp;&nbsp;2004/03/08
6&nbsp;&nbsp;&nbsp;fred&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;Qty
============================
home&nbsp;&nbsp;&nbsp;&nbsp;3
fred&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2
cup &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1

Any help would be appreciated. Thanks.

Goran (GoMo)

View 4 Replies View Related

T-SQL (SS2K8) :: Way To Add A Counter At End Of Each Row

Aug 18, 2015

My 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]...

View 2 Replies View Related

Getdate Day Counter

Dec 13, 2006

Hello,

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.

View 12 Replies View Related

Sequence Counter

Nov 5, 2007

I'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

View 5 Replies View Related

RESET COUNTER

Nov 9, 2007

Hello 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

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved