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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to Get a Final Value based on comparing two columns for set of columns

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

Hello All,

Please help me here, to solve this query,  i included in detail, what i trying to achieve for


-- Create Table DDL
CREATE TABLE [dbo].[TestTable2](

[varchar](5) NOT NULL,
 [midvendor] [varchar](5) NOT NULL,
 [odate] [varchar](6) NOT NULL,
 [orderid] [varchar](10) NULL,
 [marketid] [varchar](5) NOT NULL,
 [Amount] [float] NULL,
 [MagicAmount] [float] NULL,
 [Units] [int] NULL,
 [MagicUnits] [int] NULL,
 [Final] [varchar](10) NULL,
 [Reason] [varchar](30) NULL


---- Data Insert DDL

insert into testtable2 values ('BAY1','MAD','DEC21',NULL,'1070',0,48526,NULL,8,NULL,NULL)
insert into testtable2 values ('BAY1','MAD','DEC21','161711','1070',70965,0,9,NULL,NULL,NULL)
insert into testtable2 values ('BAY1','MKA','DEC21',NULL,'1070',0,372419,NULL,43,NULL,NULL)
insert into testtable2 values ('BAY1','MKA','DEC21','161891','1070',389392,0,49,NULL,NULL,NULL)
insert into testtable2 values ('BAY1','MLD','DEC21','162102','1070',0,0,0,NULL,NULL,NULL)
insert into testtable2 values ('BAY1','MLD','DEC21

View Complete Post

More Related Resource Links

How to Get a Final Value based on comparing three columns for set of columns


Hello Sir,


in continuous to this below is a new math, i am trying to solve but very hard to get it done, could some please help me.....

--- Original Draft
-- Create Table DDL
CREATE TABLE [dbo].[TestTable2](

[varchar](5) NOT NULL,
 [midvendor] [varchar](5) NOT NULL,
 [odate] [varchar](6) NOT NULL,
 [orderid] [varchar](10) NULL,
 [marketid] [varchar](5) NOT NULL,
 [Amount] [float] NULL,
 [MagicAmount] [float] NULL,
 [Units] [int] NULL,
 [MagicUnits] [int] NULL,
 [Final] [varchar](10) NULL,
 [Reason] [varchar](30) NULL,
 [CheckCol1]  [varchar](10) NULL,
 [CheckCol2]  [varchar](10) NULL,
 [CheckCol3]  [varchar](10) NULL


---- Data Insert DDL

insert into testtable2 values ('BAY1','MAD','DEC21',NULL,'1070',0,48526,NULL,8,NULL,NULL,'0','0','0')
insert into testtable2 values ('BAY1','MAD','DEC21'

dynamically adding sql server columns based on number of files in fileuploader


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.


I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!


Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


gridview help on each dynamic row solve this show values based on all columns

<asp:GridView style="Z-INDEX: 101; LEFT: 1px; POSITION: absolute; TOP: 183px" id="Gridview1" runat="server" Width="613px" Height="127px" ForeColor="Teal" Font-Size="12pt" Font-Names="Palatino Linotype" Font-Bold="True" Visible="False" BorderColor="Transparent" AutoGenerateColumns="false" CellSpacing="2" GridLines="None" OnRowCreated="Gridview1_RowCreated" SelectedIndex="5" ShowFooter="true" OnRowDataBound="Gridview1_RowDataBound"><Columns>  <asp:TemplateField HeaderText="ItemCode"><ItemTemplate> <asp:DropDownList id="DropDownList1" tabIndex=16 runat="server" AutoPostBack="True" DataValueField=" ItemCode " DataTextField=" ItemCode" ></asp:DropDownList></ItemTemplate></asp:TemplateField>  <asp:TemplateField HeaderText="ItemName"><ItemTemplate> <asp:DropDownList id="DropDownList2" tabIndex=16 runat="server" AutoPostBack="True" DataValueField="ItemName" DataTextField="ItemName" ></asp:DropDownList> </ItemTemplate> </asp:TemplateField>  <asp:TemplateField HeaderText="Category"&

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

Error comparing two identical columns in Condtional Split


(SQL 2005)

I'm reading in one SQL table and doing a lookup against another SQL table which is an identical copy of the first table, used for temporary storage during the load event.

I want to compare each field to find if there are any changes in the temporary table.  When I compare two string fields, Customer != temp_Customer I get no error message.  When I compare two decimal fields, Width != temp_Width I get an error message: DT_WST and DT_Numeric are incompatible with !=.

The two fields are BOTH declared as decimal 19,6 in their respective tables.  Why can't I run this comparision???

Display columns in matrix based on a group even if data don't exist in resultset


I am trying to create columns in a matrix for data that currently does not exist.  I created a matrix that groups on office name (ROWS) and Month of Appointment Date (Columns).

For example, the matrix currently looks like this.....

  January February March April

Auto Populating SharePoint List Columns based on value in one column

   I have a SharePoint List where I want to auto populate the value of some of the columns from another List based on a field "Title" which is primary & unique....
   I want to auto populate them when clicking "New Item"  and entering data... you put the "Title" value and it should match the Title value from another List and auto-populate some the columns...

   Please any idea would be highly appreciated....

Thanks in advance

comparing two database tables to all find matching columns



is there any query to find matching columns from two database tables(not system tables).




Thanks and regards, Rishabh

Hiding Columns in Matrix (RS 2005) Report Based on Columns' Values



Is there a way to hide automatically columns in a Matrix type raport in RS 2005.  The columns I want to hide have only 'N/A' values in them because there is no data available to populate them.  Here is the example:

  Fall 2010 Fall 2009

display / insert values by comparing 2 columns of a table



i have a table which has the following columns 

A_ID, A_Value,A_Date,B_ID,B_Value_B_Date


i have another table as follows

A_ID,B_ID,A_Value,B_Value, Date


this is to be done 

* if A_date and B date are equal, one record shoould be entered to the TABLE 02. the values should be A_ID,B_ID,A_Value_B_Value and A_Date (since the date is equal, no problem; even can insert B_Date instead of A_Date)

* if A_Date and B_Date are different, then the following should happen

insert one record to TABLE02 as A_ID,B_ID, A_Value, 0 , A_Date

insert another record to TABLE02 as A_ID,B_ID, 0 , B_Value, B_Date


(As you can see, when the date differs we enter the single record of Table 01 into 2 recoerds in table 02. the Value column and Date column are related in such a way that when A's date is entered, the value for B should be 0 and vise versa..)


i tried having a UDF but in halfway remembered that we cannot include INSERTS in UDFs..

can anyone please explain me how can i solve this problem ??


PS: For your reference , i've sketched a diagram. please click on this link to view it for further clarifications



thanks in advance

-novicedba :)

Custom Columns - Fill values based on value selected in a lookup column



I am working with Sharepoint 2007. I have a list of 130 buildings located in six different cities and three different provinces. I imported this list from an Excel spreadsheet. I also have a document library that contains various files associated with these buildings. I've added a custom column to the document library that contains a lookup field to the name of each building. I also have two additional custom columns that have the city and province. What I would like to accomplish is to have the city and province columns automatically be set based on the name of the building selected, such that these values are taken from the same row of this list as the building name.

Hopefully my question makes sense.

Thank you, James

Select columns based on date in row kinda like a cross tabe


I have a querry that will add data to a temp table for a period given the begin date and end date, for each month that there is data i need to have a column, which i did with :



(case when Month([Date])=1 then

Gridview column based on other columns


I realize this is a simple question.

I have a gridview with 3 columns, price, tax, and total (for example).

  I want the total column to contain price + tax. 

I've set all three columns to be templated, although maybe they don't need to be.  This grid is read-only. 

I'd like to do it in code and not in a Bind, because there are some other calculations I need to do for other columns and it's easier for me to do it in VB. 

Do I do this in pre-render for the label in column 3? If so, how do I access the contents of the other columns in that row? 



Update table - based on result of 2 columns from other tables


MSSql 2008 (2000 state)

Trying to update a column in a mapping table based on if the mapping is correct.

Table 1 own car data (PK model_ID)
Table 2 external cardata (PK Styr_ID)
Table 3 User mapping between Table1 and Table2 (PK's model_ID and Styr_ID)

I have made quite a big SQL joining data from Table 1 and Table 2 to verify if mapping is correct - And I have retrieved all incorrect rows that needs to be updated in table3 - Succes... Now I need to update Table 3 based on these results..
- The result is 3500 rows containing both model_ID, Styr_ID (these I have inserted in a temp_table - Both it didn't help me out..)

The problem I can't figure out, is that I need both values to find the correct row and update it in table3..
The only way I can see it done, is by selecting twice - but then it's not nessesary that the values are from same row (i actually doubt that they ever are)

Update table3 set status = 'A' where ......   select model_ID, select Styr_ID

Need a search query based on Columns


I have webform having these columns/fields WoNo,partNO,Customer,DDueDate,PDuedate,TMSDuedate,QADueDate,qty field  and a button called WorCenters.when i click on this button all the values should be listed based on these inputs DDueDate,PDuedate,TMSDuedate,QADueDate(4 columns are called as Worcenters).for example user wants to list all the WoNo(work orderNumber),customer under a particular work center,how can i do this,i dont have any column on WorkCenter either on webform or in database.Can any one help me how to acheive this


Segregating Measure values based on the dimension value into separate columns.


Hi Hopefully you can help me with another problem I'm having.  I'm trying to design a regular SSRS report using a cube as data source here is where I run into difficulties.  This is a financial type report so it has attributes such as Actuals, Forecast,Plan etc.  These attributes reside in a cube in a dimension called "PROCESS".  The measures is of course dollars.  So what I need is to somehow segregate dollars for actuals into one column forecast into another etc etc in my query designer so my dataset has 3-4 distinct dollar columns on which I can build my report. I thought I could accomplish it by using a calculated member functionality but I can't find anything there that will let me filter the values by "PROCESS" dimension.  I've also tried to create separate datasets filtering dollars the way I need them in each however report will not let me have fields that belong to two different datasets.  Any thoughts on how to solve this issue.  Hopefully using GUI instead of MDX editor. 


Comparing Data in Columns



i have a table which has the below columns

tablename   operation       date           oldvalue        newvalue
 t1               update     2010-11-11    1,test,123     2,test,123

Here, the table T1 is updated.

My Rek is to compare the oldvalue and newvalue columns and say thatwhich  column  has been updated.

The changes may be on any column on table t1.

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