August 6, 2013 at 3:21 am
The time interval on the table increments to approximate 20~25 sec , need to group based on the time ,the iterations are 2 0r 3 in the table in each 20 sec
Table data
DateSValue
2013-08-06 08:01:01.4308
2013-08-06 08:01:20.4302
2013-08-06 08:01:40.2402
2013-08-06 08:05:41.8101
2013-08-06 08:06:20.2171
2013-08-06 08:10:21.7501
2013-08-06 08:10:40.6575
2013-08-06 08:11:02.2171
2013-08-06 08:15:02.1771
2013-08-06 08:15:20.4132
2013-08-06 08:15:40.1801
2013-08-06 08:19:42.1171
2013-08-06 08:20:00.3072
2013-08-06 08:24:21.4935
2013-08-06 08:24:40.6372
2013-08-06 08:25:00.3532
2013-08-06 08:29:01.9302
2013-08-06 08:29:20.2771
2013-08-06 08:29:40.4001
2013-08-06 08:33:42.8301
2013-08-06 08:34:00.3201
2013-08-06 08:34:20.4771
Expected
DateS ValueGroupNumber
2013-08-06 08:01:01.43081
2013-08-06 08:01:20.43021
2013-08-06 08:01:40.24021
2013-08-06 08:05:41.81012
2013-08-06 08:06:20.21712
2013-08-06 08:10:21.75013
2013-08-06 08:10:40.65753
2013-08-06 08:11:02.21713
2013-08-06 08:15:02.17714
2013-08-06 08:15:20.41324
2013-08-06 08:15:40.18014
2013-08-06 08:19:42.11715
2013-08-06 08:20:00.30725
2013-08-06 08:24:21.49356
2013-08-06 08:24:40.63726
2013-08-06 08:25:00.35326
2013-08-06 08:29:01.93027
2013-08-06 08:29:20.27717
2013-08-06 08:29:40.40017
2013-08-06 08:33:42.83018
2013-08-06 08:34:00.32018
2013-08-06 08:34:20.47718
Final Expected from Group--Need to take average of Value and Max of dates on each GROUP
DateS ValueGroupNumber
2013-08-06 08:01:01.43081
2013-08-06 08:01:20.43021
2013-08-06 08:01:40.24021
2013-08-06 08:05:41.81012
2013-08-06 08:06:20.21712
Final result
MaxDateS AvgValueGroupNumber
2013-08-06 08:01:40.240 3 1
2013-08-06 08:06:20.21712
CREATE TABLE #LOGTABLE ( DateS DATETIME ,Value INT )
INSERT INTO #LOGTABLE ( DateS ,Value )
SELECT '2013-08-06 08:01:01.430',8
UNION ALL SELECT '2013-08-06 08:01:20.430',2
UNION ALL SELECT '2013-08-06 08:01:40.240',2
UNION ALL SELECT '2013-08-06 08:05:41.810',1
UNION ALL SELECT '2013-08-06 08:06:00.763',2
UNION ALL SELECT '2013-08-06 08:06:20.217',1
UNION ALL SELECT '2013-08-06 08:10:21.750',1
UNION ALL SELECT '2013-08-06 08:10:40.657',5
UNION ALL SELECT '2013-08-06 08:11:02.217',1
UNION ALL SELECT '2013-08-06 08:15:02.177',1
UNION ALL SELECT '2013-08-06 08:15:20.413',2
UNION ALL SELECT '2013-08-06 08:15:40.180',1
UNION ALL SELECT '2013-08-06 08:19:42.117',1
UNION ALL SELECT '2013-08-06 08:20:00.307',2
UNION ALL SELECT '2013-08-06 08:20:21.133',1
UNION ALL SELECT '2013-08-06 08:24:21.493',5
UNION ALL SELECT '2013-08-06 08:24:40.637',2
UNION ALL SELECT '2013-08-06 08:25:00.353',2
UNION ALL SELECT '2013-08-06 08:29:01.930',2
UNION ALL SELECT '2013-08-06 08:29:20.277',1
UNION ALL SELECT '2013-08-06 08:29:40.400',1
UNION ALL SELECT '2013-08-06 08:33:42.830',1
UNION ALL SELECT '2013-08-06 08:34:00.320',1
UNION ALL SELECT '2013-08-06 08:34:20.477',1
August 6, 2013 at 3:38 am
Bala' (8/6/2013)
... need to group based on the time ...
Please elaborate on your group definition, including an explanation of how group 2 is derived.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 6, 2013 at 4:35 am
The 1st group the date increments by~20-25 sec , and other by 4~5 min
DateS ValueGroupNumber
2013-08-06 08:01:01.43081
2013-08-06 08:01:20.43021 (Diff from previuos was ~20 sec)
2013-08-06 08:01:40.24021 (Diff from previuos was ~20 sec)
2013-08-06 08:05:41.81012 (Diff from previuos was ~4 Min)
2013-08-06 08:06:20.21712 (Diff from previuos was 20 sec)
2013-08-06 08:10:21.75013 (Diff from previuos was ~4 Min)
2013-08-06 08:10:40.65753 (Diff from previuos was 20 sec)
2013-08-06 08:11:02.21713 (Diff from previuos was 20 sec)
August 6, 2013 at 4:41 am
The difference between the two rows in group 2 is 39 seconds.
You'll need to establish clear rules on how to divide the groups.
Around 20 seconds doesn't work for a computer, it needs exact data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2013 at 4:42 am
Koen Verbeeck (8/6/2013)
The difference between the two rows in group 2 is 39 seconds.You'll need to establish clear rules on how to divide the groups.
Around 20 seconds doesn't work for a computer, it needs exact data.
+1 Thanks Koen.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 6, 2013 at 4:45 am
Sorry, My mistake ..
it would be 20~50 sec , but it should be considered as one group
if greater than 3 min then the next group
August 6, 2013 at 5:21 am
;WITH OrderedData AS (SELECT *, seq = ROW_NUMBER() OVER(ORDER BY DateS) FROM #LOGTABLE)
SELECT
l.DateS,
l.Value,
x.GroupColumn
FROM OrderedData l
CROSS APPLY (
SELECT TOP 1 GroupColumn = ROW_NUMBER() OVER(ORDER BY nr.seq)
FROM OrderedData tr
INNER JOIN OrderedData nr ON nr.seq = tr.seq+1
CROSS APPLY (SELECT seq = CASE WHEN tr.seq = 1 THEN 1 ELSE nr.seq END) x
WHERE (tr.seq = 1 OR DATEDIFF(minute,tr.DateS, nr.DateS) > 3)
AND x.seq <= l.seq
ORDER BY x.seq DESC
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy