--1. First we need to calculate min, max, of the data.
SELECT min(TrnValue) as MinValue, Max(TrnValue) as MaxValue,
max(TrnValue)-min(TrnValue) as RangeOfValue
INTO #query1
FROM tblInvMovement;
--2. The formula to divide the sorted set into buckets by value is:
-- Bucket = INT((Data(i) - Min(Data)) / (Max(Data) - Min(Data) *
-- (Buckets-1) + 1)
-- Note that we group the data by bucket and return the max and min
-- values in each bucket
SELECT CAST((TrnValue - MinValue)/(RangeOfValue) * (12 - 1) + 1 As Int) AS Bucket,Count(*) as Frequency,Min(TrnValue) AS MinBucketValue,Max(TrnValue) As MaxBucketValue,Avg(TrnValue) as AvgBucketValue
INTO #query2
FROM tblInvMovement A
CROSS JOIN #query1 B
GROUP BY CAST((TrnValue - MinValue)/(RangeOfValue) * (12 - 1) + 1 As Int)
ORDER BY Bucket;
--3. Now we have to address the issue that there may not be data in all
-- the buckets, so some buckets are missing from the list we created.
-- http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html
SELECT ints.i
INTO #query3
FROM (SELECT i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
FROM
(SELECT i = 0 UNION SELECT 1) AS i1 ,
(SELECT i = 0 UNION SELECT 2) AS i2 ,
(SELECT i = 0 UNION SELECT 4) AS i3 ,
(SELECT i = 0 UNION SELECT 8) AS i4 ,
(SELECT i = 0 UNION SELECT 16) AS i5 ,
(SELECT i = 0 UNION SELECT 32) AS i6
) AS ints LEFT OUTER JOIN #query2 ON ints.i = #query2.bucket
WHERE #query2.bucket IS NULL
AND ints.i <= 12
AND ints.i <> 0
ORDER BY ints.i
-4. Now we use a union query to union the results
SELECT *
FROM #query2
UNION ALL
SELECT i, 0,0,0,0
FROM #query3
ORDER BY Bucket;