Data Field Question

Jun 9, 2008

Are you able to take Case statments that have AS (Field name) and use those to subtract after one another under the data tabin sql?

For example i created 3 case statments and they have 3 columns

SUM(CASE WHEN action = 'b' AND glsrc IN ('RO', 'RR') THEN glamt ELSE 0000000.00 END) AS RentalBilling,
SUM(CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END) AS Depreciation,
SUM(CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END)
AS Repair

Under the data table I want to Subtract RentalBilling, Depreciation, and Repairs and then have that out come posted in a new column called totals.

View 9 Replies


ADVERTISEMENT

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field.  DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

How To Matrix: Conditional Data-field Bg-color When Field Is Subtotal

Mar 25, 2008

How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???

View 3 Replies View Related

SQL Server 2008 :: Selecting Field That Contains Data From Another Field

May 28, 2015

We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.

ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY

what I need to do is identify where the Brand is in the Description field ...

I have tried ;

select * from Table
where Description Like Brand

not very successful.

View 3 Replies View Related

Importing Of Varchar Field Data In Number Field

Dec 5, 2007

i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?


jto it

View 5 Replies View Related

Grouping By One Field And Combine Data Of Another Field In SSRS

Apr 2, 2008

Hi all experters,

Please suggest how to build the report in below case:

Raw data:






ID
Member
Functions

1
Alan
A

1
Alan
B

2
Tom
A

2
Tom
B

2
Tom
C

3
Mary
D

3
Mary
E


Report Shows:






ID
Member
Functions

1
Alan
A,B

2
Tom
A,B,C

3
Mary
D,E


I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.

Any good suggestion?

Thanks in advance,
Steve Wang
2008/4/2

View 6 Replies View Related

Update SQL Field With Stripped Data From Other Field

May 12, 2006

Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin

View 1 Replies View Related

How To Convert To Regular Text, Data Stored In Image Data Type Field ????

Jul 20, 2005

Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD

View 1 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

How To Set A Key For A Field In A Data Table

Aug 10, 2006

In my SQL Server 2005 database I have created a table with several data fields. One of the fields is designated as a primary key. And another one is just a key.
I can easily set the first field as the primary key, but am not able to set the second field as being just a key field.
I perform these steps to set the primary key.
(1) Right click on the table name and then select 'Open Table Definition'
(2) I right click on the field in my data table and select 'Select Primary Key'
As I mentioned previously, to set another field up as just a key field, I am not able to do this. My choices are the following when I right click on the data field to be designated as the key field:
Set Primary Key, Insert Column, Delete Column,Relationships,Indexes/Keys,Full Text Index,XML Indexes, Check constraints,Properties.
None of these choices will allow me to set the field up as a key field.
Someone please help me out with this problem.

View 1 Replies View Related

Accessing Data In One Field

Jun 20, 2008

There has to be a better way (less code) to grab the data in one field than:string sql0 = "Select PSI from agentRate where agent='" + agent + "'";
SqlCommand command0 = new SqlCommand(sql0, conn2);SqlDataReader dr0 = command0.ExecuteReader();
string PSI="";while (dr0.Read())
{PSI = (string)dr0["PSI"];
}
dr0.Close();
 
Any Thoughts?

View 4 Replies View Related

Separate Data In One Field

Feb 28, 2002

I recieved a SQL Server table that was supposed to have just the firstname in a field, but actually has firstname and middle name.
Example David Michael
Carol Anne
Is there a way in a query to look for the blank space and separate the names?

View 2 Replies View Related

Can't Enter Data In Field

May 30, 2007

I have a field in my table that is varchar:1500:null, but I cannot enter any more that 994 characters. any ideas why?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Quotes]
GO

CREATE TABLE [dbo].[Quotes] (
[QuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[DateAdded] [datetime] NULL ,
[CustomerID] [int] NOT NULL ,
[ProductName] [varchar] (100) NULL ,
[RepID] [int] NULL ,
[QuoteNumber] [varchar] (30) NULL ,
[QuoteDate] [datetime] NULL ,
[QuoteTerm] [varchar] (10) NULL ,
[QuoteFOB] [varchar] (15) NULL ,
[QuoteNAIRep] [varchar] (30) NULL ,
[QuoteExpiration] [datetime] NULL ,
[Note] [varchar] (700) NULL ,
[Comment] [varchar] (1500) NULL ,
[OrderRequirement] [varchar] (1000) NULL ,
[Status] [varchar] (1) NULL ,
[DateClosed] [datetime] NULL ,
[ProductType] [varchar] (30) NULL ,
[ImageID] [int] NULL ,
[CloseMonth] [int] NULL ,
[CloseYear] [int] NULL ,
[ClosePercent] [int] NULL ,
[Segment] [varchar] (50) NULL ,
[AccountID] [uniqueidentifier] NULL ,
[ReplacedQuoteID] [int] NULL ,
[Lead] [varchar] (80) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Quotes] ADD
CONSTRAINT [DF_Quotes_Closed] DEFAULT ('N') FOR [Status]
GO

CREATE INDEX [idx_RepID] ON [dbo].[Quotes]([RepID]) ON [PRIMARY]
GO

CREATE INDEX [indx_CustomerID] ON [dbo].[Quotes]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [indx_QuoteID] ON [dbo].[Quotes]([QuoteID]) ON [PRIMARY]
GO

CREATE INDEX [indx_Status] ON [dbo].[Quotes]([Status]) ON [PRIMARY]
GO



[Comment] [varchar] (1500) NULL , is where I can't enter more than 994 characters.

note - I know very little about SQL, I just had the responsibility placed on me at my job. If this is not enough information to go on to throw an idea at me please let me know what else you need.

Thanks in advance

View 5 Replies View Related

Sorting Out Data In A Field

Apr 19, 2004

Hi there,

I got one prob and i need help to solve it.
I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each.
Example:

ASP_Tin No
----------------

ss24fg
ss98jk
tp98ij
yh88ij
yh67tr
tp34ed
fg98bv
fg56sl
..........

and i need to sort it out into like this

ss tp yh fg
------------- ------------- ---------------- ---------------
ss24fg tp98ij yh88ij fg98bv
ss98jk tp34ed yh67tr fg56sl

i need to do it in sql statement which is in stored procedure
hope somebody can help me
Thanx

View 2 Replies View Related

Import Data Into Field

Apr 11, 2008

Hi,

I am new here, and looking for some help with SQL import.

I am using SQL Server 2000.

Problem: I have a table called 'People', with a field that I wish to populate inside of it (Town).
I already have 500 records in the table.

I have a seperate csv file containing the primary key record (ID) information, and the information that I wish to put into the field (Town).

How can I import the information into the field 'Town' by inserting the correct information next to the ID in the CSV file (There are some that I do not have to import, so I cannot just insert straight in).

Best Regards

View 5 Replies View Related

Combining Data Into One Field

Apr 14, 2008

Hello,
I'm not sure if this is possible, but here is what I'm trying to do:

I have a table that contains for example the following:

SPECIES PERCENT
s 19
P 25
B 32
D 24

I want to end up with a single field, titled S_P_B_D with the values 19_25_32_24.

Possible?
Thanks in advance.
Amber

View 16 Replies View Related

Select Only One Data In A Field

Jun 9, 2008

Hi there,
Need help..
I have a table name Changekitlist and have fields name CKNumber,ck_ID,ck_Name, etc.. Now, I plan to see each CKNumber one at a time so that I can put it in an array which I'll use in VB6 program.

However, If I use the command: "SELECT CKNumber from Changekitlist"
it displays all the CKNumber list that I have (CK001 to CK500) and I cannot store it in an array properly because it display all CKNumber at once and store all the 500 CKNumbers at once in the array.

What will I do so I can display one CK Number at a time, store in an array and loop so that I'll get the next CKNumber and store it again.. Need your advice please. Thanks!

View 12 Replies View Related

Update Data In A Field

Jul 20, 2005

Hi,how can i update the data in a field like that:organizationamesurenamezipcityI want to update the content from "organization"into "eworganization" followed by "amesurenamezipcity"yoursmarvin

View 1 Replies View Related

DTS To Add A Field On Imported Data

Jul 20, 2005

I am in the process of importing data that is in a text format to thesql server, I want to add couple of fields and insert general data inthe fields added, this data is going to be similar to all the recordsimported. Your help in this regard will be greatly appreciated.

View 1 Replies View Related

There Is No Data For The Field At Position XX

Jan 25, 2008

I am so frustrated, I don't know what to do. I have spoken to everyone that I know on how to explain this error message, but my supervisor wants to dispute every thing I tell him.

Could someone PLEASE explain the following error message and how I can go about finding what fields that it's talking about?

e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 15., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 15.

I have two environments (2 seperate companies on seperate servers). In Environment #1 (referred to as Env1 from this point on) that runs the report and generates a few of these errors (10 for an example). In Environment #2, the same report only modified SLIGHTLY for the other company, runs the report and generates 50 errors. I'd be more than happy to post the log here if someone needs it.

Someone, please, save me from logfile hell. I have told this supervisor that "No data for the field" pretty much means that there was NO DATA RETURNED. We know for a FACT that these two environments are completely different. One runs on real servers and the other is running on partial real servers and virtual servers. We have identified several differences in the configurations, but he wants me to nail down EXACTLY what is causing this error. Please remember, "No Data" is not a good enough response for this person.

Please, for the love of all that is Chocolate, HELP!!!!!!!!!!!!!!!

Thanks in advance,

Jim Evans
Microsoft Certified Application Developer

View 6 Replies View Related

T-SQL Keyword 'AS' As Name Of Data Field

Dec 18, 2007

Hello forum,

unfortunately i have a table with a column named 'AS'. I want to insert data into this table using a stored procedure but i get the error that the syntax is wrong near to the 'AS' keyword.

I tried to build a query string and execute it with the EXEC command. But that doesn't help. Same error.

I hope you understood my problem,

Thanks in advance

alex

View 3 Replies View Related

Can You Reference The Data In Another Field?

Dec 5, 2007


Is it possible to reference the data in another field within the same table?

Let's say that one column has a calculation, and a second column has a different calculation. Can the third column find the difference between the first two columns?

(I'm wondering if this is possible to do within the Layout View, not from the Data View)


Thank you!

View 3 Replies View Related

Add Another Field To The Data Flow

Apr 15, 2008



I have an OLE DB source tool that gathers most of my data from the OLTP database. I need to do an aggregation on data already in the OLAP table so I pop a OLE DB tool and add the following query:
select Count(Add_Remove) AS Total
FROM factInventory
WHERE DateKey <=?

How do I add the field Total to the datastream?

View 18 Replies View Related

Transact SQL :: How To Get Value Of A Particular Field From XML Data

Nov 6, 2015

We are running the following querry which insert data row from an XML source

INSERT INTO End_ReelProcessValue
(LineId
,ReelHist_id
,Variable_tagName
,ProcessMeasure
)

[Code] ....

From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :

[Variable_Tagname] CHAR(50) '@Name',
[ProcessMeasure] NVARCHAR(254) '@Value'

What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.

How can I retrieve that particular record ?

View 2 Replies View Related

Duration Data Field Type

Mar 17, 2008

Hi, I want to store a time duration such as 1:30 (mm:ss), 1:00, or 1:23 in a SQL 2005 database.  What is the best data field type for this data?  DateTime or TimeStamp?  Thanks 

View 2 Replies View Related

Retrieving Data Based On Value In A Field

May 29, 2008

Hi I hop i make sense with this, i have a page where you can update a products price based on the year, so for instance some products will still have prices set in 2007, while some may have prices which will be set in 2008, what i am trying to do is if the price has been set in 2008 it shows this value. The fields in the table which control the values are Rate(controls 2007), and Rate2007Period2. What i am trying to do is if Rate2007Period2 is null or 0, then display the 2007 rate, and if Rate2007Period2 is greater than 0 then display that value. Here is the view which displays the rates;
SELECT     dbo.tblRateSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblRateSchedule.DESCRIPTION, dbo.tblRateSchedule.SWT,                       dbo.tblRateSchedule.Rate, dbo.tblRateSchedule.Tstamp, dbo.tblRateSchedule.RateSchedule_ID, dbo.tblRateSchedule.RateScheduleUnit_ID,                       dbo.tblRateSchedule.RateScheduleType_ID, dbo.tblRateSchedule.WorkType_ID, dbo.tblRateScheduleUnit.Unit,                       dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblRateSchedule.Work_Type_Description AS Expr1,                       dbo.tblRateSchedule.Rate_Active, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2007Period1,                       dbo.tblHistoricalRateSchedule.Rate2007Period2FROM         dbo.tblRateSchedule INNER JOIN                      dbo.tblHistoricalRateSchedule ON dbo.tblRateSchedule.MA_Code = dbo.tblHistoricalRateSchedule.MA_Code LEFT OUTER JOIN                      dbo.tblRateScheduleType ON dbo.tblRateSchedule.RateScheduleType_ID = dbo.tblRateScheduleType.RateScheduleType_ID LEFT OUTER JOIN                      dbo.tblWorkType ON dbo.tblRateSchedule.WorkType_ID = dbo.tblWorkType.WorkType_ID LEFT OUTER JOIN                      dbo.tblRateScheduleUnit ON dbo.tblRateSchedule.RateScheduleUnit_ID = dbo.tblRateScheduleUnit.RateScheduleUnit_IDWHERE     (dbo.tblRateSchedule.Rate_Active = '1')

View 6 Replies View Related

Inserting Data From A DB Into A Text Field

Jun 2, 2008

Hello Everyone,
 
I have looked and looked for any information on how to insert data from a db into a text field using a stored procedure.  If anyone has any helpful links or suggestions, my sanity would greatly appreciate the help.
 
Thank you

View 1 Replies View Related

Deafult Data For Image Field

Apr 19, 2004

Hi,

I am storing my upoaded images in sql server. However, if the user does not upload an image, a broken link is shown in the page.
All that shows up in the DB field is <Binary>.

Can we set this field to a default value?
Is it possible to view this binary data.

I would like a default blank gif to be returned if no image is uploaded.

Regards,
JB

View 3 Replies View Related

Retriving Data From SQL Text Field

Dec 6, 2004

I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?

View 2 Replies View Related

Importing Data / Autonumber Field?

Sep 21, 2006

Hello--

I'm importing data from an Access table and, of course, one field is a primary key. This field needs to be an autonumber. The problem is the data I'm importing isn't sequential.

Can I import the data, then alter the table to make the column increment or is there a way to create the table and make the field increment, but import non-sequential data?

HELP!

Thanks,
grimey

View 2 Replies View Related







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