MS SQL Server :: Deriving The Colunm

Want To Colunm

Mar 15, 2007

Afternoon all,

I have a colunm sName(nVarChar(255) which has first name and second name in it and is divended with a ",". Can somebody show me to put these in there own colunm sFirstName and sSecondName.

Very Kind regards.

View 1 Replies View Related

Updating Colunm

Mar 18, 2008

Hello

Can some body help me in performing this?

I want to move the data from Colunm Value to Colunm CPublicID whenever CCField = 'PublicID' as shown below in green color( Without using Cursor or While loops)

CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
101 XYZ PublicID Dimension Flag 103
101 XYZ PublicID Dimension Key 103
101 XYZ PublicID Dimension Flag 230
101 XYZ PublicID Dimension Key 230
101 XYZ PublicID Dimension Flag 5691
101 XYZ PublicID Dimension Key 5691

CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
103 XYZ PublicID Dimension Flag 103
103 XYZ PublicID Dimension Key 103
230 XYZ PublicID Dimension Flag 230
230 XYZ PublicID Dimension Key 230
5691 XYZ PublicID Dimension Flag 5691
5691 XYZ PublicID Dimension Key 5691

Also , I need to know the best practice to perform the below process. Which of them is advisable?

I have a table, say as above, which has lots of data, say 100,000 records. I need to look at each row and perform certain updates/ trasnformation to a colunm (Value). I aslo, need to hold the old colunm. I came up with two method

Process A) Clone the table. So I have table2 which is a copy of table1. Read the Table1, update / trasnfored the Table2

Table1 Colunm(Value) will have Old values and table 2 colunm(Value) will have transformed values

Process B) Clone the Colunm. So I have a colunm Value and its clone Value2. Read the table and value for Colunm Value and Update the Value2.

I am using process A. because I beleive , process B, reading and updating same table make sthe entire process slow.

Please advice on th ebest industrial practice of doing this.

Thanks in advance for all the help

View 5 Replies View Related

Current Date As A Colunm

Jun 15, 2006

hi

i wanted to crete an extra colunm when i retrive a set of rows...
the date colunm must show the current date eg 15/06/2006

currently im writing it as follows

select cost, '15/06/2006' as CDate
from prices

as you can see this is static date i need the date to be current whenever the query is done.

View 4 Replies View Related

Update A String In A Colunm

Apr 25, 2007

Afternoon all.
I have a table which has a column with has been inserting incorrect data, here is an example. Column name is description and table name is tblClientUsage:
[companyname]: 288bsl | | 13/03/2007
[companyname]: 288asl | | 08/03/2007
[companyname]: RES04sl | | 19/02/2004
[companyname]: RES01sl | | 19/02/2004
[companyname]: AAsl | | 31/12/2005
[companyname]: 652Asl | | 05/02/2007
[companyname]: GAZ1sl | | 20/03/2007
[companyname]: AAsl | | 31/12/1999
[companyname]: 363asl | | 20/01/2007
[companyname]: 288b1sl | | 02/11/2006
[companyname]: 288bsl | | 02/11/2006
Now what has been happening is that is has not been putting the company name in the column, just the text companyname. Now i have a table which has the company name and company id, i can easily join the two tables together but how can i replace just the companyname text in the description column with leaving the text after.

Any help would be great

View 3 Replies View Related

Selecting Colunm With A Space ''

May 22, 2006

When I submit a sql :

select * from view from column=''

the table is a view point to oracle table with ole db provider for oracle

In mssql2000, the result return rows which containing a space ' '

However, in mssql2005, the result return no rows .

Any advise on it?

View 6 Replies View Related

Deriving Unique Rows From Historical Data

Oct 25, 2005

My application is to capture employee locations.Whenever an employee arrives at a location (whether it is arriving forwork, or at one of the company's other sites) they scan the barcode ontheir employee badge. This writes a record to the tblTSCollected table(DDL and dummy data below).The application needs to be able to display to staff in a control roomthe CURRENT location of each employee.[color=blue]>From the data I've provided, this would be:[/color]EMPLOYEE ID LOCATION CODE963 VB002964 VB003966 VB003968 VB004977 VB001982 VB001Note that, for example, Employee 963 had formerly been at VB001 but wasmore recently logged in at VB002, so therefore the application is notconcerned with the earlier record.What would also be particularly useful would be the NUMBER of staff ateach location - viz.LOCATION CODE NUM STAFFVB001 2VB002 1VB003 2VB004 1Can anyone help?Many thanks in advanceEdwardNOTES ON DDL:THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODECANNOT BE RELIED UPON TO BE UNIQUE.THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOTBEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURSWHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FORUPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILLCONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL ASREQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID IsNULL)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOINSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB001', '2005-10-18 11:59:27.383', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB002', '2005-10-18 12:06:17.833', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB001', '2005-10-18 12:56:20.690', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)

View 4 Replies View Related

Deriving From A Column Date Type Into Format YyyyMMddhhmm

Nov 21, 2007

hi,

i have to derive a column from an existing column in my data source with a data type of DATE.
to illustrate my point lets assume that:

i have dt00 as my current column with its data type of date and now i have to create another column dt01 with data type int.
i need to format the date from dt00 to the following format yyyyMMddhhmm, where

yyyy - year
MM- moth in two digits (e.g: 03, 12)
dd - days in two digits (e.g: 03, 12)
hh - hours in two digits (e.g: 03, 12)
mm - minutes in two digits (e.g: 03, 12)

and allocate this value to this new column dt01.

could someone give me some clue on how to do this using the SCRIPT TASK?

many thanks,

nicolas

View 6 Replies View Related

SSIS Deriving Dt_str To Dt_dbtimestamp Including Milliseconds

Dec 27, 2007

Hi, I am trying to derive a column from:

mm/dd/yyyy hh:mms.fff to dt_dbtimestamp as:

(dt_dbtimestamp)(colum_name,1, 23) when I include the period and three digits for milliseconds the package fails if I leave it out it is successfull. I need to include milliseconds for my datawarehouse project. I tried many different ways and always with failure
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,23) fails
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,19) succeeds

Thanks

View 9 Replies View Related

Deriving A New Column From Another Derived Column

Jul 26, 2006

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a�

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a�

as the expression for new row b?

View 6 Replies View Related