CODEWITHSUNDEEP
sqlsql-server
user2906488
user2906488

Reputation: 25

SQL - Select Data Based on Date Range Efficiently

I want to select data based on a date range in a table and also get the latest value before that date range.

How can I make this more efficient?

The table is defined as:

Name       varchar(100)   Checked
DataType   varchar(100)   Checked
Time       datetime       Checked
Value      int            Checked

The table contains the following data:

Name    DataType  Time                      Value
Name1   Type1     2010-09-29 17:29:00.000   999
Name2   Type2     2013-11-02 06:51:00.000   0

I have created the following query which is a little long winded but it does return the correct results. However, I want to know if this can be achieved using better T-SQL?

I am going to write the code as in-line SQL within my application

DECLARE @effectiveFrom DATETIME
SET @effectiveFrom = '2010-09-29 17:30:00'
DECLARE @effectiveTo DATETIME
SET @effectiveTo = '2010-09-29 17:49:00'
DECLARE @bmunit varchar(100)
DECLARE @datatype varchar(100)

--Select * from [Table] 
--where  (Time >= '2010-09-29 17:30:00' and Time < '2010-09-29     17:49:00') 

--union Select top 1 * from [Table]
-- where  Time < '2010-09-29 17:30:00' order by Time desc
SELECT P1.[Name]
      ,P1.[DataType]
      ,P1.[Time]
      ,P1.[Value]
      ,P1.Que
FROM
(
  SELECT  [Name]
      ,[DataType]
      ,[Time]
      ,[Value]
      , 'between' AS [Que]
  FROM [Table]
  WHERE  Time >= @effectiveFrom AND  EffectiveTime < @effectiveTo
) P1   
UNION SELECT P2.[Name]
      ,P2.[DataType]
      ,P2.[Time]
      ,P2.[Value]
      , 'before' AS [Que]
FROM
(
  SELECT TOP 1 [Name]
      ,[DataType]
      ,Time]
      ,[Value]
  FROM [Table] P1
  WHERE  Time < @effectiveFrom

) P2 --ON P1.[Name] = P2.[Name] AND P1.[DataType] = P2.[DataType]
WHERE Name = 'MyName' --AND P1.Time >= @effectiveFrom AND P1.Time < @effectiveTo
ORDER BY 2,3

Upvotes: 2

Views: 2064

Answers (1)

acfrancis
acfrancis

Reputation: 3681

You should be able to pare it down to something like this:

SELECT P1.[Name]
      ,P1.[DataType]
      ,P1.[Time]
      ,P1.[Value]
      ,'between' AS Que
FROM [Table] P1
WHERE Time >= @effectiveFrom AND Time < @effectiveTo
      AND Name = 'MyName'
UNION ALL
SELECT * FROM (
    SELECT TOP 1 P2.[Name]
          ,P2.[DataType]
          ,P2.[Time]
          ,P2.[Value]
          ,'before' AS [Que]
    FROM [Table] P2
    WHERE Time < @effectiveFrom
          AND Name = 'MyName'
    ORDER BY 3
) A

If you don't like repeating the name check, the select will get a bit bigger:

SELECT * FROM (
    SELECT P1.[Name]
          ,P1.[DataType]
          ,P1.[Time]
          ,P1.[Value]
          ,'between' AS Que
    FROM [Table] P1
    WHERE Time >= @effectiveFrom AND Time < @effectiveTo
    UNION ALL
    SELECT * FROM (
        SELECT TOP 1 P2.[Name]
            ,P2.[DataType]
            ,P2.[Time]
            ,P2.[Value]
            ,'before' AS [Que]
        FROM [Table] P2
        WHERE Time < @effectiveFrom
        ORDER BY 3
    ) A
) B
WHERE Name = 'MyName'

If you want to make it shorter, you can try this but I think it's less readable:

SELECT P1.[Name]
      ,P1.[DataType]
      ,P1.[Time]
      ,P1.[Value]
      ,(CASE WHEN Time >= @effectiveFrom THEN 'between' ELSE 'before' END) AS Que
FROM [Table] P1
WHERE Name = 'MyName'
      AND 
      (
          (Time >= @effectiveFrom AND Time < @effectiveTo)
          OR (Time < @effectiveFrom AND NOT EXISTS (SELECT * 
                                                FROM [Table] P2 
                                                WHERE Time < @effectiveFrom
                                                    AND P2.Time > P1.Time)
          )
      )

Is it even shorter?

Upvotes: 2

Related Questions