Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL Query for 15 minute Maximum values

Posted on 2013-11-14
Medium Priority
1,290 Views
I have a table which is updated every 10 seconds (around, not exactly because this is updated using data sent via a GSM modem).

I have to do few things using this data.

1. Get 15 minute maximum values for whole 15 minute intervals for last 24 hours.
E.g: If the time now is say 2013-08-15 18:52

Resulting query should be like...
Timestamp              MaxValue
2013-08-14 18:45      53.7
2013-08-14 19:00      56.5
2013-08-14 19:15      56
...
...
...
2013-08-15 18:00      55.4
2013-08-15 18:15      54.5
2013-08-15 18:30      49.3
2013-08-15 18:45      55.8

2. Get the percentiles (say 90th)  for whole 15 minute intervals for last 24 hours as above.

3. Get the logarithmic average for whole 15 minute intervals for last 24 hours as above.

Below is how the original table looks like.

Timestamp                              Value
2013-08-14 18:51:33.000      53.7
2013-08-14 18:52:33.000      56.5
2013-08-14 18:53:32.000      56
2013-08-14 18:54:36.000      55.4
2013-08-14 18:55:33.000      54.5
2013-08-14 18:56:33.000      49.3
2013-08-14 18:57:33.000      55.8
2013-08-14 18:58:32.000      55.6
2013-08-14 18:59:38.000      54.6
2013-08-14 19:00:32.000      49.2
2013-08-14 19:01:33.000      56.4
2013-08-14 19:02:32.000      55
2013-08-14 19:03:33.000      48.9
2013-08-14 19:04:38.000      55.6
2013-08-14 19:05:32.000      55.8
2013-08-14 19:06:32.000      54.6
0
Question by:MrB8r
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 12
• 10

LVL 49

Expert Comment

ID: 39650280
Here's the round to 15 minutes part of this question:
``````SELECT
convert(varchar,dateadd(MINUTE, (datediff(MINUTE,day_only,time_stamp) / 15) * 15, day_only),120)
, sum(value)
, min(value)
, avg(value)
, max(value)
FROM YourTable
CROSS apply (SELECT convert(datetime,convert(date,time_stamp)) AS day_only) AS ca1
GROUP BY
dateadd(MINUTE, (datediff(MINUTE,day_only,time_stamp) / 15) * 15, day_only)
``````
The cross apply simply saves doing this calculation numerous times.
That calc just strips time from the [time_stamp] but leaves it as datetime.
Then calculate the number of minutes between that point and the [time_stamp]
that value / 15 results in the number of whole 15 minute intervals
multiply that by 15 to get back to minutes
add those minutes to the [day_only] = rounded to 15 minutes

{+ edit}
because "timestamp" is a SQL term, and also because it also doesn't relate to dates or times either, I have used [time_stamp] in my code above. I would NOT recommend using "timestamp" as a field name.
0

Author Comment

ID: 39650304
Thanks a bunch PortletPaul for your quick response, answer and for the explanation too.
You made my day!!! (well... 1/3 of the day :) )
Yes, my field name is LastUpdated.

With the percentiles I need to sort the raw values (for each 15 min - about 90 rows approximately of 10 second intervals) from highest to lowest and get the 90% th value from tthe bottom (for 90th Percentile). So, it will be around the 81st raw value from the bottom.(90% of 90 rows).

0

LVL 49

Expert Comment

ID: 39650324
not sure I even understand the log average I'm afraid, had a look at wikipedia
http://en.wikipedia.org/wiki/Logarithmic_mean
and it requires 2 non-negative numbers
so is that Value of this row and Value of the previous row?
(this bit would be easier in SQL 2012 using LAG() )

also, for percentile calculation, there is a percent_rank() in sql 2012

so, do you really use sql 2008 or do you happen to have sql 2012?

---
tip, always use real table and field names, in the long run it is easier for all involved
0

Author Comment

ID: 39650356
Thanks again.
We are using 2008 and dont have 2012.

I know how to do them in Excel.
E2:E95 = Row values
F2:F95 = Intermediate value (10^(E2/10))
K2 = Final Value (10*LOG(AVERAGE(F2:F95)))

90th Percentile:
=PERCENTILE(C2:C95,0.1)

Log Average Intermediate: Column F(conversion units)
=10^(E2/10)
and then using the result...
Log Average: Column K(Leq)
=10*LOG(AVERAGE(F2:F95))

Excel Results are here

``````		SPL	MAX	Leq	conversion units	L1	L10 	L90	Lmax	Leq
1	18/10/2013 8:24	37.7	39.6	38.65	7328.245331	40	37	29	49	35
2	18/10/2013 8:24	34.6	33.7	34.15	2600.159563
3	18/10/2013 8:24	28.7	33.5	31.1	1288.249552
4	18/10/2013 8:24	28.5	37.5	33	1995.262315
5	18/10/2013 8:24	32.5	33.7	33.1	2041.737945
6	18/10/2013 8:24	28.7	40.4	34.55	2851.018268
7	18/10/2013 8:24	35.4	37.3	36.35	4315.190768
8	18/10/2013 8:25	32.3	33.8	33.05	2018.366364
9	18/10/2013 8:25	28.8	33.8	31.3	1348.962883
10	18/10/2013 8:25	28.8	37.3	33.05	2018.366364
11	18/10/2013 8:25	32.3	33.9	33.1	2041.737945
12	18/10/2013 8:25	28.9	40.7	34.8	3019.95172
13	18/10/2013 8:25	35.7	34	34.85	3054.921113
14	18/10/2013 8:26	29	33.9	31.45	1396.368361
15	18/10/2013 8:26	28.9	37.5	33.2	2089.296131
16	18/10/2013 8:26	32.5	33.8	33.15	2065.380156
17	18/10/2013 8:26	28.8	34.2	31.5	1412.537545
18	18/10/2013 8:26	29.2	37.5	33.35	2162.718524
19	18/10/2013 8:26	32.5	34.3	33.4	2187.761624
20	18/10/2013 8:27	29.3	37.7	33.5	2238.721139
21	18/10/2013 8:27	32.7	34.2	33.45	2213.09471
22	18/10/2013 8:27	29.2	34.2	31.7	1479.108388
23	18/10/2013 8:27	29.2	37.5	33.35	2162.718524
24	18/10/2013 8:27	32.5	34.5	33.5	2238.721139
25	18/10/2013 8:27	29.5	40.8	35.15	3273.406949
26	18/10/2013 8:28	35.8	34.6	35.2	3311.311215
27	18/10/2013 8:28	29.6	34.5	32.05	1603.245391
28	18/10/2013 8:28	29.5	41	35.25	3349.654392
29	18/10/2013 8:28	36	34.5	35.25	3349.654392
30	18/10/2013 8:28	29.5	39.9	34.7	2951.209227
31	18/10/2013 8:29	34.9	34.9	34.9	3090.295433
32	18/10/2013 8:29	29.9	40.8	35.35	3427.677865
33	18/10/2013 8:29	35.8	37.9	36.85	4841.723676
34	18/10/2013 8:29	32.9	34.7	33.8	2398.832919
35	18/10/2013 8:29	29.7	40.8	35.25	3349.654392
36	18/10/2013 8:30	35.8	37.8	36.8	4786.300923
37	18/10/2013 8:30	32.8	34.4	33.6	2290.867653
38	18/10/2013 8:30	29.4	42.2	35.8	3801.893963
39	18/10/2013 8:30	37.2	34.6	35.9	3890.45145
40	18/10/2013 8:30	29.6	34.8	32.2	1659.586907
41	18/10/2013 8:30	29.8	37.8	33.8	2398.832919
42	18/10/2013 8:31	32.8	34.7	33.75	2371.373706
43	18/10/2013 8:31	29.7	41.2	35.45	3507.51874
44	18/10/2013 8:31	36.2	38	37.1	5128.61384
45	18/10/2013 8:31	33	34.6	33.8	2398.832919
46	18/10/2013 8:31	29.6	41	35.3	3388.441561
47	18/10/2013 8:31	36	34.8	35.4	3467.368505
48	18/10/2013 8:32	29.8	40.9	35.35	3427.677865
49	18/10/2013 8:32	35.9	35	35.45	3507.51874
50	18/10/2013 8:32	30	34.9	32.45	1757.923614
51	18/10/2013 8:32	29.9	41.3	35.6	3630.780548
52	18/10/2013 8:32	36.3	37.9	37.1	5128.61384
53	18/10/2013 8:32	32.9	35.1	34	2511.886432
54	18/10/2013 8:33	30.1	38.1	34.1	2570.395783
55	18/10/2013 8:33	33.1	35.1	34.1	2570.395783
56	18/10/2013 8:33	30.1	35.2	32.65	1840.772001
57	18/10/2013 8:33	30.2	38.1	34.15	2600.159563
58	18/10/2013 8:33	33.1	36.1	34.6	2884.031503
59	18/10/2013 8:33	31.1	48.9	40	10000
60	18/10/2013 8:34	43.9	41.2	42.55	17988.70915
61	18/10/2013 8:34	36.2	35.8	36	3981.071706
62	18/10/2013 8:34	30.8	41.7	36.25	4216.965034
63	18/10/2013 8:34	36.7	38.9	37.8	6025.595861
64	18/10/2013 8:34	33.9	35.8	34.85	3054.921113
65	18/10/2013 8:34	30.8	42.2	36.5	4466.835922
66	18/10/2013 8:35	37.2	35.6	36.4	4365.158322
67	18/10/2013 8:35	30.6	35.6	33.1	2041.737945
68	18/10/2013 8:35	30.6	39.2	34.9	3090.295433
69	18/10/2013 8:35	34.2	35.6	34.9	3090.295433
70	18/10/2013 8:35	30.6	42.2	36.4	4365.158322
71	18/10/2013 8:35	37.2	37.9	37.55	5688.529308
72	18/10/2013 8:36	32.9	35.4	34.15	2600.159563
73	18/10/2013 8:36	30.4	41.7	36.05	4027.170343
74	18/10/2013 8:36	36.7	35.1	35.9	3890.45145
75	18/10/2013 8:36	30.1	35.5	32.8	1905.460718
76	18/10/2013 8:36	30.5	39.3	34.9	3090.295433
77	18/10/2013 8:36	34.3	35.1	34.7	2951.209227
78	18/10/2013 8:37	30.1	43	36.55	4518.559444
79	18/10/2013 8:37	38	39.5	38.75	7498.942093
80	18/10/2013 8:37	34.5	35	34.75	2985.382619
81	18/10/2013 8:37	30	43.3	36.65	4623.810214
82	18/10/2013 8:37	38.3	34.9	36.6	4570.881896
83	18/10/2013 8:37	29.9	43.2	36.55	4518.559444
84	18/10/2013 8:38	38.2	39.5	38.85	7673.614894
85	18/10/2013 8:38	34.5	35	34.75	2985.382619
86	18/10/2013 8:38	30	42.8	36.4	4365.158322
87	18/10/2013 8:38	37.8	37.7	37.75	5956.621435
88	18/10/2013 8:38	32.7	34.9	33.8	2398.832919
89	18/10/2013 8:38	29.9	44.3	37.1	5128.61384
90	18/10/2013 8:39	39.3	34.8	37.05	5069.907083
91	18/10/2013 8:39	29.8	43	36.4	4365.158322
92	18/10/2013 8:39	38	35.4	36.7	4677.351413
93	18/10/2013 8:39	30.4	44	37.2	5248.074602
94	18/10/2013 8:39	39	5	22	158.4893192
``````

Column E - Leq (Starting 38.65)
F - Conversion Units (Starting 7327.245331) - this is an intermediate value for K
I - L90 (90th Percentile) 29 here (for that 15 minute interval)
K - Log Average - 35 here (for that 15 minute interval)

So, I am looking for the values I and K for each 15 min intervals throughout a day (24*4 = 96 values appx)
0

LVL 49

Expert Comment

ID: 39650395
Is there some magic missing?

Your log data holds 2 fields LastUpdated and Value
is column C (raw) Value?

what is column D?
and how does one arrive at column E?

or is column E the raw value? (in which case how do you arrive at column C)

:( confused ):
0

Author Comment

ID: 39650434
Sorry about not removing the stuff not relevant.

OK, for Log Averages here is the table...

Raw Values(Column C) = Raw Value (starting with 38.65)
Conversion Units (Column D) = Excel equation => =10^(C2/10) (starting with 7328.245331)
Leq (Column E) = Excel equation => =10*LOG(AVERAGE(D2:D95)) -- This is the last figure I need (96 of these per day) (starting with 35)

``````		Raw	conversion units	Leq
1	18/10/2013 8:24	38.65	7328.245331	35
2	18/10/2013 8:24	34.15	2600.159563
3	18/10/2013 8:24	31.1	1288.249552
4	18/10/2013 8:24	33	1995.262315
5	18/10/2013 8:24	33.1	2041.737945
6	18/10/2013 8:24	34.55	2851.018268
7	18/10/2013 8:24	36.35	4315.190768
8	18/10/2013 8:25	33.05	2018.366364
9	18/10/2013 8:25	31.3	1348.962883
10	18/10/2013 8:25	33.05	2018.366364
11	18/10/2013 8:25	33.1	2041.737945
12	18/10/2013 8:25	34.8	3019.95172
13	18/10/2013 8:25	34.85	3054.921113
14	18/10/2013 8:26	31.45	1396.368361
15	18/10/2013 8:26	33.2	2089.296131
16	18/10/2013 8:26	33.15	2065.380156
17	18/10/2013 8:26	31.5	1412.537545
18	18/10/2013 8:26	33.35	2162.718524
19	18/10/2013 8:26	33.4	2187.761624
20	18/10/2013 8:27	33.5	2238.721139
21	18/10/2013 8:27	33.45	2213.09471
22	18/10/2013 8:27	31.7	1479.108388
23	18/10/2013 8:27	33.35	2162.718524
24	18/10/2013 8:27	33.5	2238.721139
25	18/10/2013 8:27	35.15	3273.406949
26	18/10/2013 8:28	35.2	3311.311215
27	18/10/2013 8:28	32.05	1603.245391
28	18/10/2013 8:28	35.25	3349.654392
29	18/10/2013 8:28	35.25	3349.654392
30	18/10/2013 8:28	34.7	2951.209227
31	18/10/2013 8:29	34.9	3090.295433
32	18/10/2013 8:29	35.35	3427.677865
33	18/10/2013 8:29	36.85	4841.723676
34	18/10/2013 8:29	33.8	2398.832919
35	18/10/2013 8:29	35.25	3349.654392
36	18/10/2013 8:30	36.8	4786.300923
37	18/10/2013 8:30	33.6	2290.867653
38	18/10/2013 8:30	35.8	3801.893963
39	18/10/2013 8:30	35.9	3890.45145
40	18/10/2013 8:30	32.2	1659.586907
41	18/10/2013 8:30	33.8	2398.832919
42	18/10/2013 8:31	33.75	2371.373706
43	18/10/2013 8:31	35.45	3507.51874
44	18/10/2013 8:31	37.1	5128.61384
45	18/10/2013 8:31	33.8	2398.832919
46	18/10/2013 8:31	35.3	3388.441561
47	18/10/2013 8:31	35.4	3467.368505
48	18/10/2013 8:32	35.35	3427.677865
49	18/10/2013 8:32	35.45	3507.51874
50	18/10/2013 8:32	32.45	1757.923614
51	18/10/2013 8:32	35.6	3630.780548
52	18/10/2013 8:32	37.1	5128.61384
53	18/10/2013 8:32	34	2511.886432
54	18/10/2013 8:33	34.1	2570.395783
55	18/10/2013 8:33	34.1	2570.395783
56	18/10/2013 8:33	32.65	1840.772001
57	18/10/2013 8:33	34.15	2600.159563
58	18/10/2013 8:33	34.6	2884.031503
59	18/10/2013 8:33	40	10000
60	18/10/2013 8:34	42.55	17988.70915
61	18/10/2013 8:34	36	3981.071706
62	18/10/2013 8:34	36.25	4216.965034
63	18/10/2013 8:34	37.8	6025.595861
64	18/10/2013 8:34	34.85	3054.921113
65	18/10/2013 8:34	36.5	4466.835922
66	18/10/2013 8:35	36.4	4365.158322
67	18/10/2013 8:35	33.1	2041.737945
68	18/10/2013 8:35	34.9	3090.295433
69	18/10/2013 8:35	34.9	3090.295433
70	18/10/2013 8:35	36.4	4365.158322
71	18/10/2013 8:35	37.55	5688.529308
72	18/10/2013 8:36	34.15	2600.159563
73	18/10/2013 8:36	36.05	4027.170343
74	18/10/2013 8:36	35.9	3890.45145
75	18/10/2013 8:36	32.8	1905.460718
76	18/10/2013 8:36	34.9	3090.295433
77	18/10/2013 8:36	34.7	2951.209227
78	18/10/2013 8:37	36.55	4518.559444
79	18/10/2013 8:37	38.75	7498.942093
80	18/10/2013 8:37	34.75	2985.382619
81	18/10/2013 8:37	36.65	4623.810214
82	18/10/2013 8:37	36.6	4570.881896
83	18/10/2013 8:37	36.55	4518.559444
84	18/10/2013 8:38	38.85	7673.614894
85	18/10/2013 8:38	34.75	2985.382619
86	18/10/2013 8:38	36.4	4365.158322
87	18/10/2013 8:38	37.75	5956.621435
88	18/10/2013 8:38	33.8	2398.832919
89	18/10/2013 8:38	37.1	5128.61384
90	18/10/2013 8:39	37.05	5069.907083
91	18/10/2013 8:39	36.4	4365.158322
92	18/10/2013 8:39	36.7	4677.351413
93	18/10/2013 8:39	37.2	5248.074602
94	18/10/2013 8:39	22	158.4893192
``````

For Percentiles

Raw Values(Column C) = Raw Value (starting with 37.7)
L90 (Column D) = 90th Percentile (Excel equation=> =PERCENTILE(C2:C95,0.1)) (starting with 29)

``````		Raw	L90
1	18/10/2013 8:24	37.7	29
2	18/10/2013 8:24	34.6
3	18/10/2013 8:24	28.7
4	18/10/2013 8:24	28.5
5	18/10/2013 8:24	32.5
6	18/10/2013 8:24	28.7
7	18/10/2013 8:24	35.4
8	18/10/2013 8:25	32.3
9	18/10/2013 8:25	28.8
10	18/10/2013 8:25	28.8
11	18/10/2013 8:25	32.3
12	18/10/2013 8:25	28.9
13	18/10/2013 8:25	35.7
14	18/10/2013 8:26	29
15	18/10/2013 8:26	28.9
16	18/10/2013 8:26	32.5
17	18/10/2013 8:26	28.8
18	18/10/2013 8:26	29.2
19	18/10/2013 8:26	32.5
20	18/10/2013 8:27	29.3
21	18/10/2013 8:27	32.7
22	18/10/2013 8:27	29.2
23	18/10/2013 8:27	29.2
24	18/10/2013 8:27	32.5
25	18/10/2013 8:27	29.5
26	18/10/2013 8:28	35.8
27	18/10/2013 8:28	29.6
28	18/10/2013 8:28	29.5
29	18/10/2013 8:28	36
30	18/10/2013 8:28	29.5
31	18/10/2013 8:29	34.9
32	18/10/2013 8:29	29.9
33	18/10/2013 8:29	35.8
34	18/10/2013 8:29	32.9
35	18/10/2013 8:29	29.7
36	18/10/2013 8:30	35.8
37	18/10/2013 8:30	32.8
38	18/10/2013 8:30	29.4
39	18/10/2013 8:30	37.2
40	18/10/2013 8:30	29.6
41	18/10/2013 8:30	29.8
42	18/10/2013 8:31	32.8
43	18/10/2013 8:31	29.7
44	18/10/2013 8:31	36.2
45	18/10/2013 8:31	33
46	18/10/2013 8:31	29.6
47	18/10/2013 8:31	36
48	18/10/2013 8:32	29.8
49	18/10/2013 8:32	35.9
50	18/10/2013 8:32	30
51	18/10/2013 8:32	29.9
52	18/10/2013 8:32	36.3
53	18/10/2013 8:32	32.9
54	18/10/2013 8:33	30.1
55	18/10/2013 8:33	33.1
56	18/10/2013 8:33	30.1
57	18/10/2013 8:33	30.2
58	18/10/2013 8:33	33.1
59	18/10/2013 8:33	31.1
60	18/10/2013 8:34	43.9
61	18/10/2013 8:34	36.2
62	18/10/2013 8:34	30.8
63	18/10/2013 8:34	36.7
64	18/10/2013 8:34	33.9
65	18/10/2013 8:34	30.8
66	18/10/2013 8:35	37.2
67	18/10/2013 8:35	30.6
68	18/10/2013 8:35	30.6
69	18/10/2013 8:35	34.2
70	18/10/2013 8:35	30.6
71	18/10/2013 8:35	37.2
72	18/10/2013 8:36	32.9
73	18/10/2013 8:36	30.4
74	18/10/2013 8:36	36.7
75	18/10/2013 8:36	30.1
76	18/10/2013 8:36	30.5
77	18/10/2013 8:36	34.3
78	18/10/2013 8:37	30.1
79	18/10/2013 8:37	38
80	18/10/2013 8:37	34.5
81	18/10/2013 8:37	30
82	18/10/2013 8:37	38.3
83	18/10/2013 8:37	29.9
84	18/10/2013 8:38	38.2
85	18/10/2013 8:38	34.5
86	18/10/2013 8:38	30
87	18/10/2013 8:38	37.8
88	18/10/2013 8:38	32.7
89	18/10/2013 8:38	29.9
90	18/10/2013 8:39	39.3
91	18/10/2013 8:39	29.8
92	18/10/2013 8:39	38
93	18/10/2013 8:39	30.4
94	18/10/2013 8:39	39
``````
0

LVL 49

Expert Comment

ID: 39650504
Thanks!

But I will be unable to agree with your figures IF my rounding of minutes is correct.

Currently your data might fit inside a 15 minute period, but you are free to choose when that period starts e.g. you can start at:
18/10/2013 8:24
and finish at
18/10/2013 8:39

but if I'm rounding off into 15 minute chunks starting at 00:00:00 then
18/10/2013 8:24 through 18/10/2013 8:29:59.99999 is in one chunk
and
18/10/2013 8:30 through 18/10/2013 8:44.59.99999 is in another chunk

I think I have the percentile worked out, working on that log thingy
0

LVL 49

Expert Comment

ID: 39650533
OK, believe I have both calculations now. Here's the latest query:
``````SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.1 THEN mx END) AS pct_90
, 10*log10(avg(pwr))                       AS log_avg
FROM (
SELECT
spl
, RoundTime
, mx
, 1.0 * (rank() over (partition BY RoundTime ORDER BY MX)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
, CONVERSION
, power(10.0,[conversion]/10.0) pwr
FROM LogData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
) x
GROUP BY
RoundTime
;
``````
I have introduced another cross apply to further simplify the date/time rounding.

SQL 2008 does not have a "percentile" and the closest is percent_rank in SQL 2012
that calculation can be mimicked by calculating rank() and count() over the rounded date/time range, like so:

1.0 * (rank-1) / (count-1)

replacing "rank-1" with "(rank() over (partition BY RoundTime ORDER BY MX)-1)" and
replacing "count-1" with " (count(*) over (partition BY RoundTime)-1)"
we arrive at the code of line 10 above.

So we can calculate the percentile rank of each row. In the outer query we then use a case expression to look for percentile ranks <= 0.1 {this is the equivalent of the Excel percentile(--range--,0.1) } and we take the maximum value that falls into that range of percentiles.

For log average:
just like the Excel sheet we calculate the power(10,value) for each row (equivalent of 10^(C2/10)).
Then in the outer query we take an average of that calculation over the time range, get the log (base 10) of that number and multiply by 10.

Here's my summary result:
``````|                      ROUNDTIME | PCT_90 |         LOG_AVG |
|--------------------------------|--------|-----------------|
| October, 18 2013 08:15:00+0000 |   29.2 | 35.475119445584 |
``````
And here's all the details of how I got there:
``````    CREATE TABLE LogData
([SPL] int, [LastUpdated] datetime, [MX] decimal(18,6), [conversion] decimal(18,6))
;

INSERT INTO LogData
([SPL], [LastUpdated], [MX], [conversion])
VALUES
(1, '2013-10-18 08:24:00', 37.7, 38.65),
(2, '2013-10-18 08:24:00', 34.6, 34.15),
(3, '2013-10-18 08:24:00', 28.7, 31.1),
(4, '2013-10-18 08:24:00', 28.5, 33),
(5, '2013-10-18 08:24:00', 32.5, 33.1),
(6, '2013-10-18 08:24:00', 28.7, 34.55),
(7, '2013-10-18 08:24:00', 35.4, 36.35),
(8, '2013-10-18 08:25:00', 32.3, 33.05),
(9, '2013-10-18 08:25:00', 28.8, 31.3),
(10, '2013-10-18 08:25:00', 28.8, 33.05),
(11, '2013-10-18 08:25:00', 32.3, 33.1),
(12, '2013-10-18 08:25:00', 28.9, 34.8),
(13, '2013-10-18 08:25:00', 35.7, 34.85),
(14, '2013-10-18 08:26:00', 29, 31.45),
(15, '2013-10-18 08:26:00', 28.9, 33.2),
(16, '2013-10-18 08:26:00', 32.5, 33.15),
(17, '2013-10-18 08:26:00', 28.8, 31.5),
(18, '2013-10-18 08:26:00', 29.2, 33.35),
(19, '2013-10-18 08:26:00', 32.5, 33.4),
(20, '2013-10-18 08:27:00', 29.3, 33.5),
(21, '2013-10-18 08:27:00', 32.7, 33.45),
(22, '2013-10-18 08:27:00', 29.2, 31.7),
(23, '2013-10-18 08:27:00', 29.2, 33.35),
(24, '2013-10-18 08:27:00', 32.5, 33.5),
(25, '2013-10-18 08:27:00', 29.5, 35.15),
(26, '2013-10-18 08:28:00', 35.8, 35.2),
(27, '2013-10-18 08:28:00', 29.6, 32.05),
(28, '2013-10-18 08:28:00', 29.5, 35.25),
(29, '2013-10-18 08:28:00', 36, 35.25),
(30, '2013-10-18 08:28:00', 29.5, 34.7),
(31, '2013-10-18 08:29:00', 34.9, 34.9),
(32, '2013-10-18 08:29:00', 29.9, 35.35),
(33, '2013-10-18 08:29:00', 35.8, 36.85),
(34, '2013-10-18 08:29:00', 32.9, 33.8),
(35, '2013-10-18 08:29:00', 29.7, 35.25),
(36, '2013-10-18 08:30:00', 35.8, 36.8),
(37, '2013-10-18 08:30:00', 32.8, 33.6),
(38, '2013-10-18 08:30:00', 29.4, 35.8),
(39, '2013-10-18 08:30:00', 37.2, 35.9),
(40, '2013-10-18 08:30:00', 29.6, 32.2),
(41, '2013-10-18 08:30:00', 29.8, 33.8),
(42, '2013-10-18 08:31:00', 32.8, 33.75),
(43, '2013-10-18 08:31:00', 29.7, 35.45),
(44, '2013-10-18 08:31:00', 36.2, 37.1),
(45, '2013-10-18 08:31:00', 33, 33.8),
(46, '2013-10-18 08:31:00', 29.6, 35.3),
(47, '2013-10-18 08:31:00', 36, 35.4),
(48, '2013-10-18 08:32:00', 29.8, 35.35),
(49, '2013-10-18 08:32:00', 35.9, 35.45),
(50, '2013-10-18 08:32:00', 30, 32.45),
(51, '2013-10-18 08:32:00', 29.9, 35.6),
(52, '2013-10-18 08:32:00', 36.3, 37.1),
(53, '2013-10-18 08:32:00', 32.9, 34),
(54, '2013-10-18 08:33:00', 30.1, 34.1),
(55, '2013-10-18 08:33:00', 33.1, 34.1),
(56, '2013-10-18 08:33:00', 30.1, 32.65),
(57, '2013-10-18 08:33:00', 30.2, 34.15),
(58, '2013-10-18 08:33:00', 33.1, 34.6),
(59, '2013-10-18 08:33:00', 31.1, 40),
(60, '2013-10-18 08:34:00', 43.9, 42.55),
(61, '2013-10-18 08:34:00', 36.2, 36),
(62, '2013-10-18 08:34:00', 30.8, 36.25),
(63, '2013-10-18 08:34:00', 36.7, 37.8),
(64, '2013-10-18 08:34:00', 33.9, 34.85),
(65, '2013-10-18 08:34:00', 30.8, 36.5),
(66, '2013-10-18 08:35:00', 37.2, 36.4),
(67, '2013-10-18 08:35:00', 30.6, 33.1),
(68, '2013-10-18 08:35:00', 30.6, 34.9),
(69, '2013-10-18 08:35:00', 34.2, 34.9),
(70, '2013-10-18 08:35:00', 30.6, 36.4),
(71, '2013-10-18 08:35:00', 37.2, 37.55),
(72, '2013-10-18 08:36:00', 32.9, 34.15),
(73, '2013-10-18 08:36:00', 30.4, 36.05),
(74, '2013-10-18 08:36:00', 36.7, 35.9),
(75, '2013-10-18 08:36:00', 30.1, 32.8),
(76, '2013-10-18 08:36:00', 30.5, 34.9),
(77, '2013-10-18 08:36:00', 34.3, 34.7),
(78, '2013-10-18 08:37:00', 30.1, 36.55),
(79, '2013-10-18 08:37:00', 38, 38.75),
(80, '2013-10-18 08:37:00', 34.5, 34.75),
(81, '2013-10-18 08:37:00', 30, 36.65),
(82, '2013-10-18 08:37:00', 38.3, 36.6),
(83, '2013-10-18 08:37:00', 29.9, 36.55),
(84, '2013-10-18 08:38:00', 38.2, 38.85),
(85, '2013-10-18 08:38:00', 34.5, 34.75),
(86, '2013-10-18 08:38:00', 30, 36.4),
(87, '2013-10-18 08:38:00', 37.8, 37.75),
(88, '2013-10-18 08:38:00', 32.7, 33.8),
(89, '2013-10-18 08:38:00', 29.9, 37.1),
(90, '2013-10-18 08:39:00', 39.3, 37.05),
(91, '2013-10-18 08:39:00', 29.8, 36.4),
(92, '2013-10-18 08:39:00', 38, 36.7),
(93, '2013-10-18 08:39:00', 30.4, 37.2),
(94, '2013-10-18 08:39:00', 39, 22)
;
update logdata
;
update logdata
where lastUpdated >= '2013-10-18 08:30:00'
;

**Query 1**:

select
RoundTime
, max(case when pct_rnk <=0.1 then mx end) as pct_90
, 10*log10(avg(pwr))                       as log_avg
from (
select
spl
, RoundTime
, mx
, 1.0 * (rank() over (partition by RoundTime order by MX)-1) / (count(*) over (partition by RoundTime)-1) as pct_rnk
, conversion
, power(10.0,conversion/10.0) pwr
FROM LogData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
) x
group by
RoundTime

**[Results][2]**:

|                      ROUNDTIME | PCT_90 |         LOG_AVG |
|--------------------------------|--------|-----------------|
| October, 18 2013 08:15:00+0000 |   29.2 | 35.475119445584 |

**Query 2**:

select
*
from (
select
spl
, RoundTime
, mx
, 1.0 * (rank() over (partition by RoundTime order by MX)-1) / (count(*) over (partition by RoundTime)-1) as pct_rnk
, conversion
, power(10.0,conversion/10.0) pwr
FROM LogData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
) x
order by mx

**[Results][3]**:

| SPL |                      ROUNDTIME |   MX |        PCT_RNK | CONVERSION |     PWR |
|-----|--------------------------------|------|----------------|------------|---------|
|   4 | October, 18 2013 08:15:00+0000 | 28.5 |              0 |         33 |  1995.3 |
|   6 | October, 18 2013 08:15:00+0000 | 28.7 | 0.010752688172 |      34.55 |    2851 |
|   3 | October, 18 2013 08:15:00+0000 | 28.7 | 0.010752688172 |       31.1 |  1288.2 |
|   9 | October, 18 2013 08:15:00+0000 | 28.8 | 0.032258064516 |       31.3 |    1349 |
|  10 | October, 18 2013 08:15:00+0000 | 28.8 | 0.032258064516 |      33.05 |  2018.4 |
|  17 | October, 18 2013 08:15:00+0000 | 28.8 | 0.032258064516 |       31.5 |  1412.5 |
|  15 | October, 18 2013 08:15:00+0000 | 28.9 | 0.064516129032 |       33.2 |  2089.3 |
|  12 | October, 18 2013 08:15:00+0000 | 28.9 | 0.064516129032 |       34.8 |    3020 |
|  14 | October, 18 2013 08:15:00+0000 |   29 | 0.086021505376 |      31.45 |  1396.4 |
|  22 | October, 18 2013 08:15:00+0000 | 29.2 | 0.096774193548 |       31.7 |  1479.1 |
|  23 | October, 18 2013 08:15:00+0000 | 29.2 | 0.096774193548 |      33.35 |  2162.7 |
|  18 | October, 18 2013 08:15:00+0000 | 29.2 | 0.096774193548 |      33.35 |  2162.7 |
|  20 | October, 18 2013 08:15:00+0000 | 29.3 | 0.129032258064 |       33.5 |  2238.7 |
|  38 | October, 18 2013 08:15:00+0000 | 29.4 | 0.139784946236 |       35.8 |  3801.9 |
|  25 | October, 18 2013 08:15:00+0000 | 29.5 | 0.150537634408 |      35.15 |  3273.4 |
|  28 | October, 18 2013 08:15:00+0000 | 29.5 | 0.150537634408 |      35.25 |  3349.7 |
|  30 | October, 18 2013 08:15:00+0000 | 29.5 | 0.150537634408 |       34.7 |  2951.2 |
|  27 | October, 18 2013 08:15:00+0000 | 29.6 | 0.182795698924 |      32.05 |  1603.2 |
|  40 | October, 18 2013 08:15:00+0000 | 29.6 | 0.182795698924 |       32.2 |  1659.6 |
|  46 | October, 18 2013 08:15:00+0000 | 29.6 | 0.182795698924 |       35.3 |  3388.4 |
|  43 | October, 18 2013 08:15:00+0000 | 29.7 |  0.21505376344 |      35.45 |  3507.5 |
|  35 | October, 18 2013 08:15:00+0000 | 29.7 |  0.21505376344 |      35.25 |  3349.7 |
|  41 | October, 18 2013 08:15:00+0000 | 29.8 | 0.236559139784 |       33.8 |  2398.8 |
|  48 | October, 18 2013 08:15:00+0000 | 29.8 | 0.236559139784 |      35.35 |  3427.7 |
|  91 | October, 18 2013 08:15:00+0000 | 29.8 | 0.236559139784 |       36.4 |  4365.2 |
|  89 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 |       37.1 |  5128.6 |
|  83 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 |      36.55 |  4518.6 |
|  51 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 |       35.6 |  3630.8 |
|  32 | October, 18 2013 08:15:00+0000 | 29.9 | 0.268817204301 |      35.35 |  3427.7 |
|  50 | October, 18 2013 08:15:00+0000 |   30 | 0.311827956989 |      32.45 |  1757.9 |
|  81 | October, 18 2013 08:15:00+0000 |   30 | 0.311827956989 |      36.65 |  4623.8 |
|  86 | October, 18 2013 08:15:00+0000 |   30 | 0.311827956989 |       36.4 |  4365.2 |
|  75 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 |       32.8 |  1905.5 |
|  78 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 |      36.55 |  4518.6 |
|  54 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 |       34.1 |  2570.4 |
|  56 | October, 18 2013 08:15:00+0000 | 30.1 | 0.344086021505 |      32.65 |  1840.8 |
|  57 | October, 18 2013 08:15:00+0000 | 30.2 | 0.387096774193 |      34.15 |  2600.2 |
|  73 | October, 18 2013 08:15:00+0000 | 30.4 | 0.397849462365 |      36.05 |  4027.2 |
|  93 | October, 18 2013 08:15:00+0000 | 30.4 | 0.397849462365 |       37.2 |  5248.1 |
|  76 | October, 18 2013 08:15:00+0000 | 30.5 | 0.419354838709 |       34.9 |  3090.3 |
|  67 | October, 18 2013 08:15:00+0000 | 30.6 | 0.430107526881 |       33.1 |  2041.7 |
|  68 | October, 18 2013 08:15:00+0000 | 30.6 | 0.430107526881 |       34.9 |  3090.3 |
|  70 | October, 18 2013 08:15:00+0000 | 30.6 | 0.430107526881 |       36.4 |  4365.2 |
|  62 | October, 18 2013 08:15:00+0000 | 30.8 | 0.462365591397 |      36.25 |    4217 |
|  65 | October, 18 2013 08:15:00+0000 | 30.8 | 0.462365591397 |       36.5 |  4466.8 |
|  59 | October, 18 2013 08:15:00+0000 | 31.1 | 0.483870967741 |         40 |   10000 |
|  11 | October, 18 2013 08:15:00+0000 | 32.3 | 0.494623655913 |       33.1 |  2041.7 |
|   8 | October, 18 2013 08:15:00+0000 | 32.3 | 0.494623655913 |      33.05 |  2018.4 |
|   5 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 |       33.1 |  2041.7 |
|  19 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 |       33.4 |  2187.8 |
|  16 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 |      33.15 |  2065.4 |
|  24 | October, 18 2013 08:15:00+0000 | 32.5 | 0.516129032258 |       33.5 |  2238.7 |
|  21 | October, 18 2013 08:15:00+0000 | 32.7 | 0.559139784946 |      33.45 |  2213.1 |
|  88 | October, 18 2013 08:15:00+0000 | 32.7 | 0.559139784946 |       33.8 |  2398.8 |
|  42 | October, 18 2013 08:15:00+0000 | 32.8 |  0.58064516129 |      33.75 |  2371.4 |
|  37 | October, 18 2013 08:15:00+0000 | 32.8 |  0.58064516129 |       33.6 |  2290.9 |
|  34 | October, 18 2013 08:15:00+0000 | 32.9 | 0.602150537634 |       33.8 |  2398.8 |
|  53 | October, 18 2013 08:15:00+0000 | 32.9 | 0.602150537634 |         34 |  2511.9 |
|  72 | October, 18 2013 08:15:00+0000 | 32.9 | 0.602150537634 |      34.15 |  2600.2 |
|  45 | October, 18 2013 08:15:00+0000 |   33 |  0.63440860215 |       33.8 |  2398.8 |
|  58 | October, 18 2013 08:15:00+0000 | 33.1 | 0.645161290322 |       34.6 |    2884 |
|  55 | October, 18 2013 08:15:00+0000 | 33.1 | 0.645161290322 |       34.1 |  2570.4 |
|  64 | October, 18 2013 08:15:00+0000 | 33.9 | 0.666666666666 |      34.85 |  3054.9 |
|  69 | October, 18 2013 08:15:00+0000 | 34.2 | 0.677419354838 |       34.9 |  3090.3 |
|  77 | October, 18 2013 08:15:00+0000 | 34.3 |  0.68817204301 |       34.7 |  2951.2 |
|  80 | October, 18 2013 08:15:00+0000 | 34.5 | 0.698924731182 |      34.75 |  2985.4 |
|  85 | October, 18 2013 08:15:00+0000 | 34.5 | 0.698924731182 |      34.75 |  2985.4 |
|   2 | October, 18 2013 08:15:00+0000 | 34.6 | 0.720430107526 |      34.15 |  2600.2 |
|  31 | October, 18 2013 08:15:00+0000 | 34.9 | 0.731182795698 |       34.9 |  3090.3 |
|   7 | October, 18 2013 08:15:00+0000 | 35.4 |  0.74193548387 |      36.35 |  4315.2 |
|  13 | October, 18 2013 08:15:00+0000 | 35.7 | 0.752688172043 |      34.85 |  3054.9 |
|  33 | October, 18 2013 08:15:00+0000 | 35.8 | 0.763440860215 |      36.85 |  4841.7 |
|  36 | October, 18 2013 08:15:00+0000 | 35.8 | 0.763440860215 |       36.8 |  4786.3 |
|  26 | October, 18 2013 08:15:00+0000 | 35.8 | 0.763440860215 |       35.2 |  3311.3 |
|  49 | October, 18 2013 08:15:00+0000 | 35.9 | 0.795698924731 |      35.45 |  3507.5 |
|  47 | October, 18 2013 08:15:00+0000 |   36 | 0.806451612903 |       35.4 |  3467.4 |
|  29 | October, 18 2013 08:15:00+0000 |   36 | 0.806451612903 |      35.25 |  3349.7 |
|  44 | October, 18 2013 08:15:00+0000 | 36.2 | 0.827956989247 |       37.1 |  5128.6 |
|  61 | October, 18 2013 08:15:00+0000 | 36.2 | 0.827956989247 |         36 |  3981.1 |
|  52 | October, 18 2013 08:15:00+0000 | 36.3 | 0.849462365591 |       37.1 |  5128.6 |
|  63 | October, 18 2013 08:15:00+0000 | 36.7 | 0.860215053763 |       37.8 |  6025.6 |
|  74 | October, 18 2013 08:15:00+0000 | 36.7 | 0.860215053763 |       35.9 |  3890.5 |
|  66 | October, 18 2013 08:15:00+0000 | 37.2 | 0.881720430107 |       36.4 |  4365.2 |
|  71 | October, 18 2013 08:15:00+0000 | 37.2 | 0.881720430107 |      37.55 |  5688.5 |
|  39 | October, 18 2013 08:15:00+0000 | 37.2 | 0.881720430107 |       35.9 |  3890.5 |
|   1 | October, 18 2013 08:15:00+0000 | 37.7 | 0.913978494623 |      38.65 |  7328.2 |
|  87 | October, 18 2013 08:15:00+0000 | 37.8 | 0.924731182795 |      37.75 |  5956.6 |
|  79 | October, 18 2013 08:15:00+0000 |   38 | 0.935483870967 |      38.75 |  7498.9 |
|  92 | October, 18 2013 08:15:00+0000 |   38 | 0.935483870967 |       36.7 |  4677.4 |
|  84 | October, 18 2013 08:15:00+0000 | 38.2 | 0.956989247311 |      38.85 |  7673.6 |
|  82 | October, 18 2013 08:15:00+0000 | 38.3 | 0.967741935483 |       36.6 |  4570.9 |
|  94 | October, 18 2013 08:15:00+0000 |   39 | 0.978494623655 |         22 |   158.5 |
|  90 | October, 18 2013 08:15:00+0000 | 39.3 | 0.989247311827 |      37.05 |  5069.9 |
|  60 | October, 18 2013 08:15:00+0000 | 43.9 |              1 |      42.55 | 17988.7 |

[1]: http://sqlfiddle.com/#!6/1f107/26
``````
Note I had to "fiddle" with the date/time data to get the sample to fall into a single 15 minute block. I do hope that you understand that this 15 minute block thingy may be very different to the way you have been doing it.
0

Author Comment

ID: 39664656
Hi PortletPaul,

Again a BIG THANK YOU !!! for your effort.
Sorry to be late as I was in a training for 3 days.

I have made slight modifications to your code and got the results below.
Not sure whether I am correct here.
[conversion] was an intermediate results field used to calculate the final figure for log averages.
I will do the same calculation in Excel to compare and let you know.

QUERY:
``````SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.1 THEN Profile1Max END) AS pct_90
, 10*log10(avg(pwr)) AS log_avg
FROM (
SELECT
Profile1SPL
, RoundTime
, Profile1Max
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1Max)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
--, CONVERSION
, power(10.0,Profile1SPL/10.0) pwr
FROM tblData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
WHERE  LastUpdated >= DATEADD(DAY, - 1, GETDATE())
) x
GROUP BY
RoundTime
ORDER BY RoundTime DESC
``````

RESULT
``````RoundTime	pct_90	log_avg
2013-11-21 12:30:00.000	48.6	41.0962210607613
2013-11-21 12:15:00.000	48.3	41.027225936539
2013-11-21 12:00:00.000	46.6	40.9559594671983
2013-11-21 11:45:00.000	46.5	40.626818537446
2013-11-21 11:30:00.000	49.6	40.1710675946921
2013-11-21 11:15:00.000	49.6	42.0051599849093
2013-11-21 11:00:00.000	55	49.0805636735977
2013-11-21 10:45:00.000	55	44.5929421612782
2013-11-21 10:30:00.000	47.9	42.0143837288056
2013-11-21 10:15:00.000	43.6	38.6689407535204
2013-11-21 10:00:00.000	43.6	38.855074988565
2013-11-21 09:45:00.000	47	35.5209367119439
2013-11-21 09:30:00.000	46.5	37.0223293205764
2013-11-21 09:15:00.000	46.5	43.3415207584883
2013-11-21 09:00:00.000	42.7	36.1604770278021
2013-11-21 08:45:00.000	49.3	46.591476650127
2013-11-21 08:30:00.000	42.2	34.2426885109341
2013-11-21 08:15:00.000	42.2	41.6085462543056
2013-11-21 08:00:00.000	47.5	47.2133036630461
2013-11-21 07:45:00.000	46.5	50.4995275713551
2013-11-21 07:30:00.000	47.1	50.1065446902524
2013-11-21 07:15:00.000	45.6	38.1131522276916
2013-11-21 07:00:00.000	44.3	40.7576429037151
2013-11-21 06:45:00.000	44.6	39.2511519743263
2013-11-21 06:30:00.000	45.8	40.9247680970029
2013-11-21 06:15:00.000	46.3	39.9523675901929
2013-11-21 06:00:00.000	46.2	40.0533580586587
2013-11-21 05:45:00.000	47	41.2210550277655
2013-11-21 05:30:00.000	47.3	63.8522987496146
2013-11-21 05:15:00.000	45.2	39.8888114310436
2013-11-21 05:00:00.000	45	38.4699040428038
2013-11-21 04:45:00.000	44.7	38.5041030516464
2013-11-21 04:30:00.000	45.2	38.6234382548523
2013-11-21 04:15:00.000	45	39.1906937149834
2013-11-21 04:00:00.000	45.5	39.1317015219302
2013-11-21 03:45:00.000	45.6	40.4040570659257
2013-11-21 03:30:00.000	51.7	41.4124840049934
2013-11-21 03:15:00.000	46	39.9616391218388
2013-11-21 03:00:00.000	43.9	40.5008623916
2013-11-21 02:45:00.000	42.8	50.1860696236656
2013-11-21 02:30:00.000	42.7	35.8714908710706
2013-11-21 02:15:00.000	42.7	36.2149848270692
2013-11-21 02:00:00.000	43.2	36.2802276061611
2013-11-21 01:45:00.000	43.2	39.3158596958275
2013-11-21 01:30:00.000	46	39.9320843657334
2013-11-21 01:15:00.000	44.3	38.5928316980589
2013-11-21 01:00:00.000	43.9	37.7768650634313
2013-11-21 00:45:00.000	42.1	36.237346586401
2013-11-21 00:30:00.000	41.8	36.6590219206403
2013-11-21 00:15:00.000	43.4	36.7877407270377
2013-11-21 00:00:00.000	43.7	37.4356166279691
2013-11-20 23:45:00.000	43.4	37.7080593842999
2013-11-20 23:30:00.000	44.2	37.8546504386512
2013-11-20 23:15:00.000	43.8	37.6786311736249
2013-11-20 23:00:00.000	43.2	38.9932195239975
2013-11-20 22:45:00.000	47.7	42.056619824346
2013-11-20 22:30:00.000	44.4	41.6898213016489
2013-11-20 22:15:00.000	43.6	37.178976385976
2013-11-20 22:00:00.000	43.5	36.9316980384705
2013-11-20 21:45:00.000	43.8	36.7512506144218
2013-11-20 21:30:00.000	43.2	36.6808509202342
2013-11-20 21:15:00.000	44	37.2528292941613
2013-11-20 21:00:00.000	44.6	37.7352217807028
2013-11-20 20:45:00.000	44.2	37.549360685269
2013-11-20 20:30:00.000	45.5	40.3264700937799
2013-11-20 20:15:00.000	44	40.9080145217
2013-11-20 20:00:00.000	46.8	41.7761683117893
2013-11-20 19:45:00.000	47.4	39.7432304103787
2013-11-20 19:30:00.000	43.7	41.9559077888977
2013-11-20 19:15:00.000	41.8	35.2635367310855
2013-11-20 19:00:00.000	41.8	34.8151667670832
2013-11-20 18:45:00.000	42.8	39.9518257518398
2013-11-20 18:30:00.000	48.4	38.5622199016268
2013-11-20 18:15:00.000	43.5	38.7290740806834
2013-11-20 18:00:00.000	42.4	36.3329170206036
2013-11-20 17:45:00.000	44.6	38.3585901357174
2013-11-20 17:30:00.000	44.1	37.3230095549285
2013-11-20 17:15:00.000	44.8	36.8309083003418
2013-11-20 17:00:00.000	44.5	37.2819222909208
2013-11-20 16:45:00.000	43.2	36.999904694127
2013-11-20 16:30:00.000	43.1	37.2229273338889
2013-11-20 16:15:00.000	43.8	38.5562233311211
2013-11-20 16:00:00.000	46.4	38.9579512670805
2013-11-20 15:45:00.000	48.1	39.9695311348039
2013-11-20 15:30:00.000	48.1	41.4395532163997
2013-11-20 15:15:00.000	44.8	37.6779096925193
2013-11-20 15:00:00.000	44.8	37.189353270936
2013-11-20 14:45:00.000	45.2	39.8761432073611
2013-11-20 14:30:00.000	48.8	44.8643906282321
2013-11-20 14:15:00.000	47.4	42.4319310463433
2013-11-20 14:00:00.000	49.7	44.5791297626209
2013-11-20 13:45:00.000	48.8	41.257385616175
2013-11-20 13:30:00.000	47.6	40.9373270471593
2013-11-20 13:15:00.000	47.5	39.9557382646957
2013-11-20 13:00:00.000	47.3	40.1554665980193
2013-11-20 12:45:00.000	45.3	37.4396175315547
2013-11-20 12:30:00.000	44.7	42.2826483442148
``````
0

LVL 49

Expert Comment

ID: 39664675
Looks OK to me, seems you commented out [conversion] which is fine and added a where clause, also fine. Did I miss anything?

More importantly I guess we'll wait to see if the calculations are correct.
0

Author Comment

ID: 39673508
Hi, I am back...

I got a sample set of data and added to an Excel sheet to compare the results with the results from your query.
It seems the values are having big differences.
Not sure my changes to the query caused this.
I have attached the Excel sheet for your reference.
Can you please check it (the highlighted values) and let me know what could be wrong.
Really sorry to be a pain, but this is a big headache to me and now I want to get this done soon.

Latest Query

``````SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.99 THEN Profile1SPL END) AS pct_01 -- Percentile 01 using SPL
, max(CASE WHEN pct_rnk <=0.9 THEN Profile1SPL END) AS pct_10 -- Percentile 10 using SPL
, max(CASE WHEN pct_rnk <=0.1 THEN Profile1SPL END) AS pct_90 -- Percentile 90 using SPL
, 10*log10(avg(pwr)) AS log_avg -- Log Avg using Leq
FROM (
SELECT
Profile1SPL
, RoundTime
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
, power(10.0,Profile1Leq/10.<wbr ></wbr>0) pwr -- Log Avg using Leq
FROM v24HourRawData
CROSS APPLY (SELECT convert(datetime,convert(d<wbr ></wbr>ate,LastUp<wbr ></wbr>dated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,<wbr ></wbr>LastUpdate<wbr ></wbr>d) / 15) * 15, day_only) AS RoundTime) AS ca2
WHERE  LastUpdated >= DATEADD(DAY, - 1, GETDATE())
) x
GROUP BY
RoundTime
ORDER BY RoundTime DESC

;
``````

View - v24HourRawData

``````ID             LastUpdated            Profile1SPL Profile1LEQ
365647	2013-11-25 11:59:52.000	37.5	39.2
365646	2013-11-25 11:59:32.000	27.2	39.5
365645	2013-11-25 11:59:21.000	36.5	39.7
365644	2013-11-25 11:59:11.000	28.5	39.6
365643	2013-11-25 11:59:02.000	28.2	39.3
365642	2013-11-25 11:58:52.000	38.5	39
365641	2013-11-25 11:58:41.000	28.3	38.7
365640	2013-11-25 11:58:32.000	28.7	37.9
365639	2013-11-25 11:58:22.000	41.3	31.7
365638	2013-11-25 11:58:11.000	30	40.7
365637	2013-11-25 11:58:02.000	33.3	40.7
365636	2013-11-25 11:57:51.000	38.4	40.7
365635	2013-11-25 11:57:41.000	29.2	40.8
365634	2013-11-25 11:57:33.000	28.8	40.8
365633	2013-11-25 11:57:22.000	28.6	40.8
365632	2013-11-25 11:57:11.000	28.4	40.8
365631	2013-11-25 11:57:01.000	37.4	40.8
365630	2013-11-25 11:56:51.000	28.7	40.9
365629	2013-11-25 11:56:40.000	37.7	40.9
365628	2013-11-25 11:56:30.000	42.3	40.9
365627	2013-11-25 11:56:21.000	28.2	40.9
365626	2013-11-25 11:56:10.000	42.2	40.9
365625	2013-11-25 11:56:02.000	28.7	40.9
365624	2013-11-25 11:55:51.000	28.3	40.9
365623	2013-11-25 11:55:41.000	43.9	40.9
365622	2013-11-25 11:55:30.000	28.3	40.9
365621	2013-11-25 11:55:20.000	40.1	40.9
365620	2013-11-25 11:55:11.000	43.8	40.9
365619	2013-11-25 11:55:00.000	28.5	40.9
365618	2013-11-25 11:54:49.000	42.7	40.9
365617	2013-11-25 11:54:42.000	43.2	40.9
365616	2013-11-25 11:54:25.000	36.6	40.9
365615	2013-11-25 11:54:17.000	35.1	41
365614	2013-11-25 11:54:08.000	39.3	41
365613	2013-11-25 11:53:55.000	28.5	41.1
365612	2013-11-25 11:53:45.000	38.1	41.1
365611	2013-11-25 11:53:37.000	45.1	41.1
365610	2013-11-25 11:53:25.000	28.2	41.2
365609	2013-11-25 11:53:15.000	40.6	41.2
365608	2013-11-25 11:53:04.000	33.1	41.2
365607	2013-11-25 11:52:56.000	41.4	41.2
365606	2013-11-25 11:52:47.000	42.5	41.2
365605	2013-11-25 11:52:34.000	40.6	41.2
365604	2013-11-25 11:52:26.000	38.6	41.2
365603	2013-11-25 11:52:15.000	42.3	41.2
365602	2013-11-25 11:52:04.000	28.4	41.2
365601	2013-11-25 11:51:54.000	38.6	41.2
365600	2013-11-25 11:51:44.000	28.6	41.2
365599	2013-11-25 11:51:34.000	28.9	41.3
365598	2013-11-25 11:51:23.000	29	41.3
365597	2013-11-25 11:51:04.000	28.7	41.4
365596	2013-11-25 11:50:53.000	28.3	41.4
365595	2013-11-25 11:50:43.000	28.3	41.4
365594	2013-11-25 11:50:26.000	40.1	41.4
365593	2013-11-25 11:50:13.000	28.3	41.4
365592	2013-11-25 11:50:02.000	28.7	41.4
365591	2013-11-25 11:49:43.000	45.6	41.2
365590	2013-11-25 11:49:19.000	41.9	40.4
365589	2013-11-25 11:49:10.000	28.8	40.4
365588	2013-11-25 11:48:59.000	48	40.1
365587	2013-11-25 11:48:52.000	47.8	39.6
365586	2013-11-25 11:48:39.000	29	39.2
365585	2013-11-25 11:48:30.000	28.4	39
365584	2013-11-25 11:48:19.000	43.6	38.9
365583	2013-11-25 11:48:09.000	28.8	38.8
365582	2013-11-25 11:47:59.000	39.7	38.7
365581	2013-11-25 11:47:49.000	28.6	38.5
365580	2013-11-25 11:47:38.000	28.5	38.5
365579	2013-11-25 11:47:28.000	42.6	38.3
365578	2013-11-25 11:47:19.000	29.9	38.1
365577	2013-11-25 11:47:08.000	37.5	38
365576	2013-11-25 11:46:58.000	42.3	37.8
365575	2013-11-25 11:46:48.000	33	37.6
365574	2013-11-25 11:46:38.000	40.5	37.4
365573	2013-11-25 11:46:28.000	28.6	37.1
365572	2013-11-25 11:46:18.000	28.6	36.8
365571	2013-11-25 11:46:08.000	43	36.3
365570	2013-11-25 11:45:58.000	28.7	35.9
365569	2013-11-25 11:45:48.000	41.2	35.7
365568	2013-11-25 11:45:40.000	28.6	35.3
365567	2013-11-25 11:45:27.000	34.9	35.4
365566	2013-11-25 11:45:19.000	28.3	35.4
365565	2013-11-25 11:45:07.000	28.7	35.4
``````
0

Author Comment

ID: 39673514
Excel spreadsheet as per above comment...
Test-2013-11-25.xls
0

Author Comment

ID: 39673519
Query Result

``````RoundTime	pct_01	pct_10	pct_90	log_avg
2013-11-25 12:30:00.000	38.3	37.4	28.2	34.9465461191538
2013-11-25 12:15:00.000	38.9	36.9	28.1	34.199407193288
2013-11-25 12:00:00.000	40.9	38.1	27.2	36.8297643306629
2013-11-25 11:45:00.000	47.8	42.7	28.3	40.0816843286949
2013-11-25 11:30:00.000	44.2	39.2	28.5	38.2664756683601
2013-11-25 11:15:00.000	43.6	41.3	28.3	38.7652666873982
``````
0

Author Comment

ID: 39673523

Also...

I am getting the following error time-to-time from the above query:

Msg 8134, Level 16, State 1, Procedure 15MinGraphData, Line 7
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

Again, thanks a lot for all the help.
0

LVL 49

Expert Comment

ID: 39676320
This is not an error, it is a warning which means exactly what is says:
Warning: Null value is eliminated by an aggregate or other SET operation.

i.e. inside an aggregate function a NULL value was encountered and it has been ignored.

This warning can be suppressed using:
SET ANSI_WARNINGS { ON | OFF }

Divide by zero is an error, however I'm not sure what you want to do here.
Look at line 11 of the latest query, this has a division.

, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk

what do you want to do if
(count(*) over (partition BY RoundTime)-1
is zero?
0

Author Comment

ID: 39676359
Thank you.
I would like to display the RoundTime and 'NULL' for the outputs.

Did you get any chance to compare the excel sheet results with the query output to see why is it different?
0

LVL 49

Expert Comment

ID: 39676396
>>"I would like to display the RoundTime and 'NULL' for the outputs."
so for this you use a case expression, such as

, case when count(*) over (partition BY RoundTime) > 1 then
1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1)
end AS pct_rnk

>>Did you get any chance to compare the excel sheet results with the query output to see why is it different?
no, did you compare them?, What are the results of that?
0

Author Comment

ID: 39676426
Thanks, again. that fixed the warning issue.

Yes I checked the spreadsheet and the results are having a big difference.
I have added the results from the query too in the spreadsheet (Comment ID: 39673508).

I can't understand the using of "MAX" function in the query?
0

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39676459
if in doubt, use the inner query to inspect the details. I have added a few extra columns as well. Here are the results.
``````**Query 1**:

SELECT
RoundTime
, max(CASE WHEN pct_rnk <=0.99 THEN Profile1SPL END) AS pct_01 -- Percentile 01 using SPL
, max(CASE WHEN pct_rnk <=0.9 THEN Profile1SPL END) AS pct_10 -- Percentile 10 using SPL
, max(CASE WHEN pct_rnk <=0.1 THEN Profile1SPL END) AS pct_90 -- Percentile 90 using SPL
, 10*log10(avg(pwr)) AS log_avg -- Log Avg using Leq
FROM (
SELECT
Profile1SPL
, RoundTime
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
, power(10.0,Profile1Leq/10.0) pwr -- Log Avg using Leq
FROM v24HourRawData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
WHERE  LastUpdated >= DATEADD(DAY, - 1, GETDATE())
) x
GROUP BY
RoundTime
ORDER BY RoundTime DESC

**[Results][2]**:

|                       ROUNDTIME | PCT_01 | PCT_10 | PCT_90 |         LOG_AVG |
|---------------------------------|--------|--------|--------|-----------------|
| November, 25 2013 11:45:00+0000 |   47.8 |   42.7 |   28.3 | 40.081684328695 |

**Query 2**:

SELECT
RoundTime
, Profile1SPL
, Profile1LEQ
, rank() over (partition BY RoundTime ORDER BY Profile1SPL) AS pct_rank
, count(*) over (partition BY RoundTime) AS pct_count
, 1.0 * (rank() over (partition BY RoundTime ORDER BY Profile1SPL)-1) / (count(*) over (partition BY RoundTime)-1) AS pct_rnk
, power(10.0,Profile1Leq/10.0) pwr -- Log Avg using Leq
FROM v24HourRawData
CROSS APPLY (SELECT convert(datetime,convert(date,LastUpdated)) AS day_only) AS ca1
CROSS APPLY (SELECT dateadd(MINUTE, (datediff(MINUTE,day_only,LastUpdated) / 15) * 15, day_only) AS RoundTime) AS ca2
WHERE  LastUpdated >= DATEADD(DAY, - 1, GETDATE())

**[Results][3]**:

|                       ROUNDTIME | PROFILE1SPL | PROFILE1LEQ | PCT_RANK | PCT_COUNT |        PCT_RNK |     PWR |
|---------------------------------|-------------|-------------|----------|-----------|----------------|---------|
| November, 25 2013 11:45:00+0000 |        27.2 |        39.5 |        1 |        83 |              0 |  8912.5 |
| November, 25 2013 11:45:00+0000 |        28.2 |        39.3 |        2 |        83 | 0.012195121951 |  8511.4 |
| November, 25 2013 11:45:00+0000 |        28.2 |        40.9 |        2 |        83 | 0.012195121951 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        28.2 |        41.2 |        2 |        83 | 0.012195121951 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        28.3 |        40.9 |        5 |        83 | 0.048780487804 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        28.3 |        40.9 |        5 |        83 | 0.048780487804 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        28.3 |        38.7 |        5 |        83 | 0.048780487804 |  7413.1 |
| November, 25 2013 11:45:00+0000 |        28.3 |        41.4 |        5 |        83 | 0.048780487804 | 13803.8 |
| November, 25 2013 11:45:00+0000 |        28.3 |        41.4 |        5 |        83 | 0.048780487804 | 13803.8 |
| November, 25 2013 11:45:00+0000 |        28.3 |        41.4 |        5 |        83 | 0.048780487804 | 13803.8 |
| November, 25 2013 11:45:00+0000 |        28.3 |        35.4 |        5 |        83 | 0.048780487804 |  3467.4 |
| November, 25 2013 11:45:00+0000 |        28.4 |        41.2 |       12 |        83 | 0.134146341463 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        28.4 |          39 |       12 |        83 | 0.134146341463 |  7943.3 |
| November, 25 2013 11:45:00+0000 |        28.4 |        40.8 |       12 |        83 | 0.134146341463 | 12022.6 |
| November, 25 2013 11:45:00+0000 |        28.5 |        39.6 |       15 |        83 | 0.170731707317 |  9120.1 |
| November, 25 2013 11:45:00+0000 |        28.5 |        41.1 |       15 |        83 | 0.170731707317 | 12882.5 |
| November, 25 2013 11:45:00+0000 |        28.5 |        40.9 |       15 |        83 | 0.170731707317 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        28.5 |        38.5 |       15 |        83 | 0.170731707317 |  7079.5 |
| November, 25 2013 11:45:00+0000 |        28.6 |        37.1 |       19 |        83 | 0.219512195121 |  5128.6 |
| November, 25 2013 11:45:00+0000 |        28.6 |        36.8 |       19 |        83 | 0.219512195121 |  4786.3 |
| November, 25 2013 11:45:00+0000 |        28.6 |        41.2 |       19 |        83 | 0.219512195121 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        28.6 |        40.8 |       19 |        83 | 0.219512195121 | 12022.6 |
| November, 25 2013 11:45:00+0000 |        28.6 |        38.5 |       19 |        83 | 0.219512195121 |  7079.5 |
| November, 25 2013 11:45:00+0000 |        28.6 |        35.3 |       19 |        83 | 0.219512195121 |  3388.4 |
| November, 25 2013 11:45:00+0000 |        28.7 |        35.9 |       25 |        83 | 0.292682926829 |  3890.5 |
| November, 25 2013 11:45:00+0000 |        28.7 |        35.4 |       25 |        83 | 0.292682926829 |  3467.4 |
| November, 25 2013 11:45:00+0000 |        28.7 |        40.9 |       25 |        83 | 0.292682926829 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        28.7 |        37.9 |       25 |        83 | 0.292682926829 |    6166 |
| November, 25 2013 11:45:00+0000 |        28.7 |        40.9 |       25 |        83 | 0.292682926829 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        28.7 |        41.4 |       25 |        83 | 0.292682926829 | 13803.8 |
| November, 25 2013 11:45:00+0000 |        28.7 |        41.4 |       25 |        83 | 0.292682926829 | 13803.8 |
| November, 25 2013 11:45:00+0000 |        28.8 |        38.8 |       32 |        83 | 0.378048780487 |  7585.8 |
| November, 25 2013 11:45:00+0000 |        28.8 |        40.4 |       32 |        83 | 0.378048780487 | 10964.8 |
| November, 25 2013 11:45:00+0000 |        28.8 |        40.8 |       32 |        83 | 0.378048780487 | 12022.6 |
| November, 25 2013 11:45:00+0000 |        28.9 |        41.3 |       35 |        83 | 0.414634146341 | 13489.6 |
| November, 25 2013 11:45:00+0000 |          29 |        41.3 |       36 |        83 | 0.426829268292 | 13489.6 |
| November, 25 2013 11:45:00+0000 |          29 |        39.2 |       36 |        83 | 0.426829268292 |  8317.6 |
| November, 25 2013 11:45:00+0000 |        29.2 |        40.8 |       38 |        83 | 0.451219512195 | 12022.6 |
| November, 25 2013 11:45:00+0000 |        29.9 |        38.1 |       39 |        83 | 0.463414634146 |  6456.5 |
| November, 25 2013 11:45:00+0000 |          30 |        40.7 |       40 |        83 | 0.475609756097 |   11749 |
| November, 25 2013 11:45:00+0000 |          33 |        37.6 |       41 |        83 | 0.487804878048 |  5754.4 |
| November, 25 2013 11:45:00+0000 |        33.1 |        41.2 |       42 |        83 |            0.5 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        33.3 |        40.7 |       43 |        83 | 0.512195121951 |   11749 |
| November, 25 2013 11:45:00+0000 |        34.9 |        35.4 |       44 |        83 | 0.524390243902 |  3467.4 |
| November, 25 2013 11:45:00+0000 |        35.1 |          41 |       45 |        83 | 0.536585365853 | 12589.3 |
| November, 25 2013 11:45:00+0000 |        36.5 |        39.7 |       46 |        83 | 0.548780487804 |  9332.5 |
| November, 25 2013 11:45:00+0000 |        36.6 |        40.9 |       47 |        83 | 0.560975609756 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        37.4 |        40.8 |       48 |        83 | 0.573170731707 | 12022.6 |
| November, 25 2013 11:45:00+0000 |        37.5 |        39.2 |       49 |        83 | 0.585365853658 |  8317.6 |
| November, 25 2013 11:45:00+0000 |        37.5 |          38 |       49 |        83 | 0.585365853658 |  6309.6 |
| November, 25 2013 11:45:00+0000 |        37.7 |        40.9 |       51 |        83 |  0.60975609756 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        38.1 |        41.1 |       52 |        83 | 0.621951219512 | 12882.5 |
| November, 25 2013 11:45:00+0000 |        38.4 |        40.7 |       53 |        83 | 0.634146341463 |   11749 |
| November, 25 2013 11:45:00+0000 |        38.5 |          39 |       54 |        83 | 0.646341463414 |  7943.3 |
| November, 25 2013 11:45:00+0000 |        38.6 |        41.2 |       55 |        83 | 0.658536585365 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        38.6 |        41.2 |       55 |        83 | 0.658536585365 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        39.3 |          41 |       57 |        83 | 0.682926829268 | 12589.3 |
| November, 25 2013 11:45:00+0000 |        39.7 |        38.7 |       58 |        83 | 0.695121951219 |  7413.1 |
| November, 25 2013 11:45:00+0000 |        40.1 |        41.4 |       59 |        83 |  0.70731707317 | 13803.8 |
| November, 25 2013 11:45:00+0000 |        40.1 |        40.9 |       59 |        83 |  0.70731707317 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        40.5 |        37.4 |       61 |        83 | 0.731707317073 |  5495.4 |
| November, 25 2013 11:45:00+0000 |        40.6 |        41.2 |       62 |        83 | 0.743902439024 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        40.6 |        41.2 |       62 |        83 | 0.743902439024 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        41.2 |        35.7 |       64 |        83 | 0.768292682926 |  3715.4 |
| November, 25 2013 11:45:00+0000 |        41.3 |        31.7 |       65 |        83 | 0.780487804878 |  1479.1 |
| November, 25 2013 11:45:00+0000 |        41.4 |        41.2 |       66 |        83 | 0.792682926829 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        41.9 |        40.4 |       67 |        83 |  0.80487804878 | 10964.8 |
| November, 25 2013 11:45:00+0000 |        42.2 |        40.9 |       68 |        83 | 0.817073170731 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        42.3 |        40.9 |       69 |        83 | 0.829268292682 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        42.3 |        41.2 |       69 |        83 | 0.829268292682 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        42.3 |        37.8 |       69 |        83 | 0.829268292682 |  6025.6 |
| November, 25 2013 11:45:00+0000 |        42.5 |        41.2 |       72 |        83 | 0.865853658536 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        42.6 |        38.3 |       73 |        83 | 0.878048780487 |  6760.8 |
| November, 25 2013 11:45:00+0000 |        42.7 |        40.9 |       74 |        83 | 0.890243902439 | 12302.7 |
| November, 25 2013 11:45:00+0000 |          43 |        36.3 |       75 |        83 |  0.90243902439 |  4265.8 |
| November, 25 2013 11:45:00+0000 |        43.2 |        40.9 |       76 |        83 | 0.914634146341 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        43.6 |        38.9 |       77 |        83 | 0.926829268292 |  7762.5 |
| November, 25 2013 11:45:00+0000 |        43.8 |        40.9 |       78 |        83 | 0.939024390243 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        43.9 |        40.9 |       79 |        83 | 0.951219512195 | 12302.7 |
| November, 25 2013 11:45:00+0000 |        45.1 |        41.1 |       80 |        83 | 0.963414634146 | 12882.5 |
| November, 25 2013 11:45:00+0000 |        45.6 |        41.2 |       81 |        83 | 0.975609756097 | 13182.6 |
| November, 25 2013 11:45:00+0000 |        47.8 |        39.6 |       82 |        83 | 0.987804878048 |  9120.1 |
| November, 25 2013 11:45:00+0000 |          48 |        40.1 |       83 |        83 |              1 | 10232.9 |

[1]: http://sqlfiddle.com/#!3/74336/1
``````
0

Author Comment

ID: 39679650
Hi PortletPaul,

That explanation helped.
I have done some tests and got expected results.
So, now I have got everything I wanted with this.
Thanks a million for all the help provided throughout these days.
I wonder if I can add more points apart from the default 500 because your help was much more than that for me.
0

Author Closing Comment

ID: 39679652
Excellent !!!
0

LVL 49

Expert Comment

ID: 39679666
:) no 500 is the maximum but I appreciate the sentiment - thank you. Really pleased I could help out.

All the best, Paul
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
Is your data getting by on basic protection measures? In todayâ€™s climate of debilitating malware and ransomwareâ€”like WannaCryâ€”that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.â€¦
Loops Section Overview
###### Suggested Courses
Course of the Month8 days, 3 hours left to enroll