Find Lowest Value In 3 Columns
Jun 2, 2008
Im having trouble finding a solution to comparing columns to find the lowest value.
Example
Row 1
COL 1 = 10
COL 2 = 20
COL 3 = 60
COL 4 = 5
COL 5 = 35
I would want to return 5.
I tried to write a case statement such as below but am unable to use Logical Operators.
WHEN C1 <= C2 and C1<= C3 THEN C1
WHEN C2 <= C3 THEN C2
ELSE C3
any help would be great.
Thanks,
Nick
View 1 Replies
ADVERTISEMENT
Sep 14, 2012
SELECT
@AppId AS Application_Id
, MB.POLICY_ID AS Policy_Id
, MH.BFN_CHG_DATEAS Date_Of_Disinvestment
, AD.X_INVEST_AMT AS Original_Investment_Value
, MB.TOTAL_AMOUNT AS Encashment_Value
, WH.TOT_COMMISSION AS Top-Up_Commission
FROM: I select from lot of table, as specified from my select statement
My output:
AppId PolicyId Date_Of_DisIn Original_Inv Encashment_Value Top-Up_Commision ???
1-1-1; 1000000; Jan-01-12; 2882.07; 3312.00; 2306; ??
My Aliases returns just one values, of which that's what I am expecting, so I need to make a comparison between Original_Investment_Value and (Encashment_Value + Top-Up_Commision), and return the lowest value
View 2 Replies
View Related
May 5, 2008
Hi is there any way to find no. of columns in a database table
like we can do that in Oracle by typing DESC tablename
San
View 5 Replies
View Related
Mar 9, 2008
Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this.
View 3 Replies
View Related
Apr 19, 2007
i like to write a sql query to find out similar columns in different tables.
i have something like this. but my logic is wrong.
select *
from information_schema.columns a
join information_schema.columns b
on a.column_name = b.column_name
and a.table_name <> b.table_name
View 1 Replies
View Related
Nov 27, 2006
In every database we have 'sysobjects' table storing names of all objects(including all keys) in our data base. How can I find all columns, that the key is made of? Sorry about my english :-/
View 1 Replies
View Related
Oct 16, 2007
Can anyone pls help me to solve my problem in SSRS. Also I wanted to plot the 'Ratio' in to a chart against the 'Year'.
The scenario is:
Year Amount_A Amount_B Ratio(Amount_A/Amount_B)
05 x y x/y
06 a b a/b
07 m n m/n
Its very needy.
View 9 Replies
View Related
Jul 21, 2007
I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?
View 3 Replies
View Related
May 9, 2008
TableName: EmployeeCloumns: EmployeeID EmployeeName If I do "Select * From Employee", is there a way to do something like this...
Loop through each result IterateThroughControls.ID &= dbTable & dbColumnName & "<br />"EndSo that the end result would assign ID values...EmployeeEmployeeIDEmployeeEmployeeName
View 5 Replies
View Related
Jun 15, 2005
Hi,
I have a sql query which gives me the result set with lots of columns and rows.
a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395
i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?
Thanks
View 1 Replies
View Related
Oct 9, 2006
Hello,
I want to find the columns which execute updatetriggers. Is there in sqlserver a way to find out?
In Oracle you can say:
SELECTCOLUMN_NAME
FROMDBA_TRIGGER_COLS
WHERETRIGGER_OWNER = 'MyOwner'
AND TRIGGER_NAME = 'JobUpdate'
AND TABLE_NAME = 'Job'
AND COLUMN_LIST = 'YES'
I tried: EXEC sp_depends "dba.JobUpdate".
This says nothing about the updatecolumns.
Can anybody help?
thanx and greetz
C Dunnink
The Netherlands
View 3 Replies
View Related
Jan 24, 2014
I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg
Debt_amount - billed_amount
where debt >billed amount
I only want to see records where the debt is more than the amount billed
View 1 Replies
View Related
May 30, 2007
Dear experts,
how can i find the ntext datatype columns in a database?
please guide me
View 4 Replies
View Related
May 30, 2007
Dear Experts, i need one query to find all the indexed columns with table names ,column names and type of indexes....
i'm trying with sysindexes, but i was unable to finish it....i'm not clear about keys column in sysindexes...
please guide me.
thank you very much
View 12 Replies
View Related
May 26, 2006
I need to create an function similar to the "MATCH" function in Excelthat evaluates a number within a set of numbers and returns whetherthere is a match. I have put the example of what I see in excel in thecheck column. The "0" answer in the result column is in the fourthaccount in the list. Somehow I need to loop through the accountscomparing the result to the total and indicate a match in the checkcolumn. It wouldn't even need to tell me the row number; it could be a0 or 1.account total result check123770266.84124.2112377026131.050 412377026164.38-33.33123770260131.051237702678.7152.3412377167-31.34221.891237716731.34159.211237716738.55152 51237716731.34159.211237716715238.5512377167490.91-300.36123771670190.55123771670190.5512377167-31.3443.341237716731.34-19.341237716738.55-26.551237716731.34-19.3412377167152-14012377167490.91-478.9112377167012123771670121237736347.058412377363131.05012377363-45.38176.4312377363-47.05178.11237736347.0484.0112377363-47.04178.091237736347.058412377363541.11-410.06123773630131.0512377363672.15-541.11237750737.64152.91
View 3 Replies
View Related
Aug 2, 2007
For example if we clearly define a foreign key in the master and child table as following script -
CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
FOREIGN KEY (fkey) REFERENCES master(pkey))
We can find out the two tables reference relationship by looking at INFORMATION_SCHEMA tables.
However, if the two tables are created in this way €“
CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int)
Does any one know how to programming verify the actual reference relationship exists between pkey and fkey in these two tables?
Thanks
View 1 Replies
View Related
Mar 23, 2006
Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?
View 8 Replies
View Related
Nov 15, 2006
Hi,
i have a problem where in i have to display the greatest marks scored by each student.
How can i do this?? Is there any built in TSQL function.
Rgds..,
Aazad
View 4 Replies
View Related
Mar 19, 2008
How do i Find all Columns that have Identity Property in a database. That is i will like to know all columns in a database that are identity columns.
I am using SQL Server 2005. Thanks
View 4 Replies
View Related
Jul 23, 2015
I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision
Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative
Expected
Customer 1 had a max of refnum of 2 and 1st one on number
IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative
View 21 Replies
View Related
Apr 21, 2015
We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database. The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.
We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).
I googled, but I get only all the columns in tables of database.
I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded databases).
View 8 Replies
View Related
May 28, 2012
I have the following table:
f_namef_countryf_ID
ABCUS123
DEFGB123
ABCUS456
GHIGB789
etc.
I need to run a query to discover all instances where a f_name and f_country pair exists for more than one f_ID. ABC/US is one such example; IDs 123 and 456 have this pair.
View 7 Replies
View Related
Oct 22, 2015
CREATE TABLE XYZ
(
ID int DEFAULT 5000,
NAME VARCHAR(100)
)
I have many tables that have many columns with default values
Is there a way to get a listing of
TAB_NAME, COL_NAME, Data_type, Default_Value
View 2 Replies
View Related
Aug 14, 2014
How would I compare 2 rows to find the difference in the columns?
Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3
Result:
Column1: Text2 New
Column3: Text4 Old Text3 New
View 6 Replies
View Related
Nov 10, 2006
How do i find indexes of the columns of all the tables of thedatbase...........most importantly in SQL server 2000Thanks a lot
View 3 Replies
View Related
Nov 23, 2015
I would like to know all the columns used in a store procedures.I have used dynamic sql to create these procedures .
View 4 Replies
View Related
Mar 11, 2013
I have a sample view with some dates. How would you find the numbers of items open per month. Say between OpenDate and CloseDate I want to find how many were open for January, February,?
Here is a sample table with the data
Code:
CREATE TABLE [dbo].[TestDate](
[ItemTitle] [nvarchar](50) NULL,
[ItemAttachAssignDate] [date] NULL,
[ItemDetachConcludeDate] [date] NULL,
[Status] [nvarchar](50) NULL,
[FullName] [nvarchar](100) NULL,
[OpenDate] [date] NULL,
[CloseDate] [date] NULL
) ON [PRIMARY]
GO
Code:
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2013-02-18 00:00:00', '2013-02-19 00:00:00', 'Done', 'Jeff Hunter ', '2013-02-18 00:00:00', '2013-02-19 00:00:00');
INSERT INTO [TestDate]([ItemAttachAssignDate], [ItemDetachConcludeDate], [Status], [FullName], [OpenDate], [CloseDate])
VALUES('2012-10-15 00:00:00', '2013-02-05 00:00:00', 'Done', 'Tommy Johnson', '2013-01-22 00:00:00', '2013-01-28 00:00:00');
[Code] .....
View 2 Replies
View Related
Apr 18, 2014
write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
View 4 Replies
View Related
Apr 17, 2014
how to find all primary key columns & foreign key columns in all database tables?
View 1 Replies
View Related
Sep 15, 2002
I need to find column names if any after 2 “check” columns.
Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.
So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.
Any help would be greatly appreciated.
Cheers
Henrik.
View 3 Replies
View Related
Oct 27, 2015
We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
View 1 Replies
View Related
Nov 30, 2004
OK, this should be an easy one but my brain isn't quite working right now.
I have a table, we'll call Table1 like so:
ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00
And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:
SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID
and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.
Thanks,
View 7 Replies
View Related
Jul 23, 2005
Is there a more elegant way to do the following?...declare @d1 datetime, @d2 datetimeset @d1 = '2005-01-01'set @d2 = '2005-02-01'selectcasewhen datediff(dd,@d2,@d1)is nullthen coalesce(@d1, @d2)when (datediff(dd,@d2,@d1)> 0)then @d2else @d1endCheers,..N
View 2 Replies
View Related