Sql-server – Extract entries from table where specific datetime criteria are satisfied SQL

sql server

I have a table DATATABLE that contains a timeseries that looks something like this:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 09:45:00.000
2018-01-11 09:45:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:15:00.000
2018-01-11 10:15:00.000

So if I want to extract the timeseries, I would query:

SELECT * FROM DATATABLE

Now what I want, is to be able to extract part of the timeseries that satisfies specific criteria, which changes each time. The criteria are stored as ranges of values, and so far I have created a procedure that extracts those criteria. One sample criterion would be that the time part of the timeseries should have a value that is contained int he following:

00:00:00
00:30:00
01:00:00
01:30:00
02:00:00
02:30:00
03:00:00
03:30:00
04:00:00
04:30:00
05:00:00
05:30:00
06:00:00
06:30:00
07:00:00
07:30:00
08:00:00
08:30:00
09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00

In other words, the values 2018-01-11 09:45:00.000 and 2018-01-11 10:15:00.000 should not be part of my final result. The final result would therefore look like:

2018-01-11 09:00:00.000
2018-01-11 09:00:00.000
2018-01-11 10:00:00.000
2018-01-11 10:00:00.000

The criterion that I want to set, in English would be:

''Select all the values from my timeseries that have a time part that is (exactly) contained in the series of times I provide you.''

What I wrote so far is:

SELECT * FROM DATATABLE
WHERE DATEPART(MINUTE,[DATATABLE].[ISSUETIMES])=(SELECT IssueTimes FROM @IssueTimes)

where @IssueTimes is a temporary table variable where I store the range of values I want to use as criteria and [DATATABLE].[ISSUETIMES] is the column that contains the timeseries that I want to filter.

The error I get is the following:

Operand type clash: time is incompatible with int

So i get that this probably means that am trying to compare times with int, but i do not know how to proceed as I am kind of new to SQL.

I hope my formulation was good enough to be understandable 🙂

Best Answer

That is what I suggested in comment:

declare @dt table (dt datetime);
insert into @dt values
('2018-01-11 09:00:00.000'),
('2018-01-11 09:00:00.000'),
('2018-01-11 09:45:00.000'),
('2018-01-11 09:45:00.000'),
('2018-01-11 10:00:00.000'),
('2018-01-11 10:00:00.000'),
('2018-01-11 10:15:00.000'),
('2018-01-11 10:15:00.000');

declare @tm table (tm time);

insert into @tm values
('00:00:00'),
('00:30:00'),
('01:00:00'),
('01:30:00'),
('02:00:00'),
('02:30:00'),
('03:00:00'),
('03:30:00'),
('04:00:00'),
('04:30:00'),
('05:00:00'),
('05:30:00'),
('06:00:00'),
('06:30:00'),
('07:00:00'),
('07:30:00'),
('08:00:00'),
('08:30:00'),
('09:00:00'),
('09:30:00'),
('10:00:00'),
('10:30:00'),
('11:00:00'),
('11:30:00'),
('12:00:00'),
('12:30:00');

select *
from @dt
where cast(dt as time) in (select tm from @tm);

Alternatively, use exists:

select *
from @dt t
where exists (select * 
              from @tm t1
              where t1.tm = cast(t.dt as time));

Related Solutions

Sql-server – Delete All Tables With Dates By Date

You should try printing your commands when they yield errors. If you issued this instead of executing it:

PRINT @cmd;

You would see that this:

WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

Yields this:

WHERE ValueDate BETWEEN 2012-07-01 AND 2014-01-25

Which, since those look to SQL Server like three integers with a couple of subtraction operators, becomes:

WHERE ValueDate BETWEEN 2004 AND 1988

Which isn't even a valid BETWEEN operation, even if it could translate those to dates.

What you should be doing is passing these as proper parameters, e.g.

SET @cmd = 'DELETE FROM dbo.DateTable -- always use SCHEMA prefix
 WHERE ValueDate BETWEEN @b AND @e;';

EXEC sp_executesql @cmd, N'@b DATE, @e DATE', @b, @e;

If any of these columns are DATETIME, not DATE, you really should not be using BETWEEN at all. Instead:

WHERE ValueDate >= @b AND ValueDate < DATEADD(DAY, 1, @e);

Please read:

As for the overall requirement of repeating this query against all date columns in the database, here is how I would do it (I've looked at the SO question you reference, and it just shows how to do this for one table):

DECLARE @b DATE = '2012-07-01', @e DATE = '2014-01-25', 
        @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
DELETE ' + QUOTENAME(s.name) 
 + '.' + QUOTENAME(t.name) + '
  WHERE ' + QUOTENAME(c.name) + ' >= @b
  AND ' + QUOTENAME(c.name) + ' < DATEADD(DAY, 1, @e);'
 FROM sys.tables AS t
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (40,42,43,58,61);
       -- or just = 40 if you're only interested in DATE columns

PRINT @sql;
-- EXEC sp_executesql @sql, N'@b DATE, @e DATE', @b, @e;

Sql-server – Exclude rows from SELECT

I think it can be expressed a little more succinctly:

;WITH x AS 
(
  SELECT Tid, Bid, [Action], [Status], Value, [Time], rn = ROW_NUMBER() 
    OVER (PARTITION BY Tid, Bid, [Status] ORDER BY [time] DESC)
  FROM dbo.MassiveTable
)
SELECT Tid, Bid, [Action], [Status], Value, [Time]
FROM x WHERE Status = 'Executed' AND rn = 1
AND NOT EXISTS 
(
  SELECT 1 FROM x AS t
  WHERE t.Tid = x.Tid 
    AND t.Bid = x.Bid
    AND t.[Action] = 'Remove'
);

Note that this assumes that a Remove value anywhere in the timeline makes that Tid, Bid combination invalid. If a Remove is always guaranteed to be last, and the only one in the timeline for that combination (or you don't care about earlier removes as long as they weren't the last entry), you can cheat and get a slightly more efficient plan:

;WITH x AS 
(
  SELECT Tid, Bid, [Action], [Status], Value, [Time], rn = ROW_NUMBER() 
    OVER (PARTITION BY Tid, Bid, [Status] ORDER BY [time] DESC)
  FROM dbo.MassiveTable
  WHERE [Status] = 'Executed'
)
SELECT Tid, Bid, [Action], [Status], Value, [Time]
FROM x WHERE [Action] <> 'Remove' AND rn = 1;

But those assumptions are important. Test all queries with this additional row of sample data:

(3, 'B', 'Remove', 'Executed', 45, '15:23'),

Best Answer

Related Solutions

Sql-server – Delete All Tables With Dates By Date

Sql-server – Exclude rows from SELECT

Related Question