Create A Column Based On A Value In Created Column

Jun 20, 2014

I am trying to add a column to query based on the value of another column in the query.

I first tried creating a calculated field in SSRS 2008 with this statement:

=IIF(Fields!ChargeableFlag.Value=1,Fields!Negamt.Value,0)

The report runs but I get a "#ERROR" when I place the field on the report.

I next tried creating a new column with the SQL statement:

SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours,
Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID,
Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =

Case
When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
When TaskRule.ChargeableFlag = 1 Then 'Billable'

[Code] .....

This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error: "Invalid column name 'Negamt'."

View 2 Replies


ADVERTISEMENT

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

Jul 28, 2015

I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H.  I want to create a new column called "status" based on the values of "Code".

Code:

A
B
C
D
E
F
G
H

If A,C,E,G then "status" = "Active" else if  B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".

View 4 Replies View Related

SQL Server 2012 :: Create Column Based On A Value In A Flatfile

Nov 10, 2014

I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...

I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?

Example--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3

Description code
Test1 1
Test2 2
Test3 3

View 6 Replies View Related

Column Based On Other Column (short Name Based On Name), When To Do The Transformation?

Oct 18, 2007

Hi!

I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:


Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName

Create a derived column in the SSIS flow which does the same thing

Create the ProductShortName column as a computed column which uses substring on ProductName

Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated

Create a named calculation in the table in the Analysis Services project's data source view

Create a named query in the Analysis Services project's data source view

I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?

Thanks!

View 3 Replies View Related

Master Data Services :: Filter Column Based On Other Column In Same Entity

May 12, 2015

Using MDS 2012: I have an entity "XYZ_Entity".  In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".

While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.

Is it possible, if yes then how?

View 2 Replies View Related

Query To Find A Value In Column B Based On An Aggregate Function On Column A?

Jul 20, 2005

Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy

View 5 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related

Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?

Nov 4, 2015

#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):

View 6 Replies View Related

Update Column Value In Whole Database (based On Column Value)?

Aug 27, 2015

How to Update Column Value in the whole data base (based on Column Value)?

View 2 Replies View Related

SUM Of One Column To Multiple Columns Based On Another Column Value

Oct 14, 2015

LeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays

1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10

I have the above table (LeaveEntitlement) which has the above columns.

What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.

For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual

The result should be shown in new column name AnnualLeave (20)

sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick

The result should be shown in new column name SickLeave (10)

Same all leave Types

The table should be shown as the below after executing the query

LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave

1 1 1 20 0
2 1 2 0 10
3 2 2 0 20

is it possible in sql server

View 8 Replies View Related

How Do Create Table From Excel (based On Excel Column Name) And Import Data From It?

Jun 14, 2006

l've the following situation,

l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.

So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.


Thanks.

Regards,
Yong Boon, Lim


p/s : The excel header is at the row 7.

View 3 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

Create Multi Column View From Single Column Data

Jan 9, 2008

I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

data_table
product_code month value
350 1 10
350 2 20
350 3 30

product_table
product_code profit_center
350 4520

result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 20 30

My current query gives the following result
result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30

Any direction toward a solution would be appreciated. Am using SS2005.

View 5 Replies View Related

DB Design :: Create Two Column With Math Function In One Column?

Oct 5, 2015

How Can I Create Two Column with Math Function In one Column  Like Below. 

Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key, 
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total 
)

View 5 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

Update Column Value Based On Value In Another Column?

Jan 29, 2008

Hi.

I'm writing a web application with VS2005 andSQL Server 2005 express edition.

I have an SQL table:

Table name:
statistics

Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage

When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:

UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)

This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)

Any help would be appreciated.

View 2 Replies View Related

How To Create A New Column And Insert Values Into The New Column

Mar 3, 2008

Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.

Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)





PartsNum

MiniPartsCL

NewMiniPartsCL







1

K

DK




1

K

K




1

Q

Q




0

L

L




0

L

LC




0

D

G




0

S

S



I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.

The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.

For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).

Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..

View 3 Replies View Related

Using Newly Created Column For Doing Further Calculation

Apr 12, 2015

I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1

First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.

View 2 Replies View Related

Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1

First 3 Columns are available within the Original Table_1

The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 4 Replies View Related

Sql Challenge : List Created From Column Data.

Apr 15, 2008

Lets say I have a table called MyData.

MyData has two columns, ID (int), and Value (varchar(x) )

Lets say the data in the table looks like this:

ID Value
- -----------
1 A
1 B
1 C
2 A
2 D
3 E
3 F
3 A
3 G

now, is there any way to construct a select statement from this data that will return a dataset that looks like this?

ID ALL_VALUES
- --------------------
1 A,B,C
2 A,D
3 A,E,F,G


In other words, is there a way to combine all the Value field values into one field for each ID?



View 8 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

Possible To Avoid The FILLING Of GAP (created By Deleting A Record) In The Identity Column ?

Jun 18, 2007

Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy

View 2 Replies View Related

SQL Server 2014 :: Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 8 Replies View Related

Power Pivot :: Relationship Cannot Be Created Because Each Column Contains Duplicate Values

Nov 15, 2011

I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".

View 15 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

Table , Column Created, Modified Date In Sql Server 2000 Or 2005

Oct 10, 2007



Hi

Any one please tell me is there any possible way to identify the table modified date.

I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates.
Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.

I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date
can any one help

View 8 Replies View Related

Updating Price Column Based On Another Price Column?

Aug 13, 2013

My dataset looks like this:

IDBuyDotComPriceCompanyIDShadowOf
AB CIRCLE PRO199.99203
AB CIRCLE PRO-b2199.99203AB CIRCLE PRO
AB CIRCLE PRO-TB249.99344AB CIRCLE PRO
AB CIRCLE PRO-TB-S10344AB CIRCLE PRO

I need to update the price of an item where the CompanyID is 344 and the ShadowOf is not null. The value in ShadowOf is the same as the ID that I want to get the BuyDotComPrice for. It should be simple, but I keep getting errors.

I use Microsoft SQL 2008

View 1 Replies View Related

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?

Jun 18, 2007

Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,



View 6 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

Replace Value Of ID Based On Name Column

Aug 14, 2012

I have two tables with the following structure:

Table 1
---------
id | Name
---------
1 | John
2 | Debbie
3 | Kim
4 | Mary

Table 2
---------
id | Name
---------
654 | John
415 | Debbie
68 | Kim
289 | Mary

Table 2 id's values are all messed up and I need to replace them so that they look exactly like in Table one, my guess is that query will have to replace value of "Id" based on the "Name" column, I hope I made myself clear.

View 6 Replies View Related

Identity PK Column Based On Other PK...please Help

Apr 30, 2008

I have this table where there is tow primary keys ,one for Year and the other for DocNumber.

In this table the DocNumber takes a number based on each year,for example in year 2008 the DocNumber should start counting from 1 and increment by one for every record,when the year changes to 2009 the DocNumber should start again from 1 until and seed by one again.

Those columns are PKs because there must be one doc Number for each year and they can not be null .

Is there any simple way to approach this in Sql Server 2005 ,or do I have to work on trigger to do so...???

Any ideas will be really appreciated.

Eng.Osman Hamad
http://arabic.aspose.com

View 5 Replies View Related

Column Value Based On A Condition

Nov 20, 2013

I have a column with serial numbers 1, 2,3,4,....etc...I want to have P1 for first 4 serial numbers to be displayed in a separate column beside Serial number column and for the next four serial numbers (5,6,7,8) it should be P2 and like that I want till P13 and for next again it should start with P1(I mean after P13 it should give again P1 instead of P14) and continue the same process.

Column name of Serial number is [S.No]
table name for example is #temp1

View 3 Replies View Related







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