Solved

SQL Query for 15 minute Maximum values

Posted on 2013-11-14
22
858 Views
Last Modified: 2013-11-26
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
Comment
Question by:MrB8r
  • 12
  • 10
22 Comments
 
LVL 48

Expert Comment

by:PortletPaul
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)

Open in new window

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

by:MrB8r
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.
Could you please help with the log average and percentiles too.

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).

Hope I made it clear.
0
 
LVL 48

Expert Comment

by:PortletPaul
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

by:MrB8r
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					

Open in new window


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 48

Expert Comment

by:PortletPaul
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

by:MrB8r
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	

Open in new window


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	

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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 48

Expert Comment

by:PortletPaul
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
;

Open in new window

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 |

Open in new window

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
    set LastUpdated = dateadd(minute,-9,LastUpdated)
    ;
    update logdata
    set LastUpdated = dateadd(second,-1,LastUpdated)
    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

Open in new window

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

by:MrB8r
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  

Open in new window


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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
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

by:MrB8r
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  

;

Open in new window


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

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

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

Author Comment

by:MrB8r
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

Open in new window

0
 

Author Comment

by:MrB8r
ID: 39673523
P.S: I have added 2 more percentiles I wanted (1st & 10th in addition to the 90th we already had)

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 48

Expert Comment

by:PortletPaul
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

by:MrB8r
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 48

Expert Comment

by:PortletPaul
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

by:MrB8r
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).

Could you please help me to fix that?
I can't understand the using of "MAX" function in the query?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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

Open in new window

0
 

Author Comment

by:MrB8r
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

by:MrB8r
ID: 39679652
Excellent !!!
0
 
LVL 48

Expert Comment

by:PortletPaul
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now