How To Work Out The Difference Of Two Numbers In SQL Table.
Jul 11, 2006
Hi
I have 2 columns the first column is a counter thats always counting up like a meter, the second column is a formula that calculate the difference between the counter(column one). like
Counter
Difference
1
1
3
2
6
3
9
3
11
2
13
2
17
4
25
8
The first row is taken as it is, but the second row will be difference = 3-1 the third one will be 3-6 and so on.
How can I do this in a SQL table or any other away?
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'm unable to get an alert to fire for Error 2627, Primary Key Violation. I have followed BOL's instructions for creating an alert, which appears to be nearly identical to 2000, however the alert will not work. I can see the error number in a Profiler trace along with the Severity number. I've also tried creating alerts for other error numbers and had the same result. The BOL instructions are as follows:
To create an alert using an error number
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. 2. Expand SQL Server Agent 3. Right-click Alerts and then click New Alert 4. In the Name box, enter a name for this alert. 5. Check the Enable check box to enable the alert to run. By default, Enable is checked 6. In the Type box, click SQL Server event alert. 7. In the Database name list, select a database to restrict the alert to a specific database 8. Click Error number, and then type a valid error number for the alert 9. To restrict the alert to a particular character sequence, check the box corresponding to Raise alert when message contains, and enter a keyword or character string for the Message text. The maximum number of characters is 100
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.
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference. pls reply. first: create table ##temp ( name varchar(25), roll int ) insert into ##temp values('banti',1) select * from ##temp second: create table #temp ( name varchar(25), roll int ) insert into #temp values('banti',1) select * from #temp
both works fine , then what is the difference waiting for ur reply Banti"
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I have a field (varchar) in a list that contains numbers and letters. I want to sort this table but I have only two functions that will work to convert the values:
Val The Val function sorts the numbers in the string, but the letters are not sorted
CStr The CStr function sorts the letters, but the numbers are not sorted
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
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...
Hi I have a report, a matrix as always, were the numbers are text when I save the report to Excel. What did I do wrong or what properties do I have to change? Kind regards
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'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'
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2) SELECT @Start=01.00, @End=20.00 ;WITH NumberSequence( Number ) AS ( SELECT @start as Number UNION ALL SELECT Number + 00.01 FROM NumberSequence WHERE Number < @end
I have a question on autogenerating numbers for a primary key field, "studyID," in a table€”but with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Here€™s the range of values we want our StudyIDs to be (this is to be our validation rule as well):
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC SET LKC.combo = lockCombo2 FROM [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
I have a table VisitLog pkey customer_id pkey user_id visit_date visit_note
Given both id's, I need a report that will list the visit interval. Assuming the rows are entered in ordered. the result should just display a single column with rows filled with date difference. Date Format in example below is dd/MM/yyyy
Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.
In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
I am getting an error when I try to insert a number typed in a text box control into a Sql database table column’s type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:
System.FormatException: Input string was not in a correct format. Line 428: CmdInsert.Parameters.Add(New SqlParameter("@Measure", SqlDbType.decimal, "Measure"))
A piece of the SP inside Sql server:
USE market26 GO ALTER PROC new_offer @Offer_id bigint, @Measure numeric(6,2) = null, …
What is wrong? Why it doesn’ t accept the number typed in my text box?
1. It looks like a 'ª' delimiters between fields. I need to pull out the second column in the above example it is - 9999999999. This is the place reference, so I need that in a separate field. 2. I need a way to compare the two fields and report back the change. So in the above example shows EAST. The After image shows WEST - I need the before image of EAST in one column and the WEST in another column.
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
I created one stored procedure to update the date difference in the table . in this table i have dt1,dt2,dt3... column and diff1,diff2... I wanted to find the difference between dt2 and dt1, and dt4 and dt3 and put it in separate column.
When I compiled the stored procedure, it did not show any error. But when i execute, it shows the error:
Conversion failed when converting datetime from character string.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER procedure [dbo].[autopost1] as begin declare inner int
CreatedDate                ID            Message  2015-05-29 7:00:00     AOOze           abc  2015-05-29 7:05:00     AOOze           start  2015-05-29 7:10:00     AOOze           pqy  2015-05-29 7:15:00     AOOze           stop  2015-05-29 7:20:00     AOOze           lmn  Â
 and so on following the series for every set of same ID with 5 entries for each ID
I need to Find Maximum interval time for each ID and for condition in given message (between message like Start and Stop) I used below query and it works fine
select Id, max(CreatedDate) AS 'MaxDate',min(CreatedDate) AS 'MinDate', DATEDIFF(second,min(CreatedDate),max(CreatedDate)) AS 'MaxResponseTimeinSeconds' from Table where Id in ( SELECT distinct Id from Table where Message like 'stop') group by Id
Above query displays max response time for ID A00ze as 20 minutes, but stop message has occured at 7.15. I would need to modify the query to return max response time as 15 min(from 7.00 to 7.15).
Difference of starttime(where A00ze id started) and stoptime(where stop string is found in message).
my table does not have primary key so i created a seperate index on each of the table.
I used the recommended tablediff utility and it works successfully. But its only show the difference of records in each table and does not copy rows from source to destination and destination to source table. I was expecting database1.dbo.table1 contains same records as in database1.dbo.table2.
C:Program FilesMicrosoft SQL Server90COM>tablediff /sourceserver kashif-pcs qlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsq lexpress /destinationdatabase CD /destinationtable table2 Microsoft (R) SQL Server Replication Diff Tool Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values: /sourceserver kashif-pcsqlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsqlexpress /destinationdatabase CD /destinationtable table2
Table [AB].[dbo].[table1] on kashif-pcsqlexpress and Table [CD].[dbo].[table2] on kashif-pcsqlexpress have 5 differences. Err Sno Src. Only 101 Src. Only 102 Dest. Only 103 Dest. Only 104 Dest. Only 105 The requested operation took 0.466767 seconds.
Can you write a short script for my problem, just like comparison of database1.dbo.table1 compares in database2.dbo.table2 and which ever records not present it should copy those and vice-versa.
It means Database1.dbo.table1 contains 5 records Database2.dbo.table2 contains 5 records
