September 22, 2015 at 2:03 pm
Hi All,
I'm looking for some advice with comparing times in the same field. I have two tables, one table has times of sessions (multiple rows) and the other contains the session ID, start & end times of overall session block and each session length. I am looking for a way to compare from the start of the session until the end all the times sequentialy and insert or mark all the sessions that fall between 10 min intervals.
i.e start 10:30 compare to 10:35 (no match), 10:30 to 10:40 (match 10:40 becomes new effective date), 10:40 to 10:45 (no match), 10:40 to 10:50 (match 10:50 new effective date).
I've attempted a couple of self join solutions but simply can't seem to get what I'm after. Any advice appreciated.
CREATE TABLE MyTable (
[id] INT identity NOT NULL,
[sess_id] INT NOT NULL,
[Time] datetime NOT NULL
)
GO
INSERT MyTable ( [sess_id], [Time])
VALUES ( 9999, CAST('2013-01-01 10:30' AS datetime))
, ( 9999, CAST('2013-01-01 10:35' AS datetime))
, ( 9999, CAST('2013-01-01 10:40' AS datetime))
, ( 9999, CAST('2013-01-01 10:45' AS datetime))
, ( 9999, CAST('2013-01-01 10:50' AS datetime))
, ( 9999, CAST('2013-01-01 11:00' AS datetime))
, ( 9999, CAST('2013-01-01 11:10' AS datetime))
, ( 9999, CAST('2013-01-01 11:15' AS datetime))
, ( 9999, CAST('2013-01-01 11:20' AS datetime))
, ( 9999, CAST('2013-01-01 11:30' AS datetime))
CREATE TABLE MyTable2 (
[sess_id] INT NOT NULL,
[length] int NOT NULL,
[startTime] datetime NOT NULL,
[endTime] datetime NOT NULL
)
INSERT MyTable2 ( [sess_id],[length], [startTime],[endTime])
VALUES ( 9999,10, CAST('2013-01-01 10:30' AS datetime),CAST('2013-01-01 11:30' AS datetime))
September 22, 2015 at 2:16 pm
Paul Munter (9/22/2015)
Hi All,I'm looking for some advice with comparing times in the same field. I have two tables, one table has times of sessions (multiple rows) and the other contains the session ID, start & end times of overall session block and each session length. I am looking for a way to compare from the start of the session until the end all the times sequentialy and insert or mark all the sessions that fall between 10 min intervals.
i.e start 10:30 compare to 10:35 (no match), 10:30 to 10:40 (match 10:40 becomes new effective date), 10:40 to 10:45 (no match), 10:40 to 10:50 (match 10:50 new effective date).
I've attempted a couple of self join solutions but simply can't seem to get what I'm after. Any advice appreciated.
CREATE TABLE MyTable (
[id] INT identity NOT NULL,
[sess_id] INT NOT NULL,
[Time] datetime NOT NULL
)
GO
INSERT MyTable ( [sess_id], [Time])
VALUES ( 9999, CAST('2013-01-01 10:30' AS datetime))
, ( 9999, CAST('2013-01-01 10:35' AS datetime))
, ( 9999, CAST('2013-01-01 10:40' AS datetime))
, ( 9999, CAST('2013-01-01 10:45' AS datetime))
, ( 9999, CAST('2013-01-01 10:50' AS datetime))
, ( 9999, CAST('2013-01-01 11:00' AS datetime))
, ( 9999, CAST('2013-01-01 11:10' AS datetime))
, ( 9999, CAST('2013-01-01 11:15' AS datetime))
, ( 9999, CAST('2013-01-01 11:20' AS datetime))
, ( 9999, CAST('2013-01-01 11:30' AS datetime))
CREATE TABLE MyTable2 (
[sess_id] INT NOT NULL,
[length] int NOT NULL,
[startTime] datetime NOT NULL,
[endTime] datetime NOT NULL
)
INSERT MyTable2 ( [sess_id],[length], [startTime],[endTime])
VALUES ( 9999,10, CAST('2013-01-01 10:30' AS datetime),CAST('2013-01-01 11:30' AS datetime))
I don't understand. Can you explain the business rules more clearly? Also it would help if you could post the desired output based on your sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 22, 2015 at 4:35 pm
Apologies if I was unclear, we have a master schedule table which tells us the start time and end time of the overall schedule and the length of a session within the schedule. This table has one row per schedule, the transaction table has multiple sessions that belong to a schedule with start times but alas no end times else its a datediff where the output = length.
We need to extract only the sessions that are the equal to the length defined on the master schedule table t. In this case we have sessions which are being generated at 5 min and 10 min intervals but we are only interested in the ones that happen 10 min apart. I've attached a screen shot of desired output.
Any thoughts appreciated.
September 22, 2015 at 5:13 pm
A few questions:
1. Is Length always in minutes?
2. Wouldn't you exclude Id 10 since that individual session starts at 11:30 which is the end of the overall session and therefore falls outside of the range?
3. Are the session times in MyTable always rounded to five minutes? whole minutes?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 22, 2015 at 8:52 pm
Paul Munter (9/22/2015)
We need to extract only the sessions that are the equal to the length defined on the master schedule table. In this case we have sessions which are being generated at 5 min and 10 min intervals but we are only interested in the ones that happen 10 min apart. I've attached a screen shot of desired output.
This is a simplistic shot at what I think you want. You did not provide a master schedule table so I made one up for testing.
create table MasterSchedule
(
sess_id int,
interval int
)
INSERT MasterSchedule VALUES(9999, 10)
select * , datepart(mi,[mt.time])
from MyTable mt
inner join MasterSchedule m on m.sess_id = mt.sess_id
where datepart(mi, [mt.time]) % m.interval = 0
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 23, 2015 at 12:51 am
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.MyTable' ) IS NOT NULL DROP TABLE dbo.MyTable ;
IF OBJECT_ID(N'dbo.MyTable2') IS NOT NULL DROP TABLE dbo.MyTable2;
CREATE TABLE dbo.MyTable (
[id] INT identity NOT NULL,
[sess_id] INT NOT NULL,
[Time] datetime NOT NULL
);
CREATE TABLE dbo.MyTable2 (
[sess_id] INT NOT NULL,
[length] int NOT NULL,
[startTime] datetime NOT NULL,
[endTime] datetime NOT NULL
);
INSERT INTO dbo.MyTable ( [sess_id], [Time])
VALUES ( 9999, CAST('2013-01-01 10:30' AS datetime))
, ( 9999, CAST('2013-01-01 10:35' AS datetime))
, ( 9999, CAST('2013-01-01 10:40' AS datetime))
, ( 9999, CAST('2013-01-01 10:45' AS datetime))
, ( 9999, CAST('2013-01-01 10:50' AS datetime))
, ( 9999, CAST('2013-01-01 11:00' AS datetime))
, ( 9999, CAST('2013-01-01 11:10' AS datetime))
, ( 9999, CAST('2013-01-01 11:15' AS datetime))
, ( 9999, CAST('2013-01-01 11:20' AS datetime))
, ( 9999, CAST('2013-01-01 11:30' AS datetime));
INSERT INTO dbo.MyTable2 ( [sess_id],[length], [startTime],[endTime])
VALUES ( 9999,10, CAST('2013-01-01 10:30' AS datetime),CAST('2013-01-01 11:30' AS datetime));
;WITH BASE_DATA AS
(
SELECT
MT.id
,MT.sess_id
,MT.Time
,MT2.startTime
,MT2.endTime
,ABS(DATEDIFF(MINUTE,MT2.startTime,MT.Time) % 10)
+ ABS(DATEDIFF(MINUTE,MT.Time,MT2.endTime) % 10) AS DECDIFF
FROM dbo.MyTable MT
CROSS APPLY dbo.MyTable2 MT2
WHERE MT.sess_id = MT2.sess_id
)
SELECT
BD.id
,BD.sess_id
,BD.Time
FROM BASE_DATA BD
WHERE BD.DECDIFF = 0;
Results
id sess_id Time
----------- ----------- -----------------------
1 9999 2013-01-01 10:30:00.000
3 9999 2013-01-01 10:40:00.000
5 9999 2013-01-01 10:50:00.000
6 9999 2013-01-01 11:00:00.000
7 9999 2013-01-01 11:10:00.000
9 9999 2013-01-01 11:20:00.000
10 9999 2013-01-01 11:30:00.000
September 23, 2015 at 8:25 am
Length is always in minutes, thanks for the input folks very much appreciated. Will try the advice above.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply