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.
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?
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:
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
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)
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.
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.
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
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
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
'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,'
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]
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?
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,
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.
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?
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.
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:
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
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
/* 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'
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
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.
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.
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).
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.
