Compare 2 DateValues -&> Maxvalue In Transact Sql

Jul 20, 2005

I need the maxvalue of 2 colums for each row

Select date1, date2, maximum(date1,dat2) as datemax.

Is this simple to realize ?

date1 and date2 can be NULL.

something like this:

select date1,date2, maxdate =
CASE
when date1 is null then date2
when date2 is null then date1
else
if date1 > date2 then date1
else date2
from xy

thanks for your help
paul

View 4 Replies


ADVERTISEMENT

Transact SQL :: How To Compare More Than Two Dates In Server

Oct 10, 2015

I have three date columns startdate,enddate,rmd

I need to write a query like get all the rows by comparing rmd with system todaydate and these must be with in startdate and enddate

id startdate enddate rmd
1  10-oct-15   16-oct-15    11-oct-15
2   11-oct-15    14-oct-15     11-oct-15
3   09-oct-15     11-oct-15      10-oct-15
4   07-oct-15      08-ot-15        07-oct-15
5
if sysdate(today) is 11-oct-15 then i need to get all the rows of 1, 2,3

View 5 Replies View Related

Transact SQL :: How To Compare Two Database Field

Sep 23, 2015

I have 2 database, databaseA and DatabaseB with same table structure.

I change lot of table structure in databaseB.

So i want compare what field that i add or delete in databaseB.

it's like what field there are in databaseB and not found in databaseA or nothing in databaseB but exist in databaseA.

How can i do that?

View 7 Replies View Related

Transact SQL :: How To Compare Rows With Spaces

Nov 17, 2015

I would like to compare rows from 2 tables.

For ex:

Table1
Col1 Col2 Col3
1      a     aa
2      b     bb

Table2
Col1 Col2 Col3
1        a   aa
2      b     bb

Notice Table2.Col2 first row has an extra space " a", where Table1.Col2 value is "a".I need to compare all the columns, not just 1 column (any of the column can be different). Do we use CHECKSUM for this, or is there a better way to compare the 2 tables (in this case resulted in Table2 row 1 col 2 being the difference ?

View 10 Replies View Related

Transact SQL :: Compare Products With Same Option?

Sep 11, 2015

i designed a database for eshop.

i have two table one of them is products and it has id,code,name columns and the other one is option with id,proid,option,optiondes column. and there is a relationship with id from products to proid in option table.

i want to make query that results is compare two or more products with the same option column.

View 2 Replies View Related

Transact SQL :: Compare A Table Against Multiple Tables

Jul 16, 2015

I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)

Later for some other purpose, downloaded the same day's data into five small tables.

Now when I compare the total row count of  Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match. 

So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.

View 5 Replies View Related

Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers

Aug 12, 2015

I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.

I'm looking for added columns in dbase 1.

Is there a system T-SQL script that can be used for this?

View 4 Replies View Related

Transact SQL :: Pull Dates From Database And Then Compare

Jun 10, 2015

I need to pull dates from a DB2 database via TSQL (Linked server - IBM DB2 for i IBMDASQL OLE DB Provider) and compare it to today for a less than or greater than type comparison.

Database: DB2, Customer information housed here
Columns:
UTOFMM - Month (2 character, numeric)
UTOFDD - Day (2 character, numeric)
UTOFYY - Year (2 character, numeric. Problem: years from 2000 to 2009 are stored as 0, 1, 2, ... etc)
UTOFCV - Century Value (2 char, numeric.  0 = before 2000, 1 = in or after 2000)

I need to concatenate the date to be "sql" friendly, and then compare to today's date.  It's to find any customer with date values in the fields above, and then differentiate between dates before today and after today.Here is the snippet of what I'm trying to fix.  This portion of a nightly job is just checking for <u>any</u> value in the UTOFMM column of the current record.

Add Customer ID
Update [responder].[Temp_RX_CUSTOMERS]
set CustomerID = lf.UTCSID
from [responder].[Temp_RX_CUSTOMERS] LEFT Outer Join
[HTEDTA].[THOR].[HTEDTA].UT210AP lf ON [responder].[Temp_RX_CUSTOMERS].LocationID = lf.UTLCID
where lf.UTOFMM = 0
GO

View 4 Replies View Related

Transact SQL :: Compare Two Table With Multiple Date Comparison

Oct 20, 2015

I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure

DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')

[code]...

how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.

View 4 Replies View Related

Transact SQL :: How To Compare Duplicate Rows Within A Unique Process

Nov 9, 2015

We write to a log file each time a job runs. We give each job a unique batchid. I want to compare the run times of each step/record between two batch ids: '20150101888' and '20150101777'. Column Mins in the number of minutes each step ran. I am having trouble comparing the rows that have generic process and stepname – Trans Switch in this example. A new process within a batchid starts with a 'XX', 'Load'.

So I want to compare CA's Trans to CA's Tran Switch and ER's Trans Switch to ER's, etc. There can be multiple Trans Switch per process. There should be the same number between each batch, but no guarantees that  something might change. Also, Trans Switch is not the record right after the new process (CA, ER) in production.

I have just made a very simplified example.

/**
Want to compare 20150101888 to 20150101777 and end up with this result set. Notice that the duplicate process/step within a process has the process (CA and ER in this example) and a sequential number added to it: 'CA Trans 1'. Need this to pull out the largest time differences.

Time difference, process, step, mins1, mins2, batchid1, batchid2
-6, CA, Load, 17, 23, 20150101888, 20150101777
0, CA Trans 1, Switch, 8, 8, 20150101888, 20150101777
-6, CA Trans 2, Switch, 9, 15, 20150101888, 20150101777
-4, ER, Load, 7, 11, 20150101888, 20150101777
-4, ER Trans 1, Switch, 7, 11, 20150101888, 20150101777

**/

[Code] ....

View 4 Replies View Related

Transact SQL :: Compare Event Time Value With Current Year And Month

Aug 27, 2015

I have the following code block

CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2

My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'

View 4 Replies View Related

Transact SQL :: Query To Compare Current Price With That Of Previous Value Of Item

Nov 18, 2015

There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'

Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.

ID Date
Price Status
1 11/12/2015 100 Low Value

2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value

create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)

[Code] ...

View 15 Replies View Related

Transact SQL :: Two Row Serial Number Date Compare And Take More Than 5 Hours Between Two Rows

Jul 1, 2015

A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.

tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate

TLM3004242015

[Code] .....

I want to take the result like this 

Tld_tripno
Finish Loading
Gate Out
Date and Time difference

TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00

View 10 Replies View Related

When Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)

Jul 22, 2007

Table MediaImportLog
column ↘ImportIndex     ImportFileTime            ImportSource
value    ↘80507             20060506001100          815
              80511             20061109120011           CRD                       ã€? P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table  BillerChain
column↘BillerInfoCode       ChainCode
value   ↘750                      815
value   ↘81162                  CRD
Table   Biller
column↘CompanyCode         BillerCode
value   ↘999                     750
value   ↘81162                  516
TAble DataBackup
column↘CompanyCode         Keepmonth
value   ↘999                     6
value   ↘81162                 12
 
---------------------------------------------------
 
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ? 
 
thank you very much 

View 3 Replies View Related

Compare 2 DB&#39;s

Mar 13, 2000

I remember reading that you can compare the schema of 2 databases to see if there are any diferences. For the life of me, I can not remember where I read it or what it is called. Any help would be great!

Thanks
Tammy Maxfield

View 2 Replies View Related

Compare?

Sep 30, 2004

How can I compare data in 1 column??

Thanks

View 4 Replies View Related

SQL Compare

Jan 25, 2006

Can you please help me figure out the best SQL Compare (structure and data) tool in the market.

Thanks,
Saurav

View 11 Replies View Related

Compare Using &< &<= &> &>=

Mar 22, 2007

Hey all,

If we use operator < <= > >= to compare NON nummeric, then how it is being compared..? what it returns?
eg : .... WHERE someObj1.UniqueX <= someObj2.UniqueX

considering :-

someObj1.UniqueX someObj2.UniqueX
MM1 NN1
MM2 NN2
MM3 NN3




~~~Focus on problem, not solution~~~

View 7 Replies View Related

SQL Compare

Oct 27, 2007

Hi all !

i'm new in sql and new in database.

And my question :

i m trying to compare sql database tables,views,columns,stored procedures.

First i compared tables,columns, but i can't do it with stored Procedure.



Why ? Because stored Procedures are code...

Ex... When i Compare table and columns use these codes.

"if (sourceColumn.IsString)
{
strSize = sourceColumn.Type.ToString()+"(" + sourceColumn.Size + ")";
sqlAddColumn = string.Format(@"ALTER TABLE {0} ADD {1} {2}", target.Name, sourceColumn.Name.ToString(), strSize);

....
This is my StoredProcedure Compare method

public string Compare(StoredProcedure storedTarget)

{
string storedScript = string.Empty;
if (storedTarget.RoutineDefinition != this.RoutineDefinition)
{
// string storedScript = (@"ALTER PROCEDURE ); <--- How can i put new stored procedure ?????? What is sql code for this work ?
}
}
Thank You all sql guru s

View 5 Replies View Related

Compare

Nov 26, 2007

I have an application that writes records with a timestamp to an sql table.

I wish to compare the values in each field and see if they have changed from the previous record and then delete if they haven't. Can anyone offer a script or procedure to do this please?

View 2 Replies View Related

Compare

Jul 23, 2005

hi i doing a project like "IMesh", whatever ,i will take a sentencefrom the user and want to retrieve all row from the table has similarwordsfor example the user enter "programming with c#" so i retrieve allfields that contain "programming" or "c#"some thing like what a search engine dothanx

View 1 Replies View Related

Compare

Apr 25, 2008



Hello All

i have a table with Three Fields

id StartSeqNo EndSeqNo
1 A000101 A000200
2 AA000101 AA000200
3 ABC1012A ABC2012B

Now if i pass 'ABC1015A'
The Query Should Check between StartSeqNo and EndSeqNo and should return that row

Please Help Me

Thanks in Advance

Regards
Balagangadharan

View 4 Replies View Related

Compare Two Dates

Dec 14, 2006

I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?

View 7 Replies View Related

Compare Month In SQl

Mar 10, 2008

Hi,
I have two varchar column in SQL.there im storing Month name
Lets say,
col1     col2 
JAN     APR
MAY    DEC
Is there any possibility to use the sql query select * from tablename where DEC between (col1,col2)
I just wonder since it is varchar field and not date field can i use this syntax to compare ? or any other better way to achieve this?
Thanks for any response.
Regards,
RR
 

View 1 Replies View Related

How Do I Compare Dates?

Apr 13, 2008

Hi,I need to compare Todays Date with a Date in the Table (DateExpired)to see if the membership expired. How do I do that?   thanks 
SELECT  DateExpired  FROM Member

View 2 Replies View Related

How To Compare A Value In Database?

May 14, 2008

i am making a transaction function for my website, when people transfer an amount from account A to account B, it should check the amount if account A has enough money to transfer. here is my codes, should i add an if statement before the tra.update()? how to write this codes? SqlDataSource tra = new SqlDataSource();                tra.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString();                tra.UpdateCommand = "update account set balance=balance" + -Convert.ToDecimal(TextBox5.Text) + " where AccountNumber = '" + TextBox3.Text + "'";                tra.Update();                tra.UpdateCommand = "update account set balance=balance+" + Convert.ToDecimal(TextBox5.Text) + "where AccountNumber = '" + TextBox4.Text + "'";                tra.Update();                tra.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('T','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() +"')";                int inertRowNum = tra.Insert();                Server.Transfer("deposit_withdraw_confirm.aspx");

View 5 Replies View Related

How Do I Compare Date In MS Sql

Jun 13, 2008

Hi,
I have a table with 3 columns inside
- record_id (int)
- user_id (varchar)
- login_date (date)
it is  a many-to-many relationship table that record login date of users
 
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use
SELECT COUNT(*)
FROM table1
WHERE login_date < '2008-31-05'
 
That's works
 
But the problem is I want to split the result to
- How many people have login in the last 0-5 days (based on the input (31 MAY 2008))
- How many people have login in the last 6-10 days (based on the input (31 MAY 2008))
 
How do I do that?
 
Please help
 

View 5 Replies View Related

Compare Date Value In SQL SP

Jun 20, 2008

I have table:ArticleIDSubjectBodyDateThe Date field have data:12.02.200512.02.198912.02.156314.09.134530.12.345I need to show Articles wiich have date today. Example:If today is 12.02.2008 I should show only Date with value 12.02. The results:12.02.2005
12.02.1989
12.02.1563 I try something with DAY, Returns an integer representing the day datepart of the specified date but it's doesn't work.Help :)

View 5 Replies View Related

Date Compare

Feb 2, 2005

How do you compare date in sql, it is giving me errors,

I want to view all that logged onto my system Today
(*Yes I want to use dyn query for this *)

LastLogin is of type DateTime

SELECT @myStatement = ' SELECT LastLogin, surname
FROM #TEMP WHERE ((LastLogin ='+CONVERT(datetime, @LastLogin)+')

exec(@myStatement)

Thanks in advance

View 4 Replies View Related

Compare Databases

Aug 23, 2005

Hi,I have a field TestDescription in tableA in DatabaseA. The data in this field is coming from three different fields in three different fields in DatabaseB.Is there a way to write a script to check if the length of this field TestDescription is greater than length of all those three fields in DatabaseB?Thanks

View 1 Replies View Related

SQL Compare Date

Nov 4, 2005

I want to know how can I write a sql to compare 2 dates which can run in SQL Query Analyzedeclare @date1 datetime, @date2 datetime
set @date1 = '2005-01-22 12:00:05'set @date2 = '2005-01-22 13:05:01'how can I compare whether @date1 = @date2 in terms of (YYYY-MM-DD) only?

View 3 Replies View Related

Sql Datetime Compare

Nov 4, 2005

how can i compare date @date1, @date2, @date3I want to check whether @date2 <= @date1 AND @date1 < @date3  in terms of (comparing Year, Month, Day ONLY)Those date don't need to compare the time.Any idea?

View 1 Replies View Related

How To Compare Dates

Feb 4, 2002

how to compare the dtaes for the follwing

SELECT COL1,COL2 FROM TABLE1
WHERE WORKDATE >01/01/2002

THE WORKDATE IN THE TABLE IS HAVING DATETIME DATATYPE.
I DONT WANT CONSIDER THE TIME OF THE DATEPART.
I JUST WANT TO COMPARE THE DD-MM-YYYY.

PLEASE HELP ME IN THAT.
THANKS IN ADVANCE

View 1 Replies View Related







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