.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

how to update a column with some rules in it (base on other columns)

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server
 

Hello Sir,

i have table named mytable1 it has values as below,  with composite primarykey (CKCOL1,CKCOL2,CKCOL3)
how to update finalresult values as below math, i tried it with different groupings

CKCOL1 CKCOL2, CKCOL3, SalesID,   Team1,    Team2,     Team3,     counts,   units,      ordertotal,     otheramount,  FinalResult
DDN 175 ETQ NULL 10:40 MON JUN28 NULL 1 0 2814 NULL 
DDN 175 ETQ NULL 10:40 TUE JUN29 NULL 1 0 4938 NULL
DDN 175 ETQ NULL 10:40 MON JUL05 NULL 1 0 2814 NULL
DDN 175 ETQ 31566 10:40 NULL JUL13 9 NULL 13998 3432 NULL

DDN 175 TVQ NULL 1:40 SUN JUL25 NULL 2 0 7831 NULL
DDN 175 TVQ NULL 1:40 SAT JUL24 NULL 2 0 2168 NULL
DDN 175 TVQ 40546160 1:40 MON JUN1 3 NULL 5000 NULL NULL
DDN 175 TVQ 40546245 1:40 TUE AUG1 1 NULL


View Complete Post


More Related Resource Links

conditional update a column base on multiple result

  
Hi, I have a requirement that to conditional update a column base on the table result within a single update statement.     declare @TableStageStatus table ([OldTable] [varchar](100) NULL, [OldKey] [varchar](100) NULL, [StatusCode] [varchar](10) NULL) declare @Table table ([Key] [varchar](100) NULL, [row_stus_cd] [varchar](10) NULL) INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','I') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','I') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','E') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','I') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'2','E') INSERT INTO @TableStageStatus ([OldTable],[OldKey],[StatusCode]) VALUES ('a' ,'1','E') INSERT INTO @Table ([Key],[row_stus_cd]) VALUES ('1',null) INSERT INTO @Table ([Key],[row_stus_cd]) VALUES ('2',null) UPDATE A SET A.row_stus_cd = case when row_stus_cd is null then B.StatusCode when B.StatusCode='E' then 'E' else B.StatusCode end FROM @Table A INNER JOIN @TableStageStatus B ON A.[Key]=B.OldKey and B.OldTable='a' WHERE B.StatusCode in ('E','I') select * from @Tab

WPF: how can ListBox display three columns: first and thrid column width base on the longest content

  

We try to use ListBox to display three columns: so the first and last column width will depend on the longest content in that column to decide and second column take the rest of space as its width.

How can we use ListBox to achive that?

should we try with the ListView? thx!


wss2.0 update/delete/hide lookup column that does not display any values

  

Hi All,

I have a document library that contains a Category column that is a lookup field. This is a default column that is a required field when uploading documents to the document library. The Category column is empty and I am unable to amend, hide, make it not required or delete it.

I have gone to Modify settings and columns -> clicked on the Category field to edit, but there is no option to amend the content or delete it. I am only able to amend the Column name and Description.

Since then, I have amended the column name to eg. Category1 and created a new Category field as a lookup and linked it to the correct list.

The problem I am facing now, is that I cannot hide, delete or make the Category1 (old Category) field NOT required. Either I would like to update the original field to display the correct values or alternately hide, delete or make the column not required.

Please help.


DataGridView>Edit Columns> Column Read Only property is unwilling to change

  
Hello, I am facing the following mystery: When changing the read only property of a column to false and reopening the "edit columns" window, it mysteriously turns back to true. Even when closing the designer and changing it in the designer code, the resulting execution does not allow edit, as well as when re-opening the designer you see true again.             this.numberDataGridViewTextBoxColumn1.DataPropertyName = "Number";             this.numberDataGridViewTextBoxColumn1.HeaderText = "Number";             this.numberDataGridViewTextBoxColumn1.Name = "numberDataGridViewTextBoxColumn1";             this.numberDataGridViewTextBoxColumn1.ReadOnly = true;//False is required We thought it might be related to data binding. rebinding the grid did not help as well. The is no other place in the code where it changes. Please advice, Thanks

Copy a column with update and select

  
Hi, I am using SQL Compact 3.5 I am trying to copy the values of a column called "CODIGO" from one table to another table. I mean, I want to copy the column "CODIGO"  from table  "DATOS_ACADEMICOS" to table "ALUMNOS". I use this code: UPDATE ALUMNOS  SET CODIGO=(SELECT DATOS_ACADEMICOS.CODIGO FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   But I get an error in the SELECT statment: Major error 0x80040E14, Minor error 25501 [ Token line number = 2,Token line offset = 13,Token in error = SELECT ]   I also tried this code with the same error: UPDATE ALUMNOS  SET CODIGO = (SELECT DATOS_ACADEMICOS.CODIGO             FROM DATOS_ACADEMICOS             WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID) WHERE EXISTS (SELECT 1 FROM DATOS_ACADEMICOS WHERE DATOS_ACADEMICOS.ALUMNO_ID=ALUMNOS.ID)   How can I copy from one column to another in a SQL sentence? Thanks    

split a column into multiple columns

  
Hi i have a column with the following values from my source FILENAME ComplianceStatus_ER_06022010.xml ComplianceStatus_AZER_07052010.xml ComplianceStatus_GEL_06022010.xml ComplianceStatus_AFF_05022010.xml ComplianceStatus_Good_2010.xml   I want to split the date into different columns as follows using ssis   FILENAME                            CLIENT      DATE ComplianceStatus_ER_06022010.xml    ER          06022010 ComplianceStatus_AZER_07052010.xml  AZER        07052010 ComplianceStatus_GEL_06022010.xml   GEL         06022010 ComplianceStatus_AFF_05022010.xml   AFF         05022010 ComplianceStatus_Good_2010.xml      Good        2010  

Error when creating PK to a column which has dupliacte columns..

  
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'table name' and the index name 'PK__column__118068EB'. The duplicate key value is (D1).

Name Column Not Included in List Columns

  
I created a custom content type derived from the Folder content type.  The “Name” column is included in the custom content type columns.  I add the custom content type to a custom list that allows multiple content types and the "Name" column from the custom content type is not included in the list columns.  Normally, the custom content type columns are added to the list columns, but for some reason it works differently for the “Name” column.  Even though the “Name” column is not a typical site column and not included, it’s a problem for me because I need it for a SharePoint/SQL Server data flow task. The data flow task is using the SharePoint list source and destination components and I need to map a database column to the “Name” column and it must be specified in the list columns.  Does the “File” type for the “Name” column make it impossible to be included in the list columns?  Any help is appreciated.

Discovering ODBC Tables, Columns and column Definitions

  
I have worked with ODBC data connections for a while.  Usually, I've setup a connection to some type of database (IBM ISeries, SQL Server, Progress, etc.) depending on the project and then I use MS Access to inspect or export the data using linked tables. I have a database that has numerous tables and columns.  In fact, I believe there are some 16k data columns in the entire database.  Furthermore, I have an excel file that gives me a list of all of these tables and a brief description of each column  but it does not have useful data types in the description.  What I would like to do is query the ODBC source and extract each table, it's columns and it's column data types. My end goal is to take the data from the spread sheet (column names and description) and insert that data into a database that also contains the correct, SQL data type in an additional field.  Is there a way to query an ODBC data source to acquire all of this information?

Hiding Columns (not column groups) in a Matrix, can it be done

  
Hi.  I want to be able to hide some of the columns of this report.  For example , I don't want to see the % of State Total for Total column, and I don't want to see the % of Location Total for the other column groups.  Can this be done?  Any help would be appreciated.  Thanks J I am using Visual Studio 2005   Total   Anaheim CA Arlington VA Atlanta GA Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total USA   3 100.00% 0.02%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   AB 2 100.00% 0.01%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   AE 8 100.00% 0.04%   0.00% 0.00%   0.00% 0.00% 3 37.50% 0.10% 1 12.50% 0.07%   AK 32 100.00% 0.17%   0.00% 0.00%   0.00% 0.00% 3 9.38% 0.10% 1 3.13% 0.07%   AL 161 100.00% 0.85%   0.00% 0.00%   0.00% 0.00% 17 10.56% 0.54% 84 52.17% 6.11%   AP 3 100.00% 0.02%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00% 1 33.33% 0.07%

Update nullable column in db to null?

  
Can anyone reliably get the EDS to save a nullable column to the databse as a "null" when bound to any of the controls such as "FormView"? I have tried using several different UpdateParameters (Session, ControlParamater, Parameter,  etc). I have tried setting "ConvertEmptyStringToNull" to true and leaving the property off entirely. Nothing works. On my "Inserts" it works fine. (I have made sure the column is set to nullable = true in the Entity Designer.....)

restrictions on additional columns available for addition with Lookup column?

  
Can a choice column be added as an additional field when creating a Lookup column? I was able to do this in the beta version but have been unable to in RTM. The column does not appear in the list of availabe columns to add as additional fields.

How to know the number of columns displayed on the last page for Multi column table

  
Hi, How will I determine how many columns to be displayed on the last page of the report if I am using a Multiple column table.  I am going to use the number of columns to multiply with the width of the body of the report. I am going to use a Function to get the product. 

update stored procedure for existing values of column(salperyr)

  
tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

  
tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

Update one column of a table with data from another table

  
I have two tables which are different with the exception of a single field (column name = "LocationCode"). Both tables can also be joined with a common id field. I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id. It is impractical for me to update one row at a time. How can I update all the rows quickly?   But one more point both tables lies in different database Varinder Sandhu http://varindersandhus.blogspot.com/

Update one column of a table with data from another table

  

I have two tables which are different with the exception of a single field (column name = "LocationCode").

Both tables can also be joined with a common id field.

I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id.

It is impractical for me to update one row at a time. How can I update all the rows quickly?

 

But one more point both tables lies in different database


Varinder Sandhu http://varindersandhus.blogspot.com/
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend