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