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?
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)
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.
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...
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'
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
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
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#);
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.
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.
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.
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.
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 !!!!!!!!!!!!
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
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.
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.
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
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.
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
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
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).
