interval calculation

Last Post 12 Aug 2008 04:09 AM by SwePeso. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
jmaikido
New Member
New Member

--
10 Aug 2008 10:48 PM
hi listers,

i'm having some difficulty trying to aggregate data extracted as a subset based on the following criteria:

* the interval must be continuous, ie. (mto) must equal the next (mfrom)
* the total interval, ie. min(mfrom) to the max(mto) where the intervals are continuous must be >= a particular value. In this case, selecting 4metres would return the interval 138.3 to 143, as this range contains results for continuous depth intervals.

* once having extracted the min(mfrom) and max(mto), i need to aggregate the value. ie. ave(result) for the interval. In this case it would be 4.7 as per 143 - 138.3m

Hope this is relatively clear?!

HoleID mfrom mto result
-------------------- ------------- ------------- -------------
TWDD0004 1 2 0.86
TWDD0004 3 4 8.93
TWDD0004 4 5 2.78
TWDD0004 8 9 1.21
TWDD0004 10 11 2.36
TWDD0004 11 12 0.86
TWDD0004 103 103.7 0.5
TWDD0004 121.65 122 0.5
TWDD0004 130 131 3.65
TWDD0004 131 131.5 1
TWDD0004 132 133 2.89
TWDD0004 133 134 4.02
TWDD0004 134 135 0.76
---------------------------------------------------------
TWDD0004 138.3 139 3.15
TWDD0004 139 140 3.12
TWDD0004 140 141 3.93
TWDD0004 141 142 6.48
TWDD0004 142 143 0.94
---------------------------------------------------------
TWDD0004 155 156.15 0.55
TWDD0004 164.9 165.75 2.08
TWDD0004 166.9 167.9 1.27
TWDD0004 167.9 169 0.58
TWDD0004 170.9 171.25 15


many, many TIA,
john
SwePeso
New Member
New Member

--
11 Aug 2008 01:15 AM
How can AVG(Result) be 4.7 for the suggested interval?
I get 3.524

(3.15 + 3.12 + 3.93 + 6.48 + 0.94) / 5.0 = 3.524
SwePeso
New Member
New Member

--
11 Aug 2008 01:17 AM
DECLARE @Sample TABLE (HoleID CHAR(8), mFrom SMALLMONEY, mTo SMALLMONEY, Result SMALLMONEY)

INSERT @Sample
SELECT 'TWDD0004', 1 , 2, 0.86 UNION ALL
SELECT 'TWDD0004', 3 , 4, 8.93 UNION ALL
SELECT 'TWDD0004', 4 , 5, 2.78 UNION ALL
SELECT 'TWDD0004', 8 , 9, 1.21 UNION ALL
SELECT 'TWDD0004', 10 , 11, 2.36 UNION ALL
SELECT 'TWDD0004', 11 , 12, 0.86 UNION ALL
SELECT 'TWDD0004', 103 , 103.7, 0.5 UNION ALL
SELECT 'TWDD0004', 121.65, 122, 0.5 UNION ALL
SELECT 'TWDD0004', 130 , 131, 3.65 UNION ALL
SELECT 'TWDD0004', 131 , 131.5, 1 UNION ALL
SELECT 'TWDD0004', 132 , 133, 2.89 UNION ALL
SELECT 'TWDD0004', 133 , 134, 4.02 UNION ALL
SELECT 'TWDD0004', 134 , 135, 0.76 UNION ALL
SELECT 'TWDD0004', 138.3 , 139, 3.15 UNION ALL
SELECT 'TWDD0004', 139 , 140, 3.12 UNION ALL
SELECT 'TWDD0004', 140 , 141, 3.93 UNION ALL
SELECT 'TWDD0004', 141 , 142, 6.48 UNION ALL
SELECT 'TWDD0004', 142 , 143, 0.94 UNION ALL
SELECT 'TWDD0004', 155 , 156.15, 0.55 UNION ALL
SELECT 'TWDD0004', 164.9 , 165.75, 2.08 UNION ALL
SELECT 'TWDD0004', 166.9 , 167.9, 1.27 UNION ALL
SELECT 'TWDD0004', 167.9 , 169, 0.58 UNION ALL
SELECT 'TWDD0004', 170.9 , 171.25, 15

DECLARE @WantedValue INT

SET @WantedValue = 4

;WITH Yak (HoleID, mFrom, mTo, Result, RecID)
AS (
SELECT HoleID,
mFrom,
mTo,
Result,
ROW_NUMBER() OVER (PARTITION BY HoleID ORDER BY mFrom)
FROM @Sample
), cte (HoleID, mFrom, mTo, Result, RecID, Seq)
AS (
SELECT HoleID,
mFrom,
mTo,
Result,
RecID,
0
FROM Yak
WHERE RecID = 1

UNION ALL

SELECT y.HoleID,
y.mFrom,
y.mTo,
y.Result,
y.RecID,
CASE
WHEN y.mFrom <= c.mTo THEN c.Seq
ELSE c.Seq + 1
END
FROM Yak AS y
INNER JOIN cte AS c ON c.HoleID = y.HoleID
WHERE c.RecID = y.RecID - 1
)

SELECT HoleID,
MIN(mFrom) AS mFrom,
MAX(mTo) AS mTo,
AVG(Result) AS Result
FROM cte
GROUP BY HoleID,
Seq
HAVING MAX(mTo) - MIN(mFrom) >= @WantedValue
jmaikido
New Member
New Member

--
11 Aug 2008 09:42 PM
hi Pesomannen,

your code worked an abs. 'treat' & i'm eternaly greatful for assistance, truly. Now that uv cut-ur-teeth on Part 1, there is ONE more Part to complete the scenario! The results otained so far, using a '@wantedvalue >= 4, returned:

HoleID mFrom mTo Result
-------------------- --------------------- --------------------- ---------------------
TWDD0004 138.30 143.00 3.524
TWDD0004 185.00 195.50 5.2628
TWDD0004 195.80 202.55 5.8337

Part 2, is prhaps more complex (certanly for me, but prob. v.easy 4u). As you are now aware, the code works for averaging the result for continuous intervals >= '@wantedvalue'. From above, there is a gap in the interval from 195.5 to 195.8. In the mining industry, this gap between groups of results, (in our case, >=4), is known as 'waste'. What I'm, (actually you), are working toward, is to have an @wantedvalue & an @wastevalue. By specifying an @wastevalue, we are saying ... 'give me the ave(results), for continuous intervals, >= @wantedvalue (ie. max(mfrom) - min(mto)) AND, where the difference in the gap <= @wastevalue, continue the sequence and aggregation until the next(mfrom) - previous(mto) > the @wastevalue. In this case, specifying an @wantedvalue = 4 & @wastevalue =1 would produce:

HoleID mFrom mTo Result
-------------------- --------------------- --------------------- ---------------------
TWDD0004 138.30 143.00 3.524
TWDD0004 185.00 202.55 6.85 ?? (or similar)

Here's a sample of the actual data values from 185m fro testing:

TWDD0004 185 186 2.96
TWDD0004 186 187.3 0.86
TWDD0004 187.3 188 8.15
TWDD0004 188 188.45 26.1
TWDD0004 188.45 189 16.5
TWDD0004 189 189.9 3.55
TWDD0004 189.9 190.2 2.79
TWDD0004 190.2 191 1.07
TWDD0004 191 191.85 1.3
TWDD0004 191.85 192.85 2.22
TWDD0004 192.85 193.3 0.59
TWDD0004 193.3 194 0.79
TWDD0004 194 194.85 5.98
TWDD0004 194.85 195.5 0.82
>> interval gap ---------------------------------------
TWDD0004 195.8 197 7.03
TWDD0004 197 198 3.84
TWDD0004 198 198.3 12.1
TWDD0004 198.3 199 8.66
TWDD0004 199 200 3.53
TWDD0004 200 201 3.22
TWDD0004 201 202.1 7.22
TWDD0004 202.1 202.55 1.07

Many, many TIA,
John
SwePeso
New Member
New Member

--
12 Aug 2008 04:09 AM
With @WantedValue = 4 and @WasteValue = 1
is this the desired result from all the sample data?

HoleID mFrom mTo Result
TWDD0004 1,00 5,00 4,19
TWDD0004 8,00 12,00 1,4766
TWDD0004 130,00 135,00 2,464
TWDD0004 138,30 143,00 3,524
TWDD0004 185,00 202,55 5,4704
SwePeso
New Member
New Member

--
12 Aug 2008 04:27 AM
See code posted here
Thinking outside the box


Acceptable Use Policy
---