Finding Differences Between Dates In A Single Colu
Feb 28, 2008
I have a log table that displays the history of the status of an entity and the date the status changed.
member_id | modification_date | status
I wish to find the differences between datetime values in different rows of the modification_date column. I would need the amount of time between whatever the next sequential time was for the member. I would also need to know the status change in that time. I don't know how to do this with just one date column.
Any assistance is appreciated.
Thank you.
View 2 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
Mar 18, 2008
Any suggestions on how I would find the 2nd and 4th tuesday of every month?
View 5 Replies
View Related
Sep 7, 2014
I have a table that contains activity dates. If a certain activity occurs 3 times within fifteen days, I need to flag them that account.What is the best approach?
View 1 Replies
View Related
Mar 25, 2014
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)
INSERT INTO #SessionTest
SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL
SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL
SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL
SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL
SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
[code]....
View 6 Replies
View Related
Jun 6, 2008
Friends
I'm using Sql Server 2005, in which I've table like this
USE [Sample]
GO
/****** Object: Table [dbo].[Table1] Script Date: 06/07/2008 03:10:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpID] [int] NULL
) ON [PRIMARY]
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)
My task is I want to find Missing Dates (Except Saturday, Sunday)
for each Employee.
Note: Missing Dates should be Working Days only (Excluding Saturday & Sunday)
Help me out
Thanks
Rajesh N.
View 6 Replies
View Related
Mar 27, 2008
I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.
Any help would be greatly appreciated.
Thanks
JP
View 6 Replies
View Related
Mar 28, 2000
I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.
Thanks in advance
View 1 Replies
View Related
Nov 27, 1999
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
View 1 Replies
View Related
Nov 27, 1999
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
View 1 Replies
View Related
Aug 9, 2007
I have a table with several million rows of data. There is a date field defined as a char(8) with some bad rows. i tried to locate them with below
select date_stopped from patient_medication
where isdate(convert(datetime,date_stopped)) = 1
This won't work, I get the
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Any way around this?
View 2 Replies
View Related
Aug 22, 2007
Hi
I have two below datacolumns
'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)
AS
Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'
from tablename
where ......
Advance thanks
View 3 Replies
View Related
Oct 10, 2007
Hi all,
How to update a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type
How to DELETE a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type
how to INSERT a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type
update XmlCatalog1 set Document1.modify('delete /X12_U1_837/X12_Q1_837/header/ISA//ISA_Authorization_Information_Qualifier') where id=2
----------
The error which i am getting is
XML Validation: Invalid content. Expected element(s):ISA_Authorization_Information_Qualifier where element 'ISA_Authorization_Information' was specified. Location: /*:X12_U1_837[1]/*:X12_Q1_837[1]/*:header[1]/*:ISA[1]/*:ISA_Authorization_Information[1]
View 1 Replies
View Related
Apr 21, 2015
I am trying to find an easy way to create multiple of just two date in a single sql statement.
E.G.
A statement using the parameters
@StartDate = '2015-01-01'
@EndDate = '2015-01-05'
Ends up with rows:
'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'
What would be the best way to do this ?
View 3 Replies
View Related
Oct 13, 2015
The data I have is as follows -
ID1 ID2 Date Action
100 500 09/08/14 Open
100 500 09/24/14 Close
101 510 07/10/15 Open
101 510 07/19/15 Close
The output I want in a single result set is -
ID1 ID2 Open_Date Close_Date
100 500 09/08/14 09/24/14
101 510 07/10/15 07/19/15
Any way to do this in T-SQL .
View 10 Replies
View Related
May 24, 2015
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()
I'm guessing some sort of recursion is required here to produce this.
View 7 Replies
View Related
Mar 2, 2006
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
View 3 Replies
View Related
Jul 18, 2014
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
View 9 Replies
View Related
Feb 28, 2015
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
View 7 Replies
View Related
Jun 21, 2005
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 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
