Creating A New Column Of Differences (x[2]-x[1])

Apr 7, 2008

I have a table that looks like this:

id, x
1 9.5
2 10
3 11
...
n x

and what I would like is to update this table with the differences of values in the x column (x[2]-x[1]) where the first difference would just be defined as 0.

result table would look like this:

id, x, dx
1 9.5 0 (where dx[1] defined to be 0)
2 10 0.5 (where dx[2] = 10-9.5 = 0.5)
3 11 1.0 (where dx[3]= 11-10 = 1.0)
...

Any suggestions on the best way to do this?

View 9 Replies


ADVERTISEMENT

How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL

Apr 21, 2008


I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.

For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.

I am using SQL Server 2005. And the Two Servers are Linked Servers

What Script can i use to accomplish this task. Thanks






View 4 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Creating A Column.

Dec 27, 2006

Is there a way for me to create a column in SQL?
What Im looking to do is create a view with columns from a specific table. In addition to this I would like to append a column to the view which would contain data based on the data from a pre-existing column.
Table AColumn 1Column 2Column 3
View AColumn 1Column 2Column 3Column 4
Now here is the Row structureColumn 1   Column 2   Column 3   Column 4Test           Test          A               ONTest           Test          B               OFF
So if Column 3 contains "A" as its field data than Column 4 will contain "ON" and if Column 3 contains "B" than Column 4 will contain "OFF"
Is this possible?
TIA,
Stue.

View 4 Replies View Related

Creating A Composite Column

Jul 18, 2005

I have several tables where I have used the command
distinct first_name, last_name, dob
to unduplicate.

I was wondering if I could create a new column in my table that combines the existing records for first_name, last_name and dob?

View 4 Replies View Related

Creating Number Column

May 14, 2008

I have a ACTION column. i.e Its only disply SELL/BUY.

ACTION

BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY

How to create another column display like this

ACTION

BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1

Help me anyone...

View 9 Replies View Related

Creating Number Column

May 14, 2008

I have a ACTION column. i.e Its only disply SELL/BUY.

ACTION

BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY

How to create another column display like this

ACTION

BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1

Help me anyone...

View 2 Replies View Related

Creating A View With A New Column

Jan 31, 2014

I'm using SQL Server 2008 to solve the following exercise.

I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone

I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders

View 4 Replies View Related

Creating A New Column In A Table

Jan 25, 2006

I have created new columns fields in a table using EManager. However, I am wondering Which command do we use to create columns through the analyser??? Is it the same like the "Insert into"?

View 3 Replies View Related

Creating A Key Column For A Table

Jan 11, 2008

Hello,

I'm just using SSIS to build my first data warehouse. I've read quite a bit, but I'm stuck at the very first task: I need to assign a key column (just an int that starts at 1 and basically numbers the rows). What transformation do I use? I was going to use the derived column, but if that's the case, I don't know the script for the expression to do it. If I'm right, would someone mind supplying that also?

Thanks a lot,
Andy

View 4 Replies View Related

Why Do We Need To Collate One Column While Creating Table?

Apr 21, 2008

Hi,Good morning to All.
I have some doubt.I cannot do anything without knowing the reason why we are doing that particular thing.
Any way, my doubt is:
While creating table, in some places I see COLLATE clause.
For example, recently I have seen this.
CREATE TABLE dbo.OrderDetails(       OrderDetailID INT IDENTITY(1,1) NOT NULL,       ItemNumber VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_As NULL,       Qty INT NULL)ON [PRIMARY]
In the above table, why the Collate clause for ItemNumber column? What is the advantage with that clause?I have seen sql server help for this. I have found 1011 results for the query SELECT * FROM fn_helpcollations().Then I searched for SQL_Latin related collations using: SELECT * FROM fn_helpcollations() where name like 'SQL_Latin%'I have found 31 results.How can I know which clause i have to use for which type of query?
Can anybody please tell answers for all these doubts?
Thanks in advance.
Regards,Ashok kumar.

View 1 Replies View Related

Creating An Identity Column In A View

Mar 25, 2004

I need a view that will:
1) select the distinct values in a column (the easy part)
2) assign the equivalent of an identity column to each row containing the distinct value it returns.

For example TableA contains 25 records but one of the columns only contains three unique values (say, 'A','B', & 'C').

This is what I want my view to return:

1 A
2 B
3 C

In other words, I need the view to assign the 1, 2 and 3 to the individual rows.

Thanks in advance for any pointers on this one.

View 4 Replies View Related

T-SQL (SS2K8) :: Creating A Custom Column?

May 14, 2014

creating a custom column that will put a list of 5 values for each unique value(or in this case Employee).

So I have a Employee table were I pull a list of all active employees -

Example -

024Swanson, Ronrswanson@tv.com
026Donaughy, Jackjdonaughy@tv.com
028Scott, Michaelsmichael@tv.com

What I want to do is add a column that has 5 values and create a row for each value

I want it too look like this -

024Swanson, Ronrswanson@tv.com a
024Swanson, Ronrswanson@tv.com b
024Swanson, Ronrswanson@tv.com c
024Swanson, Ronrswanson@tv.com d
024Swanson, Ronrswanson@tv.com e

[Code] ....

Currently all my query looks like is this -

SELECT EmpID, LastFirst, Email
FROM dbo.EmpList
WHERE (Active = 1)

View 4 Replies View Related

Can I Increment The Column (c1+2) ---by Creating The Trigger

Mar 10, 2006

hi,i am a beginner to ms sql server2000i have a tablecreate table ddd (a int, b int)by table structure is a bnow when i enter a value in b column suppose '2' in column bbext time when i insert a value in the column a i have to get the valuein b as 3 is thi spossible with triggersinsert into gdg values (1,2)a b1 2insert into gdg (a) values(2)a b2 3----------------> i have to get this 3 automaticallyis there any method to get thispls help mesatish

View 1 Replies View Related

Creating Primary Key On Nullable Column

Feb 29, 2008

Hello,

I am trying to recreate a primary key that I dropped in a table....

I'm using a statement like

ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED

(

num,
store
)


But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..

Does anyone know how to get it to use that column to create a primary key after the initial table creation?

Here is the error i get

Cannot define PRIMARY KEY constraint on nullable column in table


Thanks a lot

View 5 Replies View Related

Creating A Child Row With Two Parents? (in One Column)

Sep 29, 2006

Hi!

How can I make a relation that would let me relate an employee to two departments?

There is a table with emloyees and each one works in a department and there is a foreign key relation in the child (employees) table to the parent (departments) table. So can an employee work in two departments? (it's not impossible to imagine, is it?) And how would I retrieve all the employees of a department?

Or in other words, if I were working on some kind of software for my local supermarket and they need to keep track of all bills they ever gave out. I would make this products table and this bills table. Then I would create a foreign key column in the products table and fill it with bill_ids from the bills table and make retrieving bill items a simple getchildrow[](bill_row). Note that this is only an analogy, I need it done this way. How? (using C# express 2005, and SQL express and DataSets)

View 12 Replies View Related

Does Column Order Matter When Creating A Table?

Mar 16, 2001

Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?

View 1 Replies View Related

Problem Creating View With DISTINCT Column

Oct 5, 2005

Hi Guys

I'm trying to create a view in SQL Server to display only one copy of all records in a table, where some of the records are exact matches to other records (except for an ID autoincrement field)

I have the following code that displays distinct titles:

sql Code:






Original
- sql Code




SELECT DISTINCT DocTitle
FROM dbo.TBL_TABLE_NAME






SELECT DISTINCT DocTitleFROM         dbo.TBL_TABLE_NAME



But when i use this code (adding one field to query), it displays all the duplicate records also, which I do not want:

sql Code:






Original
- sql Code




SELECT DISTINCT DocTitle, ID
FROM dbo.TBL_TABLE_NAME






SELECT DISTINCT DocTitle, IDFROM         dbo.TBL_TABLE_NAME




I want to get the distinct results for DocTitle, with 5 other columns added to the query so they can be displayed in a web page list.
The query I want is something like this, but I can not get it to work:

sql Code:






Original
- sql Code




SELECT DISTINCT DocTitle, ID, FirstName, LastName, Company, DocDescription, DocFile
FROM dbo.TBL_TABLE_NAME
ORDER BY DocTitle






SELECT DISTINCT DocTitle, ID, FirstName, LastName, Company, DocDescription, DocFileFROM         dbo.TBL_TABLE_NAMEORDER BY DocTitle



I know this query works from an MS Access DB, but I need it to work from SQL Server.

I am using SQL Server 2000

Any help is greatly appreciated.

Alex

View 1 Replies View Related

Creating Non Clustered Index On DateTime Column

Dec 24, 2014

I have 5 million rows of table, and going to create Non Clustered Index for Datetime values column. Creating Non clustered Index on Datetime value column will affect performance or not.

View 4 Replies View Related

Creating A Primary Key Column In SQL Server 2005

Feb 1, 2008

I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?

One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.

With these information, can some body help me in deciding this?

View 2 Replies View Related

Creating A New Numbered Column As Primary Key In A Table

Jul 23, 2005

This is a fairly simple question, but what is the easiest way to:create a new numbered column (where value is simply the row number) inan existing table and setting it as a primary key?

View 1 Replies View Related

Creating Trigger For A Single Column/Field?

Apr 15, 2008



Hi all,

My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks

Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()

BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

View 3 Replies View Related

Creating A New Column By Shifting A Timestamp At Least 5 Seconds Later

Apr 8, 2008

I have a table that looks like this:

[Timestamp] [P]
2008-04-06 17:30:00 119
2008-04-06 17:30:03 120
2008-04-06 17:30:07 119
2008-04-06 17:30:11 118
2008-04-06 17:30:12 129
2008-04-06 17:30:13 125

What I'm after is to create a new [dtP] column where the [dtP] is the [P] value at least 5 seconds later. I would assume I should use the dateadd() function but I've tried [Timestamp] = dateadd(ss,5,[Timestamp]) in my WHERE clause and to no avail. Any suggestions on the best way to proceed?

Desired Result:

[Timestamp] [P] [dtP]
2008-04-06 17:30:00 119 120
2008-04-06 17:30:03 120 121
2008-04-06 17:30:07 121 122
2008-04-06 17:30:11 122 125
2008-04-06 17:30:12 128 125
2008-04-06 17:30:13 129 etc...
2008-04-06 17:30:14 125

View 2 Replies View Related

SPROC Probs Creating A Non-identity Number Column

Jun 26, 2006

Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).

The Jist of my problem... When a user first inserts six pictures, he gets:

|1|
|2|
|3|
|4|
|5|
|6|

All is good. But, say he deletes picture |3|. Now the list order looks like this:

|1|
|2|
<- |3| is removed
|4|
|5|
|6|

And, then he inserts two more pictures, now he his this:

|1|
|2|

|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|

What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.

I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:


Code:


ALTER PROCEDURE dbo.sp_NewPersonalPic

(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)

AS

BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END

BEGIN
SET @photo_number = 1
SELECT

@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END

BEGIN



My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:

photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200

If |3| is removed above, the numbers are reordered based on the time of entry sequence.

Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.

Thanks and sorry for the verbose post.

View 5 Replies View Related

SQL 2012 :: How To Prepend Value To Existing Value While Creating A Temp Column

Dec 5, 2014

I am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:

SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name]
FROM SkywardUserProfiles

I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:

domainName=CONCAT('domain',User Name);

View 2 Replies View Related

Transact SQL :: ALTER Vs UPDATE When Creating A New Column With Default Value

May 8, 2015

I have a table with ~30M records. I'm trying to add a column to the existing table with default value and have noticed following ... When using alter with default value- (Executes more than 45 min and killed forcefully)

ex:  
ALTER TABLE dbo.Table_X Add is_Active BIT CONSTRAINT DF_Table_X_is_Active DEFAULT 'FALSE' NOT NULL
GO

When using update command after adding column with alter (without default value) it completes is 5 min.

ex:  
ALTER TABLE dbo.Table_X Add is_Active BIT NULL
GO
UPDATE Table_X SET is_Active = 0 WHERE is_Active IS NULL
GO

Why there is so much of difference in execution times ? I was just trying to understand internal behavior of the SQL in these two scenarios. 

View 4 Replies View Related

Creating A Unique Constarint On A Multiple Null Column

Aug 3, 2007



HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

View 1 Replies View Related

Data Warehousing :: Creating A Table With Column Store Index?

Sep 26, 2015

I am trying to create a sample table in the Azure SQL  Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.

CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL

[Code] ....

what's the correct syntax

View 2 Replies View Related

Transact SQL :: Creating Dummy Headers (column Names) With Union All

May 19, 2015

I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....

COL 1     COL2      COL3       COL4 etc.                         BEH                             BIG       BPL etc.

*************************************   INTAKT       DEFEKT       INTAKT DEFEKT          INTAKT

*************************************       B                E                 B         E                    B

I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?

View 4 Replies View Related

Reporting Services :: Creating A Child Column Group In A Table?

Nov 9, 2015

I have the following column values (which represent the hours in a day) - 

I want to be able to create a "child group" which would create 15 minute interval values under each hour, like - 

0   15   30    45    60 

So these would repeat under, each "hour" column value.

How can I do this ?

View 7 Replies View Related

Integration Services :: Creating A Column With Total Number Of Records In Dataset For Each Row?

Aug 17, 2015

I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.

My dataset:

A  20 abc
B 24 mnp
c 44 apq

Now I need to add another column within my transformation before I store the result set to destination like this:

A 20 abc 3
b 24 mnp 3
c 44 apq 3

Here. new column gives count of total rows in our dataset which was 3.

How can I achieve this? Can I use derive column to this?

View 6 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???





View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???


thanks,

Ran Kizi

View 3 Replies View Related







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