CODEWITHSUNDEEP
sqlsql-servert-sql
trailer
trailer

Reputation: 115

SQL Query - Select last date before timespan where no dates exists in timespan

In my table, I have a datetime column and a tag column. I would like to select the latest value before a timespan, if there are no values in the timespan. If there are values in the timespan, I would not like to return any values.

My query has the following input parameters:

@StartDate datetime
@EndDate datetime

The most important query results:

  1. The query should not return any results if there is data between @StartDate and @EndDate
  2. The query should return the latest value for each Tag before the @StartDate IF, and only if, there are no results between @StartDate and @EndDate

My issue, I have created two queries:

  1. Returns the latest result before the timespan.
  2. Returns all results IN the timespan.

The idea is to SELECT [Values before the timespan] WHERE NOT EXISTS IN [Values in the timespan].

I have tried to join these queries to get the end result, but this is where I struggle.

STEPS TO REPRODUCE (SETUP): 

CREATE TABLE dbo.MyTable(id int IDENTITY(1,1) NOT NULL, Tag nvarchar(200) NOT NULL, StartTime datetime NOT NULL)  
DECLARE @day int, @month int, @year int

SELECT @day = 15, @month = 1, @year = 2015
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MyTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MySuperTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
SELECT @day = 16, @month = 1, @year = 2015
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MyTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MySuperTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
SELECT @day = 18, @month = 1, @year = 2015
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MyTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MySuperTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
SELECT @day = 19, @month = 1, @year = 2015
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MyTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MySuperTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
SELECT @day = 26, @month = 1, @year = 2015
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MyTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))
INSERT INTO dbo.MyTable(Tag, StartTime) VALUES('MySuperTag',dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1))

STEPS TO REPRODUCE (QUERY):
This should not return any values, since there are values in the timespan. 

DECLARE @day int, @month int, @year int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @day = 17, @month = 1, @year = 2015
SET @StartTime = dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)
SET @EndTime = dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1 + 3)

SELECT * FROM (SELECT id, Tag, StartTime FROM dbo.MyTable WHERE StartTime < @StartTime AND Tag NOT IN ( SELECT Tag FROM dbo.MyTable WHERE (StartTime > @StartTime AND StartTime < @EndTime))) as d WHERE EXISTS ( SELECT Tag, StartTime, ROW_NUMBER FROM ( SELECT Tag, StartTime, ROW_NUMBER() OVER(PARTITION BY Tag ORDER BY StartTime DESC) AS ROW_NUMBER FROM dbo.MyTable WHERE StartTime < @StartTime) AS b WHERE ROW_NUMBER = '1')

STEPS TO REPRODUCE (QUERY2):
This should produce the latest values before the timespan, since there are no values in the timespan. 

SELECT @day = 21, @month = 1, @year = 2015
SET @StartTime = dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)
SET @EndTime = dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1 + 3)

SELECT * FROM (SELECT id, Tag, StartTime FROM dbo.MyTable WHERE StartTime < @StartTime AND Tag NOT IN ( SELECT Tag FROM dbo.MyTable WHERE (StartTime > @StartTime AND StartTime < @EndTime))) as d WHERE EXISTS ( SELECT Tag, StartTime, ROW_NUMBER FROM ( SELECT Tag, StartTime, ROW_NUMBER() OVER(PARTITION BY Tag ORDER BY StartTime DESC) AS ROW_NUMBER FROM dbo.MyTable WHERE StartTime < @StartTime) AS b WHERE ROW_NUMBER = '1')

EDIT: Added "latest value for each Tag" in section about expected results.

Upvotes: 2

Views: 959

Answers (3)

Taras Velykyy
Taras Velykyy

Reputation: 1801

SELECT * 
FROM MyTable t
WHERE StartTime < @start
  AND id = 
    (SELECT TOP 1 mt.id FROM MyTable mt WHERE mt.Tag = t.Tag ORDER BY StartTime DESC)
  AND NOT EXISTS 
    (SELECT 1 FROM MyTable WHERE StartTime >= @start AND StartTime <= @end)
ORDER BY StartTime DESC;

Upvotes: 0

AeroX
AeroX

Reputation: 3443

Here is my revised answer for the modified question:

SELECT [A].* FROM [dbo].[MyTable] AS [A]
INNER JOIN (
  SELECT [Tag], MAX([StartTime]) AS [StartTime]
  FROM [dbo].[MyTable]
  WHERE [StartTime] < @StartTime
  GROUP BY [Tag]
) AS B ON ([A].[Tag] = [B].[Tag] AND [A].[StartTime] = [B].[StartTime])
WHERE
  [A].[StartTime] < @StartTime AND
  0 = (
    SELECT COUNT(*)
    FROM [dbo].[MyTable]
    WHERE [StartTime] BETWEEN @StartTime AND @EndTime
  )
;

The joined subquery works out the latest date for each tag before the @StartTime and joins back to it's self so that the full row can be returned (with the id).

Upvotes: 1

AeroX
AeroX

Reputation: 3443

The following should be roughly what you are looking for:

SELECT TOP 1 *
FROM [dbo].[MyTable] 
WHERE
  [StartTime] < @StartTime AND
  0 = (
    SELECT COUNT(*)
    FROM [dbo].[MyTable]
    WHERE [StartTime] BETWEEN @StartTime AND @EndTime
  )
ORDER BY [StartTime] DESC;

The 0 = (SELECT COUNT(*) ...) bit causes the query to return no data when there is data between the @StartTime and @EndTime. The rest of the query is then just selecting the first row before the @StartTime.

SQL Fiddle

Comparison of query plans for 0 = (SELECT COUNT(*) ...) vs EXISTS(SELECT 1 ...) Query Plan Comparison

Upvotes: 0

Related Questions