Would like to know if it is possible to calculate the duration of a Datetime Start and End Dates ignoring all overlapps?
Eg:
1) StartTime 10:00:00 EndTime 11:00:00 Duration: 01:00:00
2) StartTime 10:30:00 EndTime 11:15:00 Duration: 00:45:00
Total Duration should be 01:15:00 and not 01:45:00
Hi,What I have is a booking table, and when updating/inserting I need toensure that there are no date/time overlaps. The problem I'm having isthat while the following script works for events on the same day, itfails miserably when a booking starts on a previous day.I've just spent the last hour going through previous posts and justcan't seem to it right.My DB structure (Sql Server 2000):Table: CollateralBooking-- CBID - int, identity(1, 1)-- CBcPartNumber - varchar(50) (foreign key)-- CBdDateTimeFrom - smalldatetime-- CBdDateTimeTo - smalldatetime-- CBcAlias - varchar(50) (foreign key)My current script (in a stored proc):IF (SELECT COUNT(*) FROM CollateralBookingWHERE (((@CBdDateTimeFrom > CBdDateTimeFrom) AND (@CBdDateTimeFrom < CBdDateTimeTo))OR ((@CBdDateTimeTo > CBdDateTimeFrom) AND (@CBdDateTimeTo < CBdDateTimeTo)))AND (CBcPartNumber = @CBcPartNumber)) <> 0BEGIN-- Return an error.END-- ... Other checks & finally, the insert/update.--Posted via http://dbforums.com
View 2 Replies View RelatedI have a dataset (DATA) like this-
Store Start End Type
XXXX 02-03-2015 10:04:00 02-03-2015 10:08:00 1
XXXX 02-03-2015 10:06:00 02-03-2015 10:10:00 2
XXXX 02-03-2015 10:09:30 02-03-2015 10:12:00 1
YYYY 03-03-2015 20:04:00 03-03-2015 20:12:00 1
YYYY 03-03-2015 20:06:00 03-03-2015 20:10:00 2
YYYY 03-03-2015 20:09:00 03-03-2015 20:16:00 1
YYYY 03-03-2015 20:15:00 03-03-2015 20:18:00 2
YYYY 03-03-2015 20:17:00 03-03-2015 20:22:00 2
YYYY 03-03-2015 20:21:00 03-03-2015 20:27:00 1
The output of this file (RESULT) is-
Store Start End Mins of Type 2 only
XXXX 02-03-2015 10:04:00 02-03-2015 10:12:00 00:01:30
YYYY 02-03-2015 20:04:00 02-03-2015 20:27:00 00:05:00
So for each Store (Store is unique in the table), I am rolling up the intervals with overlaps to create a single interval.
Now, for each store, I want to find the time period for purely type 2. So if there is an overlap, type 1 has the dominance. And I want the sum of time period of whatever is left for type 2.
I have written this code but not able to address the overlap issue:
alter table [DATA] add Outage float;
update [DATA]
set Outage = DATEDIFF(SECOND,[Start],[END])
alter table [RESULT] add [Outage_Type1 (%)] float,[Outage_Type2 (%)] float;
[Code] ....
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here  )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Hi,
I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @Startdate is fine because the timestamps is at 12:00:00AM but my @EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.
If someone can help me out with, I would greatly appreciate it.
Thanks,
I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:
Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)
I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.
Can anyone help me get started in tackling this problem or provide any examples?
In my example, i have a table as below:
Time|Machine
2013-08-05 10:12:00 |M1
2013-08-05 10:15:00 |M1
2013-08-05 10:20:00 |M1
2013-08-05 10:11:00 |M2
2013-08-05 10:15:00 |M2
2013-08-05 10:16:00 |M2
2013-08-05 10:17:00 |M2
2013-08-06 11:12:00 |M1
2013-08-05 11:42:00 |M1
2013-08-05 12:02:00 |M1
I want to have a report for every machine by time that will show how long each machine worked. The result is something like below:
M1|8 (minutes)
M2|6
M1|50
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process.
I have a table that consists of: Order#, Processes, Time_In, Time_Out.
Order# can be 1, 2, 3, 4, 5.
While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping.
Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process.
For each process we will have recorded time when the order# comes in and when it finishes with each process.
I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
I.E.:
Order# | Process | Time_In | Time_out
1 | Receiving | 2007-12-1 10:00:00.000 | 2007-12-1 10:10:00.000
1 | Incoming Q.A. | 2007-12-1 10:40:00.000 | 2007-12-1 11:42:00.000
1 | Visual Check | 2007-12-2 08:10:00.000 | 2007-12-2 11:00:00.000
1 | Shipping | 2007-12-2 11:20:00.000 | 2007-12-2 11:52:00.000
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
Please help.
Thanks in advance.
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process.
I have a table that consists of: Order#, Processes, Time_In, Time_Out.
Order# can be 1, 2, 3, 4, 5.
While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping.
Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process.
For each process we will have recorded time when the order# comes in and when it finishes with each process.
I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
I.E.:
Order# | Process | Time_In | Time_out
1 | Receiving | 2007-12-1 10:00:00.000 | 2007-12-1 10:10:00.000
1 | Incoming Q.A. | 2007-12-1 10:40:00.000 | 2007-12-1 11:42:00.000
1 | Visual Check | 2007-12-2 08:10:00.000 | 2007-12-2 11:00:00.000
1 | Shipping | 2007-12-2 11:20:00.000 | 2007-12-2 11:52:00.000
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
2 | xxxxx | xxxxx | xxxxx
Please help.
Thanks in advance.
i've got an excel spreadsheet generated everyday which stores the duration of talk time as 0:02:09
When i use the Integration Services Package to move the data into a table. I've used datetime column. It comes up as 1899-12-30 00:00:02:09.000
What's best practice to store duration as in a SQL Server table? or should i convert it to seconds.
Hello, everyone:
I have a customer time duration format like "12:15" that means 12 minutes and 15 seconds. I want to convert to be "12.25 " that means 12.25 minutes. Any suggestion please. Thanks a lot.
ZYT
Hi all,
Thanks for setting up such a great site and forum.
Here is my problem:
I have a table like the following in SQL Server 2005:
order | taskid | main_person | temp_person | start_assign_date
1 | 3 | John | John | 2008-01-01 10:20:22
2 | 3 | John | Joe | 2008-02-05 15:20:22
3 | 3 | John | John | 2008-02-07 20:25:20
4 | 6 | Joe | Joe | 2008-01-01 10:20:22
5 | 6 | Joe | Mike | 2008-02-01 10:20:22
6 | 10 | Doug | Doug | 2008-01-01 10:20:22
7 | 7 | Russ | Russ | 2008-02-01 11:20:22
8 | 7 | Russ | Mike | 2008-02-08 12:20:22
9 | 7 | Russ | John | 2008-02-10 20:05:12
It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.
I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long
Something like this:
From 2008-##-## to 2008-##-##
Task 3 - John - 15 days
Task 3 - Joe - 5 days
Task 6 - Joe - 18 days
Task 6 - Mike - 2 days
Task 10 - Doug - 20 days
I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.
I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.
Any hint or comment would be appreciated.
Thanks
Sami
I am working with a proprietary database that records the date, time,location, and speed of a vehicle. It is pulling this information fromGPS unit tied to a vehicle. The table is populated with values thatare pulled from the GPS unit every 30 seconds. I need to find theduration of time for when a vehicle is stopped. I have created acursor that runs though all of the tables, and gathers the data forwhen the vehicle's speed is equal to zero. I have inserted this datainto a temp table, where I am having a problem is identifying theduration of time the vehicle is stopped. I cannot figure out how toquery the table and grab the first row when the speed is zero and thelast row where the speed is zero.The data looks like this…Date TimeVehIDLatLongSpeed12/31/2003 66144 295708535220842.92747-83.63003012/31/2003 66159 295708535220842.92696-83.62935012/31/2003 66179 295708535220842.9271 -83.62902 012/31/2003 66269 295708535220842.92709-83.62903012/31/2003 66359 295708535220842.9271 -83.62901012/31/2003 66449 295708535220842.92709-83.62904012/31/2003 66539 295708535220842.92708-83.62903012/31/2003 66629 295708535220842.92708-83.629 012/31/2003 66719 295708535220842.92708-83.62903012/31/2003 67414 295708535220842.9269 -83.630920Any help would be greatly appreciated…Thanks,Dave
View 5 Replies View RelatedI have the following SQL query
SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]
[code]....
Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.
Below is the create table schema and data
GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,
[code]....
i am trying to calculate the time that is spend by user on the internet per site. the file that we receive from the WebProxLog contains the following fields ClientIP, ClientUname,logdate,logTime, ProcessingTime, DestinationHost,DestinationIP. I want to check per user per site how much time the user spend per date. Now my challenge is that if the user connects to the side today and logout and reconnects again to the same side how do i calculate the time spend on the site because the file does not show the logout time
I have a table with following data
(Id, date ,time)
11 2015/8/1
12:20:00
11
2015/8/1 12:21:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
11:21:00
12 2015/8/1
18:10:20
I need the table with following record only
(Id, date ,time)
11 2015/8/1
12:20:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
18:10:20
I have a problem with date/time:
1. I have data something like this:
start 07.30
end 16.00
How can i count how many hours and minutes from start to end?
2. Another data
start: 20050805 -> August 5, 2005
end: 20050810
How can I return value that when i insert 20050809 it is between start and end, and if 20050811 it will say false that the date is between start and end
thx
I have two table one with employee time in out. Employee is having two shift
[EMPID][Date] [Time]
818401/01/201410:04
818401/01/201411:48
818401/01/201416:17
818401/01/201422:20
Second table shows Schedule for employee
[EmpID][IN1] [OUT1] [IN2] [OUT2]
818510:00:0020:00:0015:00:0020:00:00
How do i get the detail in this format
EmpID Date In1 out1 In2 out2
8185 01/01/2014 10:04 11:48 16:17 22:20
Hello All,
I have some difficulties with TSQL where i need to calculate time between two records in seconds.
Records look like this:
ID IP DATE CHANNEL_ID #CALCTIME
1 10.132.184.226 12/06/2007 08:00:00 406 35
2 10.96.121.230 12/06/2007 08:00:10 1 45
3 10.128.242.214 12/06/2007 08:00:20 4 39
4 10.132.184.226 12/06/2007 08:00:30 2
5 10.96.121.230 12/06/2007 08:00:55 3
6 10.96.121.214 12/06/2007 08:00:59 3 21
7 10.96.121.214 12/06/2007 08:01:20 4
.........................................................................................................
Basically i would need to calculate the time between 2 records with the same IP (next one with the same IP) and calculate the time between the DATE which is basically attached to the first record. As you can see the first #CALCTIME is done between the ID #1 and ID#4 which means that the time difference must be done between the current and the next record.
Really appreciate your help!
Thank you!
Sebastijan
Hi,
I get a no. of seconds (like '33450', varchar) for each day and I have a day field (like '19.10.2004', varchar).
How can I easily convert it into a datetime-field (like 2004-10-19 09:17:50) ?
Does anybody has an idea ?
:confused:
I am trying to produce a report that will show a duration in minutes of a time when a room was occupied for a category. Whilst I have the start and end dates and times, the end user must be able to specify not only a range of dates, but a start and endtime of hours in the day in which they are interested in (it will be applied to all days in the range - they are not allowed to specify a different start/endtime per day).
The example I have is a date range of 6 to 17 October, but they only want the times from 09:00 to 21:00, so if a room was occupied from 08:00 to 11:00 they would only want to know the duration as 120 minutes (09:00 to 11:00) not 180.
The data is supplied by a third party, and duration in minutes is supplied, but it is not much use when they are not interested in the 'real' duration.
CREATE TABLE [dbo].[Evts](
[Location_name] [nvarchar](200) NULL,
[Event_Category] [nvarchar](500) NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Duration] [int] NULL
[Code] ....
---but the expected output is
Location_nameEvent_Categoryduration
RS8CM240
RS8OD480 -- eliminating 08:00 - 09:00 on 17th
RS8OOT300 -- eliminating 08:00 - 09:00 on 13th
RS8SCC150
RS8SODT1740
RS8 SODT180
RS8SODTB60
RS8SODTNT180
RS8TR60
Looking for info regarding to the minute calculation when the start time specified by the user differs from that of the actual startime?
Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.
Any help is appreciated.
I am having problems building a CTE. I have a table:
declare @tbl table ( ID int, S time(0), E time(0) )
with the following data:
insert@tbl ( ID, S, E )
values ( 1, '03:00', '05:00' ),
( 1, '04:00', '06:30' ),
( 1, '22:00', '23:00' ),
( 1, '23:00', '23:40' ),
( 1, '23:30', '23:45' )
I need to build a CTE that will eliminate overlaps and produce the following result:
ID S E
-- --------- ---------
1 03:00:00 06:30:00
1 22:00:00 23:45:00
Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
I'm importing from an Excel spreadsheet into an SQL Table using DTS.
Two things:
1. I don't want anything imported into Column 1 of the table as this has been designed to automatically increment (creating a primary key). I haven't worked out how to do this.
2. How can I get the DTS package to ignore the top 2 rows of the Excel spreadsheet (header information)?
An answer to one will be great but both will be tremendous!!
All,
So I have this query that is ignoring my date filter and for the life of me I can't figure out why. Was hoping some guru could explain it to me. Here goes:
This query ignores my date filter:
SELECT
rcv.Name AS MachineName,
r.JobId,
j.Name AS JobName,
r.CreateTime AS JCreateTime,
rsv.Name AS JResultStatus,
rpv.Path + rpv.Name AS ResourcePool,
rcvv.ResourceConfigurationVal AS Dimension
FROM
dbo.Result_View AS r
INNER JOIN dbo.ResourceConfiguration_View AS rcv ON r.ResourceConfigurationId = rcv.Id
INNER JOIN dbo.Job_View AS j ON r.JobId = j.Id
INNER JOIN dbo.ResultStatus_View AS rsv ON r.ResultStatusId = rsv.Id
INNER JOIN dbo.Resource_View AS rv ON rcv.ResourceId = rv.Id
INNER JOIN dbo.ResourcePool_View AS rpv ON rv.ResourcePoolId = rpv.Id
RIGHT JOIN dbo.ResourceConfigurationValue_View AS rcvv ON rv.LatestResourceConfigurationId = rcvv.ResourceConfigurationId
LEFT JOIN dbo.Dimension_View AS d ON rcvv.DimensionId = d.Id
WHERE
(r.CreateTime > DATEADD(DAY, -15, GETDATE()))
AND
(rcv.Name LIKE 'PNP%')
AND
(d.Id = 859)
OR
(d.Id = 860)
If I comment out the last two joins and associated select/filters, all of the sudden the date filter works again. From everything I have read, the joins are supposed to be processed BEFORE the filters are applied in the virtual table. My DB goes back a number of years and contains millions of records. W/O the date filter, the query takes a very, very long time to run.
Any ideas on why this would happen?
Thanks,
Allen
Hi,
View 1 Replies View RelatedI have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want.
I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code.
Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables.
Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC
If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order.
When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order.
Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly?
Regards, Robert Barnes
I am using the SqlDataSource to access the dB from my page. Basically this is what I do with it ds.SelectParameters.Clear();
ds.SelectParameters("Id", TypeCode.Int32, id.ToString());
DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.MaximumRows = 1;
dssa.AddSupportedCapabilities(DataSourceCapabilities.Page);
DataView dv = (DataView)ds.Select(dssa);
if (dv.Count > 0)
{
// collect the information
string title = (string)dv[index].Row.ItemArray[0];
}
And the SelectCommand attribute of the SqlDataSource is set in design mode to "SELECT * from vw_Items ORDER BY Category".
So, since I am trying to retrieve just the item with the given Id I was expecting just one record but when I step through I see that the data view has a count of 9 (all records in the table) !!!
What am I doing wrong here? why can't it return just one? as per the select statement which after adding the parameter should be something like "SELECT * FROM vw_Items WHERE ID = 5 ORDER BY Category
If you have a table called Person and has ID, Age, Gender and Name. And you have a stored procedure:
CREATE PROCEDURE [dbo].[GetName]@Age TINYINT,@Gender TINYINTASBEGIN SELECT Name FROM Person WHERE Age = @Age AND Gender = @Gender END
You want some times to send @Age as null to get all ages (or in other words, you are not concerned with the age) or @Gender as null to get all genders.
A way to do that is to build your query string dynamically and execute it with EXEC command. However, this is a bad solution because you are losing most of the SP benefits and writting longer and more complicated procedures, specially that you have a lot of parameters!
Another solution that I thought of is having your select like this: SELECT Name FROM Person WHERE Age = ISNULL(@Age,Age) AND Gender = ISNULL(@Gender, Gender)
However, I noticed that when the value of the column is NULL, you will get false results, obviously because you are using = operator in comparing a value with null while you should be using IS operator, so this method did not work.
I am wondering if any one has a good solution for this.
Adam Tibi
In our schools we have a number of East-European, Turkish, Scandinavian, ... students. Their names contain "special" characters, like Ö, Ü, Ø, ... Our users want to be able to search for student names without having to enter those special characters. Most often they don't know the exact spelling of the names and they get "no match found" messages as a result.
They want to have persons with the name Ösgür, Osgueld, ... in the result set after entering "osgu" in the search field.
What is the best way to do this? I was thinking about using another collation near the LIKE, but I don't know if that would work and how it should be done. The Database collation is Latin1_General_CI_AS.
Hi,
I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.
Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.
Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part
Here is the beginning of the Transactions table create script
CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,
The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).
Here is an example of what I get.
130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).
068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072