Calculate Differences Between Records?
Feb 9, 2015
I would like to calculate the date difference between two records:
IDStartEnd
12/9/15 13:28:412/9/15 13:28:46
22/9/15 13:29:032/9/15 13:29:12
32/9/15 13:29:172/9/15 13:29:21
42/9/15 13:29:272/9/15 13:29:31
So I need to find the difference between two consecutive records and put the result in a new column called Downtime. For example:
Record 2 Start (2/9/15 13:29:03) - Record 1 End (2/9/15 13:28:46) = 0:00:17
Record 3 Start (2/9/15 13:29:17) - Record 2 End (2/9/15 13:29:12) = 0:00:05
Record 4 Start (2/9/15 13:29:27) - Record 3 End (2/9/15 13:29:21) = 0:00:06
and so on…
Also what do I do about the 1st record since there is no previous record to subtract from?
So far I have this code in my query to generate my table: SELECT Start, End FROM group_table3
My desired output should look like this:
IDStartEndDowntime
12/9/15 13:28:412/9/15 13:28:46#VALUE!
22/9/15 13:29:032/9/15 13:29:120:00:17
32/9/15 13:29:172/9/15 13:29:210:00:05
42/9/15 13:29:272/9/15 13:29:310:00:06
View 4 Replies
ADVERTISEMENT
Sep 9, 2014
How can we calculate the difference between two dates (years, months and days)
example:
between '01 / 01/2011 'and '05 / 04/2014' I would have years, months and days
View 1 Replies
View Related
Feb 8, 2007
I am trying to test some data handling between two different versions of an application.
I have restored the database schema twice, once as DB_old and once as DB_new.
I import a transaction using the new application into DB_new and I import the SAME transaction into the DB_old using the old version of application.
I then have to eyeball the data in SQL Query Analyzer to try to identify problems where the fields have received different values.
I have done this by running a select statement twice telling it to use both of the databases and then viewing it in two grids. There are a lot of columns so I have to do a lot of scrolling across the screen to do the comparison, and since the view is in two separate grids I have to hop back and forth and click the scroll bars, etc.
It seems like there has to be a better way. I don't suppose there is a way to lock the two grids so they both scroll together is there?
I was thinking maybe I could insert each of the selects into a temporary table and then do some kind of comparison to identify which values were different in each column. Some of the columns will have differences, like the timestamp, but if I could somehow identify which columns were different then I could eyeball them to identify which of those were okay to be different and which of them were actually bugs from the changed application version.
I have no idea how to identify those individual columns with different data values or even where to start.
Just so you understand better what I am doing now here is the query I am running that I then eyeball:
use DB_new
select * from claim where claim_id = 35144
use DB_old
select * from claim where claim_id = 35144
Thanks for any ideas.
View 7 Replies
View Related
Aug 28, 2015
I have table that contains below data
CreatedDate ID Message
2015-05-29 7:00:00 AOOze abc
2015-05-29 7:05:00 AOOze start
2015-05-29 7:10:00 AOOze pqy
2015-05-29 7:15:00 AOOze lab
2015-05-29 7:20:00 AOOze lmn
2015-05-29 7:30:00 AOOze start
2015-05-29 7:35:00 AOOze stop
2015-05-29 7:40:00 AOOze pqy
2015-05-29 7:45:00 AOOze stop
2015-05-29 7:50:00 AOOze lmn
I need to Find Maximum interval time for between message like Start and Stop as per order of createdDate.
For example:
OccuranceCount MinDate MaxDate DurationInSeconds
1 2015-05-29 7:05:00 2015-05-29 7:35:00 30
2 2015-05-29 7:30:00 2015-05-29 7:45:00 15
View 6 Replies
View Related
Jan 24, 2006
I have a table of sample data
Samples(sample_no, sample_date..)
I have no idea how to do the following in sql server or if its even possible:
1. Calculate the difference in days between all samples.
2. Select the median result
Any trick to get this done would be really helpful
thanks,
DB
View 3 Replies
View Related
Jan 24, 2006
I have a table of sample data
Samples(sample_no, sample_date..)
I have no idea how to do the following in sql server or if its even possible:
1. Calculate the difference in days between all samples.
2. Select the median result
Any trick to get this done would be really helpful
thanks,
DB
View 10 Replies
View Related
Oct 16, 2006
I execute the following SQL, and return 3 records, I want to know how many disk space the 3 records use, how can I do? thanks! select * from myTable where username='Paul'
View 6 Replies
View Related
Oct 7, 2015
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
View 6 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Nov 22, 1998
I am curious to what major differences there are between these two versions.
Trying to decide whether or not to purchase the SQL 6.5 training kit from Microsoft or not. If the code and utilities are the same, then I could probably still learn from the 6.5 version. Any thoughts, suggestions will be greatly appreciated.
Thanks
View 3 Replies
View Related
Aug 12, 2004
What is the differences between SQL Server Desktop Engine and SQL Server Standard Edition???
Thanks
Lystra
View 3 Replies
View Related
Feb 13, 2008
Hi,
I am having the following problem AFTER converting to VS2008 from VS2005 and SQLCE 3.5 from 3.01:
SQL CE db file has a table called Court0 with various columns of type float. I populate the values by copying floats from another table/tables. I do this via ado.net using this code snippet:
foreach (DataColumn column in this.mycourtsDataSet1.Tables[tableName].Columns)
{
string columnName = column.ColumnName.ToString();
string columnValue = aRow[0][columnName].ToString();
object cValue = aRow[0][columnName];
if (columnName.Remove(1) == "T" && !string.IsNullOrEmpty(columnValue))
{
// Add the value to the Court0 table.
DataRow[] bRow = this.mycourtsDataSet1.Tables["Court0"].Select("BookingPeriod = '" + columnName + "'");
if (bRow.Length > 0)
{
double colValue = Convert.ToDouble(cValue);
//bRow[0][tableName] = Convert.ToInt32(columnValue);
========> bRow[0][tableName] = colValue; <==== colValue is '1055.01'
}
}
}
}
This works fine in VS2005/CE3.01 BUT not in VS2008/CE3.5
In CE3.5, the value entered into the cell looses it's decimal value.
For example, '1055.01' becomes '1055.0' in CE3.5 .
Can someone explain to me why the conversion stuffs up in CE3.5 and what do I do to fix it.
Thanks,
View 1 Replies
View Related
Jul 4, 2006
Table struct (table1 and table2):
areacode
phonenumber
phonenumber2 (combined areacode + phonenumber) (actual column)
------------------------------------------------------------
select x.areacode, x.phonenumber from table1 as x
inner table2 as y
on x.AreaCode = y.AreaCode and x.phonenumber = y.phonenumber
result: 0
select x.areacode, x.phonenumber from table1 as x
inner join table2 as y
on x.phonenumber2 = y.phonenumber2
result: 100
select (x.areacode + x.phonenumber) as phone from table1 as x
inner join table2 as y
on (x.areacode + x.phonenumber) = (y.areacode + y.phonenumber)
result: 0
WHat's the difference between those queries? Why can't I get a result from the 1st and 3rd query?
View 9 Replies
View Related
Feb 5, 2004
We have a database that when an update is released (and this is very often) the release notes don't cover most of the actual changes. Every time groups of our custom queries and reports get broken due to database changes. Does anyone know how to compare two databases and get a report of the differences between them? I can either have the two versions on the same server or on different servers if that makes a difference.
I'm hoping for something where you input @oldversion, @newversion
and return is
@oldversion, tblname, fieldname, char(8)
@newversion, tblname, fieldname, varchar(8)
@oldversion, tblname, [Null], [Null]
@newversion, tblname, fieldname, int
@oldversion, [Null]
@newversion, tblname
also any changes in dependancies
Thanks
Brent
View 8 Replies
View Related
Feb 20, 2004
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding
SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up
Does this make sense?
View 11 Replies
View Related
Jul 7, 2003
as promised:
--sp_addlinkedserver @server = '____________'
--sp_addlinkedserver @server = '____________'
--select * from sysservers
--sp_addlinkedserver
-- '____________',
-- 'Oracle',
-- 'MSDAORA',
-- 'ORC1'
--Select * from ___________.ORC1.dbo.sysobjects
/* Objects in Company1 Missing in Company2 */
Select 'Table Objects in Company1 but are not in Company2'
select Left(a.name,30), a.refdate from sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From ____________.dbname.dbo.sysobjects b where a.name = b.name)
/* Objects in Company2 Missing in Company1 */
Select 'Table Objects in Company2 but are not in Company1'
select Left(a.name,30), a.refdate from ____________.dbname.dbo.sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From sysobjects b where a.name = b.name)
/* Column Differences */
Select 'Column Differences between like named tables'
select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
, Left(x.DataType,15) as Company1DataType, x.length as Company1Length, x.refdate as Company1RefDate
, Left(y.DataType,15) as Company2DataType, y.length As Company2Length, y.refdate as Company2RefDate
from
( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U' and a.name like 'TBL%') As x
, ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
where a.id = b.id and a.xtype = 'U'
and b.xusertype = c.xusertype
and a.name like 'TBL%') As y
Where x.TabName = y.TabName
and x.ColName = y.ColName
and (x.length <> y.length or x.DataType <> y.DataType)
/* Column Differences */
Select 'Column in Company1.com not in Company2'
Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from ____________.dbname.dbo.sysobjects d, ____________.dbname.dbo.syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2
/* Column Differences */
Select 'Column in Company2 not in Company1.com'
Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from sysobjects d, syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2
--Select 'Table Objects that are still in use in both Company2 and Company1'
--select Left(a.name,30), a.refdate from sysobjects a, ____________.dbname.dbo.sysobjects b
--where a.name = b.name and a.xtype = 'U'
Brett
8-)
View 1 Replies
View Related
Apr 2, 2008
I saw some demo-codes ,introducing sqlconnection class, sqlcommand class and etc, are involed .sqlexpress and c:abc.mdf.
so , I am quite confuse what's the deferences between .sqlexpress and c:abc.mdf
Thanks
View 1 Replies
View Related
Jun 2, 2005
What's the difference between using CREATE TABLE #TempTable and DECLARE @Table TABLE for temp tables and are there any advantages or disadvantages to using one over the other?Thanks
View 4 Replies
View Related
Mar 14, 2002
I have a server with two test instances of a data base. I have a query which creates a temp table, inserts 29 rows, perform 4 update queries to add counts and then dumpps out the results. This entire query script runs 1.33 minutes on one instance and 2.5 minutes on the other. On the production server this query now runs in 9 seconds. If I run any one of the test updates individually they execute under 2 seconds, just like the production server.
THe results are repeatable.
All are SQL 7 with all service packs on NT4 sp6. Both test data bases are backups of production from last week. I suspect some kind of caching/buffer problem, but I do not know what to look for. I am not a DBA so I have no idea what role TEMPDB plays may play in this.
Can anyone give us ideas on where to look for the performance difference? Will our impending upgrade to SQL2K solve this problem or make it worse? Any ideas would be appreciated.
View 1 Replies
View Related
Mar 29, 2002
Hi,
Is there any tool to find the differences between the two databases. I would like to know the differences in developmental server and Production server. if the developers create any new objects, I want to migrate them to production server.
Can we do it in sql server 200 or do we need to have separate tool.
Thanks in advance.
View 2 Replies
View Related
Sep 13, 2004
I am updating a db with data from a file, in this data we have new info, info that has been updated and info that is to be removed from the db.
Now I was wondering which approach results in better performance/shorter executin time:
1. first update excisting values, then insert new ones, and last delete cancelled data
or
2. delete cancelled data and data that will be updated, then insert new and updated info
I get all this data from a file, in that file all rows are similar and there is one column that defines if the data is new, updated or to be deleted (thus all the updates also include the information for the enty that has not been altered).
// Pati
View 4 Replies
View Related
Oct 18, 2005
Hi Guru's,
I am kind of baffeled. I have a table with a column of 8 varchar in 2000
and the same in 6.5. When I insert into 2000 with a data length of more than 8 chars via Cold Fusion into the table, it fails. The same Cold Fusion
program inserts into the 6.5 table, but truncates the data but does not fail.
Does anyone know why this happens. Thanks, Newbie.
View 1 Replies
View Related
Jun 3, 2008
I have written the SQL below and need to change the case. I need to say that if today’s date (get date) is between <> 4 weeks of sv_latest_appraisal then = Outstanding. Can this be done in SQL? I know it is very specific so not sure. Please help! Sam
SELECT Employee.FORENAME AS Forename,
Employee.SURNAME AS Surname,
Employee.LOCATION AS Location,
Employee.DEPARTMENT AS Department,
Employee.STARTDATE AS Startdate,
Sv_latest_appraisal.NEXT_APP AS Next_app,
Sv_latest_appraisal.USR_EAR_TYPE AS Usr_ear_type,
Sv_latest_appraisal.USR_EAR_TYPE_NEW AS Usr_ear_type_new,
CASE
WHEN Sv_latest_appraisal.NEXT_APP <= getdate() THEN 'OVERDUE'
WHEN Sv_latest_appraisal.NEXT_APP >= getdate() THEN 'NOT DUE'
WHEN Sv_latest_appraisal.NEXT_APP = getdate() THEN 'DUE TODAY'
ELSE 'UNKNOWN'
END
FROM (dbo.EMPLOYEE AS Employee
INNER JOIN dbo.SV_latest_appraisal
ON Employee.EMPLOY_REF = Sv_latest_appraisal.EMPLOY_REF)
INNER JOIN dbo.JOB AS Job
ON Employee.JOB_REF = Job.JOB_REF
WHERE (Employee.LEAVER = 0)AND (Employee.LOCATION LIKE 'GE')
View 20 Replies
View Related
Jan 22, 2015
I have a SQL Server 2005 table titled "Journeys" as follows:
+---------------+
| Journeys |
+---------------+
| JourneyID |
| PlateNumber |
| DepartureDate |
| DepartureKM |
| ArrivalDate |
| ArrivalKM |
+---------------+
The table contains the following sample data:
+------------+--------------+----------------+--------------+--------------+-----------+
| JOURNEYID | PLATENUMBER | DEPARTUREDATE | DEPARTUREKM | ARRIVALDATE | ARRIVALKM |
+------------+--------------+----------------+--------------+--------------+-----------+
| 1 | ABC-123 | 01-01-2015 | 10000 | 01-02-2015 | 10200 |
| 2 | ABC-123 | 01-02-2015 | 10210 | 01-03-2015 | 10500 |
| 3 | ABC-123 | 01-03-2015 | 10500 | 01-04-2015 | 10650 |
| 4 | ABC-123 | 01-04-2015 | 10607 | 01-05-2015 | 10900 |
| 5 | XYZ-999 | 01-15-2015 | 30200 | 01-16-2015 | 30400 |
| 6 | XYZ-999 | 01-16-2015 | 30405 | 01-17-2015 | 30600 |
| 7 | XYZ-999 | 01-17-2015 | 30600 | 01-18-2015 | 30750 |
| 8 | XYZ-999 | 01-18-2015 | 30752 | 01-19-2015 | 30920 |
+------------+--------------+----------------+--------------+--------------+-----------+
I want to generate a query that returns a the following results with an extra column named 'KMDifference' which is the difference between 'ArrivalKM' from last day and 'DepartureKM' from today.
Desired results:
+-------------+---------------+-------------+-------------+-----------+--------------+
| PlateNumber | DepartureDate | DepartureKM | ArrivalDate | ArrivalKM | KMDifference |
+-------------+---------------+-------------+-------------+-----------+--------------+
| ABC-123 | 01-01-2015 | 10000 | 01-02-2015 | 10200 | 0 |
| ABC-123 | 01-02-2015 | 10210 | 01-03-2015 | 10500 | 10 |
| ABC-123 | 01-03-2015 | 10500 | 01-04-2015 | 10650 | 0 |
| ABC-123 | 01-04-2015 | 10607 | 01-05-2015 | 10900 | 7 |
| XYZ-999 | 01-15-2015 | 30200 | 01-16-2015 | 30400 | 0 |
| XYZ-999 | 01-16-2015 | 30405 | 01-17-2015 | 30600 | 5 |
| XYZ-999 | 01-17-2015 | 30600 | 01-18-2015 | 30750 | 0 |
| XYZ-999 | 01-18-2015 | 30752 | 01-19-2015 | 30920 | 2 |
+-------------+---------------+-------------+-------------+-----------+--------------+
[URL]
View 4 Replies
View Related
Mar 5, 2015
I have this 2 tables:
Table1: Table2:
ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4I
need to find all differences between the 2 Tables.
A mismatch can be:
- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value
So I want to be returned:
ID Value ID Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4
There must be something simpler than the solution I came up with which is:select * from Table1 a full outer join Table2 b on a.Id = b.Id
WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)Martin
View 4 Replies
View Related
Dec 6, 2005
HEY,
CAN YOU TELL ME THE DIFFERENCES BETWEEN VARIOUS SQL
View 2 Replies
View Related
Oct 26, 2007
Dear Experts,
i've taken a backup from one database(which is running on sql server 2000) 'X' and restored it in another database(sqlserver 2005) as 'X' with the same name.
when i'm taking the backup, the size of the database is 59.63 MB (test database)
after restoring the same in my local machine, on sql server 2005, it became 158.25 MB.
why is this much of difference.....
any architectural difference...?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 1 Replies
View Related
Jan 30, 2008
Hi,
Can any body tell me the differences b/w BCP & BULK INSERT??
View 7 Replies
View Related
Jan 30, 2008
Hello All,
I am a newbie when it comes to MS SQL Server administration and I am hoping you can help me out. We are migrating from a shared webhosting platform to our own internal dedicated web / MS SQL (2005) server and have encountered an error that appears to be stemming form Query Syntax.
In our old system we could simply query via the following format:
"Select [Column] from [Table Name]"
But on the SQL Server I just set up we have to query via this format:
"Select [Column] from [Database Name].[Table Name]"
We have literally hundreds of preprogrammed Queries and it would be quite difficult to change them all. Does anyone know how I can set up SQL Server to run so that our quieries do not require the DatabaseName in the statement? I have placed the connection code below, if that helps any.
Set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB; Data Source =; Initial Catalog =; User Id =; Password="
Set objRec = Server.CreateObject("ADODB.Recordset").
View 10 Replies
View Related
Jul 20, 2005
Hello,I've got installed Win 2003 SBS Premium with the SQL Server 2000 on a servermachine. It works almost fine, except the application which uses the SQLServer. The main part of the application runs (since the last update) fine,but other tools of that application (database import and the databasemanager for check and rebuild) doesn't. They hang up or kill the database.Our software developer says that these problems are in correlation with theserver os. But there won't be any problem if we install Win 2000 Server andadd an additional SQL Server 2000.Finally my question is: Are there any differences between the SQL Server2000 Versions, which are sold (a) as a single product, (b) as part of theWin 2003 SBS Premium package and (c) as part of other Server versions?Thanks in Advance,Martin
View 6 Replies
View Related
Mar 27, 2008
In previous threads I saw that in a scenario where Log Shipping is active any other Log Backup activities should be avoided in order to let the Log Chain stay intanct.
Until now we just used Mirroring and Full Backup and Log Backup. Introducing Log Shipping to a third Server in a separate location would mean that the existing Log Backup Jobs must be removed or Log Shipping will not work.
I am glad to have read these threads before falling into that trap myself.
A question was raised by my colleague who is responsible for the System and Network Administration. Are these Log Backups that will be performed by Log Shipping just differential? He would not like to see that Log Shipping is pushing or pulling Log's that grow during time.
I named to him two reasons that it must be differential. A Log Backup truncates the existing Log which implies that the next Log is just the difference since the last Backup. Also, it must be differential because the Transations can only be committed once anyway, so Log Shipping implies that those Log Backups are just as big as the incoming Transactions since the last Log Backup.
Did I get this right?
View 3 Replies
View Related
Mar 12, 2007
Hi there,
I'm trying to find what are the main differences between SQL Server 2005 64-bits and 32-bits. So far, I've found some articles about TPC-C performance but I would like to know some response time or execution time of a batch or SSIS packages.
Any information about this 2 versions is appreciated.
Thanks!
View 3 Replies
View Related
Apr 7, 2008
I have a table that looks like this:
id, x
1 9.5
2 10
3 11
...
n x
and what I would like is to update this table with the differences of values in the x column (x[2]-x[1]) where the first difference would just be defined as 0.
result table would look like this:
id, x, dx
1 9.5 0 (where dx[1] defined to be 0)
2 10 0.5 (where dx[2] = 10-9.5 = 0.5)
3 11 1.0 (where dx[3]= 11-10 = 1.0)
...
Any suggestions on the best way to do this?
View 9 Replies
View Related
