Find Max Values From 3 Different Columns?
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
ADVERTISEMENT
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
May 6, 2015
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA :  abc,cde,efg  Â
ColumnB : cde,xyz,abc  Â
the result in  Â
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
View 5 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Jun 11, 2015
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
View 3 Replies
View Related
Mar 22, 2006
hi,
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
regards,
nicolas
View 5 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
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
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
May 28, 2008
I have written this script: What I want to do now is, with the new JOINED table find and display all the duplicates custID WHERE the price is either 100 OR 200. Can anyone help? I am very new to all this and can't see how to do it. Thanks!
SELECT
*
FROM
table_customer T1
INNER JOIN
table_itemsBought T2
ON
T2.custID = T1.custID
WHERE
price = '100'
OR price = '200';
View 2 Replies
View Related
Mar 26, 2015
I have a column called firstname ..in that it stores value like this
john smith
andrew jr
jim sr
andrew bar
tina *^
don $%
I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..
I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?
View 2 Replies
View Related
Dec 7, 2007
I was wondering how to do a find and replace with SQL? Would I use a SET statement?
I need to find a specific value and replace all of the results with a different value.
Thanks for all responses.
View 1 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
Apr 11, 2008
I am trying to merge records with a stored procedure and I am getting a constraint violation error. Just wondering is there a way of finding out which records are the actual culprit?
This would help greatly in being able to troubleshoot this issue.
The error is this:
2/21/2008 12:40:39 PM :: Violation of UNIQUE KEY constraint 'SK_SomeConstraint'. Cannot insert duplicate key in object 'tblTable'.
The statement has been terminated.
The constraint is a group of clustered indexes that must be unique:
GO
/****** Object: Index [SK_SomeConstraint] Script Date: 04/11/2008 14:56:17 ******/
ALTER TABLE [dbo].[tblTable] ADD CONSTRAINT [SK_SomeConstraints] UNIQUE NONCLUSTERED
(
[IDa] ASC,
[IDb] ASC,
[IDc] ASC,
[someField] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
View 4 Replies
View Related
Dec 10, 2013
Is there a way to find duplicate values and get the timediff from the start and end of each duplicate.
datetimeTagname value MachineValueTime Diff Minutes
12/9/13 8:55machine_1 14,423 Machine_1 14,423 5
12/9/13 9:00machine_1 14,423 Machine_1 14,428 9
12/9/13 9:05machine_1 14,428 Machine_1 42,743 4
12/9/13 9:10machine_1 14,428
12/9/13 9:14machine_1 14,428
12/9/13 11:32machine_1 42,743
12/9/13 11:36machine_1 42,743
View 6 Replies
View Related
Feb 14, 2008
Hello,
I forget. How can PATINDEX be used to find column values where the data is all numeric? I've tried, the following:
select distinct acct from tbl_CYProcessedSales
where PatIndex('%[0-9]%',Acct) > 0
order by acct
Acct is varchar(8). What am I doing wrong?
Thank you for your help!
CSDunn
View 12 Replies
View Related
Mar 25, 2008
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.
I have tried to find the values which are out of the smalldatetime range, with the following query
SELECT *
FROM midmar
WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'
Which doesn't quite work, and gives me values that are actually between those two values listed.
I have also tried having the WHERE clause read:
WHERE bday <'06/06/2079' AND
bday>'01/01/1900'
and that doesn't really work either.
What's going on? Is there likely another problem besides the structuring of my queries?
View 9 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
