Geek Answers Handbook

How to filter strings between dates without using a loop in tsql

I want to filter rows from one table between two dates, and those filtered rows should be under the same date, e.g.

I want to get all the lines between (March 16, 2015 6AM) and (March 17, 2015 6AM) as (March 17, 2015) the dates and (March 17, 2015 6AM) and (March 18, 2015 6AM), for example, ( March 18, 2015), etc.

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 request I'm 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

which 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 a loop. I am using sql 2014, is there an alternative solution?

+4
source share
3

FetchedOn LogTime:

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:

FetchedOn, datetime SQL2012 +.

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
+2

. , . , CTE:

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

, :

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

:

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
+3

, SQL Server 2005+: DATEADD(HOUR, 6, CAST(CAST(DATEADD(HOUR, 18, A.LogTime) AS DATE) AS DATETIME)). 18 LogTime, CAST, , 6 .

( ):

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;
+1

More articles:


All Articles