July 6, 2012 at 8:28 am
Hi guys,
Because I haven't find an obvius solution let me explain my issue.
I've a table "dbo.Trend_15min_Info" like this:
Table_NameTable_Start_Time Table_End_Time Table_Index
------------------------------------------------------------------------------------------
Trend_15min_12012-07-06 08:16:30.000 2012-07-06 09:27:37.0001
Trend_15min_2 2012-07-06 00:16:30.000 2012-07-06 02:01:30.000 2
Trend_15min_3 2012-07-06 02:16:30.000 2012-07-06 04:01:30.000 3
Trend_15min_4 2012-07-06 04:16:30.000 2012-07-06 06:01:30.000 4
Trend_15min_5 2012-07-06 06:16:30.000 2012-07-06 08:01:30.000 5
I need to find the table index corresponding to a specific range. Example:
Range 2012-07-06 03:01:00 to 2012-07-06 8:20:00 should return Table Index 3,4,5 and 1.
Range 2012-07-06 00:17:00 to 2012-07-06 2:20:00 should return Table Index 2 and 3
Also if the range is not full complete
Range 2012-07-06 00:00:00 to 2012-07-06 2:20:00 should return Table Index 2 and 3
I began with this, but it didn't work :
Select Table_Index FROM dbo.Trend_15min_Info WHERE Table_Start_Time between '2012-07-06 03:01' and '2012-07-06 08:20'
There is another solution ; test for each row if the both date of my range is between Table_Start_Time and Table_End_Time, but I wonder if there is an SQL statement made for my needs.
Thanks,
July 6, 2012 at 8:39 am
You're on the right path here, just need to handle both possibilities (start time between dates, or end time between dates)
CREATE TABLE #Table_Index(
Table_Name VARCHAR(25),
Table_Start_Time DATETIME,
Table_End_Time DATETIME,
Table_Index INT)
INSERT #Table_Index
(Table_Name,
Table_Start_Time,
Table_End_Time,
Table_Index)
VALUES
('Trend_15min_1','2012-07-06 08:16:30.000','2012-07-06 09:27:37.000',1 ),
('Trend_15min_2','2012-07-06 00:16:30.000','2012-07-06 02:01:30.000',2),
('Trend_15min_3','2012-07-06 02:16:30.000','2012-07-06 04:01:30.000',3),
('Trend_15min_4','2012-07-06 04:16:30.000','2012-07-06 06:01:30.000',4),
('Trend_15min_5','2012-07-06 06:16:30.000','2012-07-06 08:01:30.000',5 )
Select Table_Index FROM #Table_Index WHERE Table_Start_Time between '2012-07-06 03:01' and '2012-07-06 08:20'
OR Table_End_time between '2012-07-06 03:01' and '2012-07-06 08:20'
July 6, 2012 at 10:53 am
Thanks, Sometime I juste need a little help and you did it !
July 6, 2012 at 11:00 am
Have to agree, as I came up with the same code:
DECLARE @StartRange DATETIME = '2012-07-06 03:01:00';
DECLARE @EndRange DATETIME = '2012-07-06 8:20:00';
WITH SampleData AS (
SELECT
TableName,
CAST(TableStartTime AS DATETIME) TableStartTime,
CAST(TableEndTime AS DATETIME) TableEndTime,
TableIndex
FROM
(VALUES ('Trend_15min_1','2012-07-06 08:16:30.000','2012-07-06 09:27:37.000',1),
('Trend_15min_2','2012-07-06 00:16:30.000','2012-07-06 02:01:30.000',2),
('Trend_15min_3','2012-07-06 02:16:30.000','2012-07-06 04:01:30.000',3),
('Trend_15min_4','2012-07-06 04:16:30.000','2012-07-06 06:01:30.000',4),
('Trend_15min_5','2012-07-06 06:16:30.000','2012-07-06 08:01:30.000',5))dt(TableName,TableStartTime,TableEndTime,TableIndex))
SELECT
TableIndex
FROM
SampleData
WHERE
TableStartTime BETWEEN @StartRange AND @EndRange OR TableEndTime BETWEEN @StartRange AND @EndRange;
GO
DECLARE @StartRange DATETIME = '2012-07-06 00:17:00';
DECLARE @EndRange DATETIME = '2012-07-06 2:20:00';
WITH SampleData AS (
SELECT
TableName,
CAST(TableStartTime AS DATETIME) TableStartTime,
CAST(TableEndTime AS DATETIME) TableEndTime,
TableIndex
FROM
(VALUES ('Trend_15min_1','2012-07-06 08:16:30.000','2012-07-06 09:27:37.000',1),
('Trend_15min_2','2012-07-06 00:16:30.000','2012-07-06 02:01:30.000',2),
('Trend_15min_3','2012-07-06 02:16:30.000','2012-07-06 04:01:30.000',3),
('Trend_15min_4','2012-07-06 04:16:30.000','2012-07-06 06:01:30.000',4),
('Trend_15min_5','2012-07-06 06:16:30.000','2012-07-06 08:01:30.000',5))dt(TableName,TableStartTime,TableEndTime,TableIndex))
SELECT
TableIndex
FROM
SampleData
WHERE
TableStartTime BETWEEN @StartRange AND @EndRange OR TableEndTime BETWEEN @StartRange AND @EndRange;
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply