Updating Table Column With Cumulative Numbers

Nov 13, 2007

Hello,

One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?

Thanks a lot,
Andy

SQL version: 2005, Standard edition.

View 1 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Calculate Cumulative Numbers

Feb 6, 2015

I want to show cumulative numbers, but don't know how to calculate them.

Here is an example of the source and the wanted result:

Source:

[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)

Result:

[Week] [Count]
1 15
2 20
3 26
4 36

Is this possible, and how?

TestData:

USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)

View 8 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Add New Table Column With Numbers

Jul 19, 2007

Hello.

Whats the easiest way to add a column with a numeration to my table?

I want the users to have an easier life finding on which row they are
talking.

I preffer of course doing it in reporting service and not adding a
column in my dataset.

Thanks.

View 1 Replies View Related

Masking Table Of ID Numbers - Populating A Column

Aug 29, 2013

I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...

View 4 Replies View Related

Inputting Random Numbers To Table Column From Web Form

Feb 28, 2007

I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click        If Page.IsValid Then            ' Define data objects            Dim conn As SqlConnection            Dim comm As SqlCommand            ' Read the connection string from web.config            Dim connectionString As String = _            ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString            ' Initialize connection            conn = New SqlConnection(connectionString)            ' Create command            comm = New SqlCommand( _            "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _            "CategoryID, SubjectID, Description, StatusID) " & _            "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _            "@CategoryID, @SubjectID, @Description, @StatusID)", conn)            ' Use randomize            Randomize()            Dim randomvalue As Integer            ' Generate random value between 999 and 100.            randomvalue = Int((900 * Rnd()) + 100)            ' Add command parameters            comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50)            comm.Parameters("@First_Name").Value = fnameTextBox.Text            .            .            .            comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int)            comm.Parameters("@StatusID").Value = randomvalue            'Enclose database code in Try-Catch-Finally            Try                ' Open connection                conn.Open()                ' Execute the command                comm.ExecuteNonQuery()                ' Reload page if the query executed successfully                Response.Redirect("HelpDesk.aspx")            Catch                ' Display error message                dbErrorMessage.Text = _                    "Error submitting the help desk request! Please try again later, and/or change the entered data!"            Finally                'close connection                conn.Close()            End Try        End If    End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch'  and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue' 

View 2 Replies View Related

SQL Server 2014 :: Updating A Column In One Table From Another Table

Dec 23, 2013

We have two tables with names X and Y.

X has a,b columns. And Y has c,d columns.

I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.

View 3 Replies View Related

Updating Column In A Table

Jan 18, 2001

hi i want to update column with new value in a table is it possible to do so in stored procedure . the name of the column will be an input to stored procedure ie at the time of writing the stored procedure i dont know which column the user will be updating

View 2 Replies View Related

Updating Table With New Column

May 25, 2001

Does anyone know of the SQL statement to add additional comuns to an existing table. I know i can do it through enterprise managaer, but I want to see if I can do it from query analyzer or some script to add columns without having to recreate the table. Or a way to save off the data and recreate the table then placing data back in? I hope that makes sense. I am thinking of this from the sens of doing all updates through source safe

Thanks!

View 1 Replies View Related

Updating A Column With A Count From Another Table?

May 18, 2014

My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.

e.g. TABLE2:

e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5
UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);

--^Doesn't work

so my TABLE1 should be:

e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0

(The .. is just spacing to show the table here)

View 1 Replies View Related

Updating A Column With A Column From Another Table

Apr 1, 2004

Hi, I am kinda new to this so here it goes:

table name: USERS
field names : LName, FName, EmpID


other table name:PERSONNEL
field names : (same as USERS)

The EmpID column in my USERS table is blank (I have 30 records in the table.)

I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data

how would I do this or what would the code be?

sanctos

View 2 Replies View Related

Updating A Column In A Table That Contains 50 Million Rows

Feb 27, 2008

I'm looking for some performance assistance on updating a column value in a table that contains approximately 50 million rows. I have a permanent table in another database that has the key column and value to be set. My query is listed below, but I'm afraid it will run quite awhile. Any suggestions would be appreciated.

update mytable
set column2 = b.column2
from mytable as a
join mytable1 as b
on a.column1 = b.column1



There is a one to one relationship between the two tables.

View 8 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Transact SQL :: Cumulative Sum On Value Column

Apr 28, 2015

How would I iterate through this table and do a cumulative sum on the value column :

I'm trying to get the following result:

View 5 Replies View Related

Updating Two Columns With Sequence Numbers With Where Clause?

Jun 29, 2015

I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.

my example table:

numez numhx
111 111
111 111
0 111
111 0
111 0

and my results should be:

numez numhx
1 2
3 4
0 5
6 0
7 0

View 4 Replies View Related

Updating A Column In One Table From Another Table

Dec 23, 2013

We have two tables with names X and Y.

X has a,b columns. And Y has c,d columns.

I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.

View 1 Replies View Related

Data Mining :: Updating Two Columns With Sequence Numbers With Where Clause

Jun 29, 2015

I have a question  in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.

Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0

and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0

View 3 Replies View Related

Updating A Single Column On A Table SQL Server 2005 (45 Records), The Session Hangs...

May 12, 2007

Hello, I am pretty new with SQL Server 2005.

I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

select pos_key from pos_station where staff_key = 1105

POS_KEY
=======
NULL


update pos_station set pos_key = 1 where staff_key = 1105

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The table has a few constraints and a couple of indices in place.

Then I create another table (but no contraints or indices), just copy the data from the problematic one and the update WORKS (in msecs) :



update pos_station_new set pos_key = 1 where staff_key = 1105

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Is there any way to tell if the table (any table in SQL Server) is corrupted or not ?

How can I tell if a session is waiting for something and what is that something ?

Thank you very much for your help.

Tom

View 7 Replies View Related

How Can I Get The Cumulative Sum Of A Field In A Table

Aug 29, 2007

For Eg.

I have a table like gias given below:
Name Amount
------------------------------
aaa 10
bbb 20

and I want an output like one given below on running SQL query or stored procedure
Name Amount cumulative amount
---------------------------------------------------------------------
aaa 10 10
bbb 20 30

can anyone plz help me on this

View 28 Replies View Related

Calculate Cumulative Amount From Table

Sep 2, 2014

I need to calculate cum amount from the following table.

CREATE TABLE #TotalRevenue_Investments
( [Month] INT,[Year] INT,TotalRevenue INT,Descr VARCHAR(100),Company VARCHAR(100))
INSERT INTO #TotalRevenue_Investments
( Month ,
Year ,
TotalRevenue ,

[Code] ....

AND so ON ..

I need the OUTPUT AS FOR example

SELECT 1 AS Month,2014 AS Year,12 AS cumAmt,'Late Sales' AS Descr,'US Late Sales' AS Company

View 3 Replies View Related

Update Table - Running Cumulative Sum

Dec 18, 2013

How can I update a table to have a running cumulative sum?

For Example:

Update Table1 Set Cumulative_Sum = Row_Sum + Previous_Row_Sum

It should look something like this:

Row_Sum Cumulative_Sum
1 1
2 3
3 6
4 10

View 6 Replies View Related

Updating A Table By Both Inserting And Updating In The Data Flow

Sep 21, 2006

I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.

Any suggestions?

View 7 Replies View Related

Multiply Numbers In A Column

Mar 21, 2002

Hi

I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.

There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :

SELECT EXP(SUM(LOG(ColumnName))) FROM tblName

This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.

I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.

If anyone has any ideas or suggestions, then that would be much appreciated.

thanks....Tom

View 1 Replies View Related

Sequential Numbers In Column

Oct 25, 2013

I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.

Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?

View 3 Replies View Related

STUCH WITH SSN NUMBERS COLUMN....HW TO DO

Apr 23, 2008



OK I HAVE AN INTERESTING SITUATION HERE.

I HAVE AN SSN COLUMN.

SSN NUMBER HAS 9 NUMERIC DIGITS.

NOW IN THE SSN COLUMN I HAVE A LOT OF BAD SSN WHICH CONTAIN ONLY 3 OR ONLY 4 OR ONLY 5 AND SO ON DIGITS.

HOW CAN I FILTER THESE ONES OUT SO THAT I CAN MAKE AN EXCELL REPORT OF THIS DATA.

I AM THINKGING OF USING CONDITIONAL SPLIT.

BUT HOW SHOULD I GO ABOUT IT.

PLEASE LET ME KNOW THE COMMAND

View 1 Replies View Related

Removing Numbers From A Column

Oct 11, 2007



Hi,

I have the following tables :




Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))

Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);

The final data looks like this :

Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL






1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column




Code Block
Expected Output :

Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL






Can anyone help me please with the query?

thanks.

View 4 Replies View Related

Convert Sql Smalldatetime Column Into Numbers

Aug 27, 2004

HI all;

I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.

Any help would be highly appreciated, thanks all.

View 1 Replies View Related

Ordering A Column In Descending With Numbers First

Oct 8, 2013

I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.

Example:

Source column:
2008
2005
2010
2013
All-Year
Month-year

Required Output:

Source Column:
2013
2010
2008
2005
Month-Year
All-year

View 4 Replies View Related

Reuse Numbers In Identity Column

Mar 4, 2008

Hello,

I have an ID column which is an Identity column, and it counts from 1 upwards.

If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.

Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?

View 5 Replies View Related

Create Sequential Numbers In A Column

Jul 20, 2005

I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq

View 1 Replies View Related

Gradually Add O Substract Numbers In A Column

Nov 22, 2007

how do I gradually add o substract numbers in a column for example if I have



total
deposito
test

120

120


80
40

77

117

7

124


4
120


i need in the test column:
if the number is in the total column the number in test is added
if the number is in the deposito column the number in test is substracted

i have this query:


SELECT gir_cantidad as total,null as deposito,'operation' as test

FROM giros inner join corresponsales on cor_corresponsal_id = gir_corresponsal_id where gir_fecha >= '11/21/2007' and gir_fecha <= '11/22/2007 23:59:59' and gir_fecha_anul is null and gir_agencia_id = 1

UNION

select null as total,paa_valor as deposito,'operation' as test from pagosagencia where paa_fecha >= '11/21/2007' and paa_fecha <= '11/22/2007 23:59:59' and paa_agencia_id= 1 order by gir_fecha

In column test i need the functionality to add and subtract the columns total and deposito

Please help

View 4 Replies View Related

Updating The Column Defined As 'Text' Column

Oct 31, 2002

Hi,

I have a table with col_noteText defined as 'Text' datatype column.

I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.

Does anyone know how to do this for 'Text' datatype column.

Thanks in advance.

jfk

View 1 Replies View Related

Identity Column Jumped 1.8 Billion Numbers

Aug 19, 1999

After moving our database between servers, one table had a hiccup in it's
identity column. The number jumped from 761 to 1886863475 on the next
insert. This is a production server and I didn't catch it until yesterday.
So I have several days worth of numbers in a central table. I can't clean
them out, but I wondered if I could do the following:

DBCC CHECKIDENT ('table_name', RESEED, 800)

I tested it on our development server and it doesn't seem to cause any
problems. I know that when we get to 1.8 billion on the identity column
again we'll get an error, but I'm willing to risk it.

A few questions. 1) Am I insane for trying this? 2) Has anyone else ran into
a similar problem and what did you do to fix it? 3) If I do this, is there
anything in particular to watch out for? 4) What caused the jump in the
first place?

My other option is to change the datatype from int to decimal(19) or
something along those lines. This will upset our programmers and I'll have
to change all my foreign keys (not a big deal, just a pain).

Any input at all here would be appreciated.

Thanks,

Grant

View 1 Replies View Related







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