SELECT DateTime not in SQL

Question

I have the following table:

oDateTime              pvalue 
2017-06-01 00:00:00    70
2017-06-01 01:00:00    65
2017-06-01 02:00:00    90
ff.
2017-08-01 08:00:00    98

The oDateTime field is an hourly data which is impossible to have a duplicate value.

My question is, how can I know if the oDateTime data is correct? I meant, I need to make sure the data is not jump? It should be always 'hourly' base.

Am I missing the date? Am I missing the time?

Please advice. Thank you.

What's the expected result? Do you want to show the missing times? — Felix Pamittan, Aug 07 '17 at 07:52
Count the number of records for each "date". There should be 24 for each date. — Incognito, Aug 07 '17 at 07:54
Count of distinct `times` per date should be 24 and sum of `times` per date should be 276. Then you know you've got a record for each hour. — user2877959, Aug 07 '17 at 08:00
@ Haminteu is there any possibility of more than one row in the same hour? — Shiblu, Aug 07 '17 at 08:34

Vojtěch Dohnal · Accepted Answer · 2017-08-07T09:19:17.517

Based on this answer, you can get the missing times form your table MyLogTable it like this:

DECLARE @StartDate DATETIME = '20170601', @EndDate DATETIME = '20170801'

SELECT  DATEADD(hour, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(hour, @StartDate, @EndDate) AND 
NOT EXISTS (SELECT 1 FROM MyLogTable WHERE DATEADD(hour, nbr - 1, @StartDate)= oDateTime )

If you need to check longer period, you can just add CROSS JOIN like this

FROM      sys.columns c
CROSS JOIN sys.columns c1

It enables you to check much more than cca thousand records (rowcount of sys.columns table) in one query.

Danger here is that you need enough rows in sys.columns to get all the hours. For dates in example, that's 1465. If you are missing on it (like my demo server), another cross join on same table could be added. — Nenad Zivkovic, Aug 07 '17 at 09:11

Chester Lim · Answer 2 · 2017-08-09T16:12:17.520

Perhaps you are looking for this? This will return dates having count < 24 - which indicates a "jump"

;WITH    datecount
      AS ( SELECT   CAST(oDateTime AS DATE) AS [date] ,
                    COUNT(CAST(oDateTime AS DATE)) AS [count]
           FROM     @temp
           GROUP BY ( CAST(oDateTime AS DATE) )
         )
SELECT  *
FROM    datecount
WHERE   [count] < 24;

EDIT: Since you changed the requirement from "How to know if there is missing" to "What is the missing", here's an updated query.

DECLARE @calendar AS TABLE ( oDateTime DATETIME ) 
DECLARE @min DATETIME = (SELECT MIN([oDateTime]) FROM @yourTable)
DECLARE @max DATETIME = (SELECT MAX([oDateTime]) FROM @yourTable)

WHILE ( @min <= @max )
    BEGIN
        INSERT  INTO @calendar
        VALUES  ( @min );

        SET @min = DATEADD(hh, 1, @min);
    END;

SELECT  t1.[oDateTime]
FROM    @calendar t1
        LEFT JOIN @yourTable t2 ON t1.[oDateTime] = t2.[oDateTime]
GROUP BY t1.[oDateTime]
HAVING  COUNT(t2.[oDateTime]) = 0;

I first created a hourly calendar based on your MAX and MIN Datetime, then compared your actual table to the calendar to find out if there is a "jump".

Thank you and how can I know that I didn't missing the date? — Haminteu, Aug 08 '17 at 01:56

Kapil · Answer 3 · 2017-08-07T08:54:04.277

0

Since your table is not having any unique id number, use a row_number() to get the row number in the cte , then perform an self inner join with the row id and next id ,take the difference of oDateTime accordingly, this will show exactly which row do not have time difference of one hour

 ;with cte(oDateTime,pValue,Rid)
 As
 (
  select *,row_number() over(order by oDateTime) from [YourTableName] t1
 )
 select *,datediff(HH,c1.oDateTime,c2.oDateTime) as HourDiff from cte c1
 inner join cte c2
 on c1.Rid=c2.Rid-1  where datediff(HH,c1.oDateTime,c2.oDateTime) >1

edited Aug 07 '17 at 08:54

answered Aug 07 '17 at 08:20

Kapil

987
5
11

Thank you and how can I know that I didn't missing the date? – Haminteu Aug 08 '17 at 01:57

Shiblu · Answer 4 · 2017-08-07T09:21:33.390

0

You could use DENSE_RANK() for numbering the hours in a day from 1 to 24. Then all you have to do is to check whether the max rank is 24 or not for a day. if there is at least one entry for each hour, then dense ranking will have max value of 24.

Use the following query to find the date when you have a oDateTime missing.

SELECT [date]
FROM
(
  SELECT *
  , CAST(oDateTime AS DATE) AS [date]
  , DENSE_RANK() OVER(PARTITION BY CAST(oDateTime AS DATE) ORDER BY DATEPART(HOUR, oDateTime)) AS rank_num
  FROM Test
) AS t
GROUP BY [date]
HAVING(MAX(rank_num) != 24);

If you need validation for each row of oDateTime, you could do self join based on rank and get the missing hour for each oDateTime.

edited Aug 07 '17 at 09:21

answered Aug 07 '17 at 08:46

Shiblu

457
3
12

Thank you and how can I know that I didn't missing the date? – Haminteu Aug 08 '17 at 01:57
What is your expected output for missing a continuous sequence of day? Do you want days that are missing in a month, year or do you want missing months between the earliest and the latest date? Please provide an example. – Shiblu Aug 08 '17 at 06:57
I need to list all the date and time that missing. – Haminteu Aug 09 '17 at 02:25

SELECT DateTime not in SQL

4 Answers4