4

I want to filter rows from a single table between two datetimes and those filtered rows should come under a single date, for example

i want to get all the rows between (16 mar 2015 6AM) and (17 mar 2015 6AM) datetimes as (17 mar 2015) date and (17 mar 2015 6AM) and (18 mar 2015 6AM) datetimes as (18 mar 2015) date and so on.

this is my demo table

Id      Name    LogTime
1       mj      2015-03-16 01:28:03.257
2       mj      2015-03-16 05:28:03.257
3       mj      2015-03-16 06:28:03.257
4       mj      2015-03-16 18:28:03.257
5       mj      2015-03-17 01:28:06.677
6       mj      2015-03-17 06:28:06.677
7       mj      2015-03-17 16:28:07.460
8       mj      2015-03-17 07:28:03.257
9       mj      2015-03-18 01:28:08.193
10      mj      2015-03-18 05:28:03.257
11      mj      2015-03-18 06:28:03.257
12      mj      2015-03-18 18:28:03.257
13      mj      2015-03-19 01:28:06.677
14      mj      2015-03-19 06:28:06.677
15      mj      2015-03-19 16:28:07.460
16      mj      2015-03-19 07:28:03.257
17      mj      2015-03-20 01:28:08.193
18      mj      2015-03-20 05:28:03.257
19      mj      2015-03-20 06:28:03.257
20      mj      2015-03-20 18:28:03.257

below is the query that I am using.

DECLARE @i INT = 1

DECLARE @from DATETIME
    ,   @to DATETIME

WHILE (@i <= 5)
BEGIN

    SET @from = CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(D, -@i, '2015-03-20'), 102) + ' 6:00:00')
    SET @to = CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(D, -@i + 1, '2015-03-20'), 102) + ' 6:00:00')

    SELECT  *,  @to AS 'FetchedOn'
    FROM Biometric
    WHERE LogTime BETWEEN @from AND @to
    ORDER BY LogTime

    SET @i = @i + 1

END

that generates the following result.

Id  Name    LogTime             FetchedOn
14  mj  2015-03-19 06:28:06.677 2015-03-20 06:00:00.000
16  mj  2015-03-19 07:28:03.257 2015-03-20 06:00:00.000
15  mj  2015-03-19 16:28:07.460 2015-03-20 06:00:00.000
17  mj  2015-03-20 01:28:08.193 2015-03-20 06:00:00.000
18  mj  2015-03-20 05:28:03.257 2015-03-20 06:00:00.000

Id  Name    LogTime             FetchedOn
11  mj  2015-03-18 06:28:03.257 2015-03-19 06:00:00.000
12  mj  2015-03-18 18:28:03.257 2015-03-19 06:00:00.000
13  mj  2015-03-19 01:28:06.677 2015-03-19 06:00:00.000

Id  Name    LogTime             FetchedOn
6   mj  2015-03-17 06:28:06.677 2015-03-18 06:00:00.000
8   mj  2015-03-17 07:28:03.257 2015-03-18 06:00:00.000
7   mj  2015-03-17 16:28:07.460 2015-03-18 06:00:00.000
9   mj  2015-03-18 01:28:08.193 2015-03-18 06:00:00.000
10  mj  2015-03-18 05:28:03.257 2015-03-18 06:00:00.000

Id  Name    LogTime             FetchedOn
3   mj  2015-03-16 06:28:03.257 2015-03-17 06:00:00.000
4   mj  2015-03-16 18:28:03.257 2015-03-17 06:00:00.000
5   mj  2015-03-17 01:28:06.677 2015-03-17 06:00:00.000

Id  Name    LogTime             FetchedOn
1   mj  2015-03-16 01:28:03.257 2015-03-16 06:00:00.000
2   mj  2015-03-16 05:28:03.257 2015-03-16 06:00:00.000

now I want to get the same result without using loop. I am using sql 2014, is there any alternate solution?

Improve this question
3
  • You can use CLR table valued function. It might be faster, than t-sql while loop. Commented Apr 18, 2015 at 10:18
  • By "same result" do you mean five separate result sets or would you accept a single result set with an i column to group the results by date? Commented Apr 18, 2015 at 13:52
  • no of tables doesn't matter for me, as long as it returns the correct result set. Commented Apr 19, 2015 at 3:46

3 Answers 3

Reset to default
3

Here is some code. The Idea is to get all possible distinct ranges from test data. This is what CTE returns:

st                      ed
2015-03-15 06:00:00.000 2015-03-16 06:00:00.000
2015-03-16 06:00:00.000 2015-03-17 06:00:00.000
2015-03-17 06:00:00.000 2015-03-18 06:00:00.000
2015-03-18 06:00:00.000 2015-03-19 06:00:00.000
2015-03-19 06:00:00.000 2015-03-20 06:00:00.000

After this it simple join on condition where data falls between those ranges:

DECLARE @t TABLE(ID INT, D DATETIME)

INSERT INTO @t VALUES
(1       ,'2015-03-16 01:28:03.257'),
(2       ,'2015-03-16 05:28:03.257'),
(3       ,'2015-03-16 06:28:03.257'),
(4       ,'2015-03-16 18:28:03.257'),
(5       ,'2015-03-17 01:28:06.677'),
(6       ,'2015-03-17 06:28:06.677'),
(7       ,'2015-03-17 16:28:07.460'),
(8       ,'2015-03-17 07:28:03.257'),
(9       ,'2015-03-18 01:28:08.193'),
(10      ,'2015-03-18 05:28:03.257'),
(11      ,'2015-03-18 06:28:03.257'),
(12      ,'2015-03-18 18:28:03.257'),
(13      ,'2015-03-19 01:28:06.677'),
(14      ,'2015-03-19 06:28:06.677'),
(15      ,'2015-03-19 16:28:07.460'),
(16      ,'2015-03-19 07:28:03.257'),
(17      ,'2015-03-20 01:28:08.193'),
(18      ,'2015-03-20 05:28:03.257'),
(19      ,'2015-03-20 06:28:03.257'),
(20      ,'2015-03-20 18:28:03.257')

;
WITH    cte
          AS ( SELECT DISTINCT
                        DATEADD(HOUR, -18, CAST(CAST(D AS DATE) AS DATETIME)) AS st ,
                        DATEADD(HOUR, 6, CAST(CAST(D AS DATE) AS DATETIME)) AS ed
               FROM     @t
             )
    SELECT  t.ID, t.D, c.ed
    FROM    cte c
            JOIN @t t ON t.D BETWEEN c.st AND c.ed

Output:

ID  D                       ed
1   2015-03-16 01:28:03.257 2015-03-16 06:00:00.000
2   2015-03-16 05:28:03.257 2015-03-16 06:00:00.000
3   2015-03-16 06:28:03.257 2015-03-17 06:00:00.000
4   2015-03-16 18:28:03.257 2015-03-17 06:00:00.000
5   2015-03-17 01:28:06.677 2015-03-17 06:00:00.000
6   2015-03-17 06:28:06.677 2015-03-18 06:00:00.000
7   2015-03-17 16:28:07.460 2015-03-18 06:00:00.000
8   2015-03-17 07:28:03.257 2015-03-18 06:00:00.000
9   2015-03-18 01:28:08.193 2015-03-18 06:00:00.000
10  2015-03-18 05:28:03.257 2015-03-18 06:00:00.000
11  2015-03-18 06:28:03.257 2015-03-19 06:00:00.000
12  2015-03-18 18:28:03.257 2015-03-19 06:00:00.000
13  2015-03-19 01:28:06.677 2015-03-19 06:00:00.000
14  2015-03-19 06:28:06.677 2015-03-20 06:00:00.000
15  2015-03-19 16:28:07.460 2015-03-20 06:00:00.000
16  2015-03-19 07:28:03.257 2015-03-20 06:00:00.000
17  2015-03-20 01:28:08.193 2015-03-20 06:00:00.000
18  2015-03-20 05:28:03.257 2015-03-20 06:00:00.000
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I did. I suppose your answer is not incorrect, but what bothers me about it is that there is no explanation whatsoever what that "some code" does. Your answer would be so much more helpful if it wasn't just a code / result dump, but if these were accompanied with at least a brief explanation of the thinking behind it. I would be happy to upvote if the question were edited accordingly.
I will update but I really think that there is nothing to comment. Code is selfexplanatory in this situation
2

You can calculate FetchedOn based on the time part of the LogTime field:

SELECT B.*,  dateadd(day, (iif(cast(LogTime as time) < '06:00:00', 0, 1)), cast(LogTime as date)) + cast('06:00:00' as datetime) as FetchedOn
from Biometric B
ORDER BY FetchedOn DESC, LogTime

Update:

Simpler formula to calculate FetchedOn, and also a cast to datetime added for SQL2012+ compatibility.

SELECT B.*, cast(cast(dateadd(hour, +18, LogTime) as date) as datetime) + cast('06:00:00' as datetime) as FetchedOn
from Biometric B
ORDER BY FetchedOn DESC, LogTime
Improve this answer

7 Comments

All other answers so far are simple code dumps without any explanation whatsoever. Your answer is much shorter, but it explains (if only briefly) the reasoning behind a working solution. +1 for that.
@stakx, are you serious? I will shut my eyes on everything but this code simply doesn't work.
First you have syntax error in dateadd, because you are using date type there, it is error. You return all rows. Op return only 18 rows. But the idea is correct and you need to correct those errors
I mean in dateadd function you have date + datetime, this is illegal: Msg 402, Level 16, State 1, Line 37 The data types date and datetime are incompatible in the add operator.
iif would never work in sql 2008, its sql 2012 and later
|
1

Here's a formula that should work for SQL Server 2005+: DATEADD(HOUR, 6, CAST(CAST(DATEADD(HOUR, 18, A.LogTime) AS DATE) AS DATETIME)). We simply add 18 hours to the LogTime, abuse CAST to truncate the time piece off, and then add 6 hours to this value.

Full Example (based on original):

DECLARE @i INT = 5
DECLARE @endDate DATETIME = '2015-03-20 06:00:00.000';
DECLARE @startDate DATETIME = DATEADD(d, -@i, @endDate);

SELECT *
FROM (
    SELECT A.Id
         , A.LogTime
         , FetchedOn = DATEADD(HOUR, 6, CAST(CAST(DATEADD(HOUR, 18, A.LogTime) AS DATE) AS DATETIME))
    FROM(VALUES
        (1, '2015-03-16 01:28:03.257')
      , (2, '2015-03-16 05:28:03.257')
      , (3, '2015-03-16 06:28:03.257')
      , (4, '2015-03-16 18:28:03.257')
      , (5, '2015-03-17 01:28:06.677')
      , (6, '2015-03-17 06:28:06.677')
      , (7, '2015-03-17 16:28:07.460')
      , (8, '2015-03-17 07:28:03.257')
      , (9, '2015-03-18 01:28:08.193')
      , (10, '2015-03-18 05:28:03.257')
      , (11, '2015-03-18 06:28:03.257')
      , (12, '2015-03-18 18:28:03.257')
      , (13, '2015-03-19 01:28:06.677')
      , (14, '2015-03-19 06:28:06.677')
      , (15, '2015-03-19 16:28:07.460')
      , (16, '2015-03-19 07:28:03.257')
      , (17, '2015-03-20 01:28:08.193')
      , (18, '2015-03-20 05:28:03.257')
      , (19, '2015-03-20 06:28:03.257')
      , (20, '2015-03-20 18:28:03.257')
    ) A (Id, LogTime)
    WHERE A.LogTime BETWEEN @startDate AND @endDate
) A
ORDER BY A.FetchedOn DESC, A.LogTime ASC;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.