May 30, 2019 at 3:52 pm
Hi I have a table shown below
Hi,
I have a table shown below and I have to check a time to see if it is in the table.
So for example, I might have 10:02:00 and I want to see if it is in here. So in SQL I would check each row
is 10:02:00 >= 9:28:13 and <= 9:32:32 and so on.
I can only see to do this with a Cursor, does anyone have a better way?
And keep in mind please, I will have multiple dates to do this with.
Thank you
Date start talkend talk
5/29/20199:28:13 AM9:32:32 AM
5/29/20199:52:27 AM9:53:15 AM
5/29/20199:55:01 AM9:57:33 AM
5/29/201910:10:01 AM10:10:12 AM
5/29/201910:14:38 AM10:16:55 AM
5/29/201910:40:02 AM10:44:49 AM
5/29/201910:45:37 AM10:49:49 AM
5/29/201910:53:25 AM10:54:47 AM
5/29/201911:02:38 AM11:04:23 AM
5/29/201911:03:52 AM11:05:42 AM
5/29/201911:08:12 AM11:09:15 AM
5/29/201911:08:38 AM11:10:14 AM
5/29/201911:10:17 AM11:11:16 AM
5/29/201911:33:47 AM11:35:01 AM
5/29/201911:51:35 AM11:52:52 AM
5/29/201912:03:23 PM12:04:21 PM
5/29/201912:09:15 PM12:10:31 PM
5/29/201912:10:33 PM12:11:48 PM
5/29/201912:46:19 PM12:47:52 PM
5/29/201912:56:36 PM12:58:49 PM
May 30, 2019 at 4:31 pm
SELECT ...
FROM ...
WHERE @SomeTime >= StartTalk AND @SomeTime <= EndTalk
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 30, 2019 at 5:09 pm
Thanks for the reply, but I can not do that.
I am taking a time like 10:02:00 and looping through the table above and checking this for each row; like I said the only way I know is through a cursor. I am hoping someone knows a better way.
Thank you
May 30, 2019 at 5:23 pm
Thanks for the reply, but I can not do that. I am taking a time like 10:02:00 and looping through the table above and checking this for each row; like I said the only way I know is through a cursor. I am hoping someone knows a better way. Thank you
There is nothing in your description of the problem that would preclude you from using Phil's solution. Is there something that you've left out of your description?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 30, 2019 at 5:59 pm
Hi,
I am sorry maybe I am misunderstanding it, I will try putting it in and see what happens thanks, guys.
May 30, 2019 at 6:06 pm
I see what I did not make clear, sorry about that, I had this in my question "And keep in mind please, I will have multiple dates to do this with." which I should have made clearer; sorry.
So basically, I am looping through on table to get a time, and for each record (time) in that table, I will check to see if it is in the second table something like as follows :
select time
from ##TempMissedTable
Where time NOT IN (
select time
from ##TempAnnasTime
where Start_Time >= '09:28:13.0000'
AND End_Time <= '09:32:32.0500')
May 30, 2019 at 8:36 pm
IF OBJECT_ID('tempdb..#myTable') IS NOT NULL BEGIN DROP TABLE #myTable END
IF OBJECT_ID('tempdb..#newTable') IS NOT NULL BEGIN DROP TABLE #newTable END
CREATE TABLE #myTable ([Date] date,[start talk] datetime,[end talk] datetime)
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 9:28:13 AM','5/29/2019 9:32:32 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 9:52:27 AM','5/29/2019 9:53:15 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 9:55:01 AM','5/29/2019 9:57:33 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 10:10:01 AM','5/29/2019 10:10:12 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 10:14:38 AM','5/29/2019 10:16:55 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 10:40:02 AM','5/29/2019 10:44:49 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 10:45:37 AM','5/29/2019 10:49:49 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 10:53:25 AM','5/29/2019 10:54:47 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:02:38 AM','5/29/2019 11:04:23 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:03:52 AM','5/29/2019 11:05:42 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:08:12 AM','5/29/2019 11:09:15 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:08:38 AM','5/29/2019 11:10:14 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:10:17 AM','5/29/2019 11:11:16 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:33:47 AM','5/29/2019 11:35:01 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 11:51:35 AM','5/29/2019 11:52:52 AM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 12:03:23 PM','5/29/2019 12:04:21 PM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 12:09:15 PM','5/29/2019 12:10:31 PM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 12:10:33 PM','5/29/2019 12:11:48 PM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 12:46:19 PM','5/29/2019 12:47:52 PM')
INSERT INTO #myTable ([Date],[start talk],[end talk]) VALUES ('5/29/2019','5/29/2019 12:56:36 PM','5/29/2019 12:58:49 PM')
SELECT *
FROM #myTable
--now cheat and create dummy test values for whatever table you're checking FROM:
SELECT [Date],DATEADD(mi,2,[start talk]) AS [start talk],dateadd(mi,2,[end talk]) AS [end talk]
INTO #newTable
FROM #myTable
SELECT *
FROM #newTable
--now find the ones that started within the spans from #myTable
SELECT *
FROM #myTable t1
LEFT JOIN #newTable t2 ON t2.[start talk] >= t1.[start talk] AND t2.[start talk] <= t1.[end talk]
WHERE t2.[start talk] IS NOT NULL-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 31, 2019 at 11:42 am
Thanks I got this to work, and this really helped.
Thank you
May 31, 2019 at 12:06 pm
Glad to hear it. FYI, if you can provide the data like you did, but scripted out like I did so it's just a copy/paste to get it into a temp table, folks will be much more likely to provide testable solutions for you. Makes it easy to help, not that this one was super hard to get into a table
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply