CODEWITHSUNDEEP
sql-server
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

Select only value of given interval

I am working on a SQL query where I have to select only one value of given time interval in seconds.

Suppose I give time interval=5, then the value should come after every 5 seconds. If I give time interval=60, then the value should come after every 60 seconds i.e. after every minute. If I give time interval=300, then the value should come after every 300 seconds i.e. after every 5 minutes and so on.

For this I used the following query 

DECLARE @FROMDATETIME DATE = '2016-09-21 14:00',
        @TODATETIME   DATE = '2020-09-21 14:00',
        @INTERVAL     NVARCHAR(50) = '300' 

DECLARE @intv INT = ((CONVERT(INT, @INTERVAL)) / 60) 

SELECT 
    CONVERT(NVARCHAR(20), DATEANDTIME, 113) DATETIME, 
    ALARM, F870 'COLD JUICE PRE [PSI]', 
    F872 'COLD WATER PRE [PSI]', F82 'FILLER TEMP [DEGC]', 
    F810 'FLOW [LTR/MIN]', F869 'HOT JUICE PRE[PSI]', 
    F874 'HOT WATER PRE[PSI]', F867 'HOT WATER TEMP[DEGC]', 
    F84 'JUICE TEMP AFTER HOLD TUBE[DEGC]', F822 'PD FOR COLD PHE[PSI]', 
    F821 'PD FOR HOT PHE[PSI]', F8X 'PHE INLET TEMP[DEGC]', 
    B1 'MACHINE 1 STATUS', B2 'MACHINE 2 STATUS' 
FROM
    (SELECT 
         DATEANDTIME,
         NULL ALARM, F870 F870, F872 F872, F82 F82, F810 F810, F869 F869, 
         F874 F874, F867 F867, F84 F84, F822 F822, F821 F821, F8X F8X, 
         CASE 
            WHEN B1 = 1 THEN 'ON' 
            ELSE 'OFF' 
         END B1, 
         CASE WHEN B2 = 1 
            THEN 'ON' 
            ELSE 'OFF' 
         END B2
     FROM 
         DATALOGTABLE WITH (NOLOCK) 
     WHERE  
         (CONVERT(DATE, DATEANDTIME, 103) >= CONVERT(DATE, @FROMDATETIME, 103) 
         AND CONVERT(DATE, DATEANDTIME, 103) <= CONVERT(DATE, @TODATETIME, 103))
         AND (((DATEPART(minute, dateandtime) % @intv) = 0))            

     UNION ALL

     SELECT 
         DATEANDTIME, ALARMTXT ALARM,
         NULL F870, NULL F872, NULL F82, NULL F810, NULL F869, NULL F874, 
         NULL F867, NULL F84, NULL F822, NULL F821, NULL F8X, NULL B1, NULL B2
     FROM 
         DBO.ALARMHISTORY WITH (NOLOCK) 
     WHERE 
         (CONVERT(DATE, DATEANDTIME, 103) >= CONVERT(DATE, @FROMDATETIME, 103) 
         AND CONVERT(DATE, DATEANDTIME, 103) <= CONVERT(DATE, @TODATETIME, 103))
         AND (((DATEPART(minute, dateandtime) % @intv) = 0))
    ) Z 
ORDER BY 
    Z.DATEANDTIME DESC

In the above query I have given time interval of 300 second i.e. 5 minute means value should come after every 300 second or 5 minutes. But it is showing value of every minute. 

  09 Jan 2020 20:10:59  NULL    70.00   72.00   2.00    0.00    69.65
  09 Jan 2020 20:10:58  NULL    70.00   72.00   2.00    0.00    69.65
  09 Jan 2020 20:10:57  NULL    70.00   72.00   2.00    0.00    69.65
  09 Jan 2020 20:10:56  NULL    70.00   72.00   2.00    0.00    69.65
  09 Jan 2020 20:10:55  NULL    70.00   72.00   2.00    0.00    69.65

Here the first record have date '09 Jan 2020 20:10:59' and second should be '09 Jan 2020 20:15:59' because interval given between two rows date is 300 seconds. which is not coming in this case. What to do here?

Upvotes: 1

Views: 255

Answers (1)

daniel
daniel

Reputation: 1070

You seem try to mod minutes to seconds, you should correct this:

declare @DATETIME DATETIME = '2016-09-21 14:59:30';
declare @INTERVAL NVARCHAR(50) = '300'

select (DATEPART(minute, @DATETIME) * 60 + DATEPART(second, @DATETIME)) % @INTERVAL;

Upvotes: 1

Related Questions