May 11, 2012 at 10:18 am
I have a table which contains package id's, location, and package weight
Package Id Location weight
------------ --------- --------
1 1 1.08
2 1 1.78
3 1 10.77
4 2 0.45
5 1 2.33
6 3 12.22
7 3 1.77
etc
I need to write a sp where you can pass in a weight resolution for example 1.00
and have it break up the data into each location, with the total number of packages in that range and total weight in 1.00 increments
For example output should look like this.
The limiting factor for each location will be the max weight that location contains (For location 1, all 1.00 increments up to 11.00 as 10.77 is the max weight in that location)
Location Range Quantity Total
--------- ----------- --------- ------
1 0.00-1.00 0 0
1 1.00-2.00 2 2.86
1 2.00-3.00 1 2.33
.
.
.
2 0.00-1.00 1 0.45
.
.
3 0.00-1.00 0 0.00
3 1.00-2.00 1 1.77
etc
Any experts out there who can point me in the right direction ?
May 11, 2012 at 10:31 am
The way to get the weight grouping you're after is the FLOOR command against a decimal.
The code would look something like this:-
IF OBJECT_ID('tempdb..#Package') IS NOT NULL
DROP TABLE #Package
CREATE TABLE #Package
(PackageID int identity(1,1) Primary Key
, Location int
, Weight decimal(9,2))
INSERT INTO #Package
VALUES
(1,1.08)
INSERT INTO #Package
VALUES
(1,1.78)
INSERT INTO #Package
VALUES
(1,10.77)
INSERT INTO #Package
VALUES
(2,0.45)
INSERT INTO #Package
VALUES
(1,2.33)
INSERT INTO #Package
VALUES
(3,12.22)
INSERT INTO #Package
VALUES
(3,1.77)
SELECT Location
, FLOOR(Weight) AS WeightGrouping
, COUNT(*) AS Quantity
, MAX(Weight) AS MaxWeight
FROM #Package
GROUP BY Location
, FLOOR(Weight)
ORDER BY Location, WeightGrouping
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 13, 2012 at 9:09 pm
If you need to fill in all the gaps created by your resolution, you may be able to use the following:
DECLARE @w TABLE (pkg_id INT, location INT, weight DECIMAL(9,2))
DECLARE @res DECIMAL(9,2)
SET @res = 1
INSERT INTO @w
SELECT 1,1,1.08
UNION ALL SELECT 2,1,1.78
UNION ALL SELECT 3,1,10.77
UNION ALL SELECT 4,2,0.45
UNION ALL SELECT 5,1,2.33
UNION ALL SELECT 6,3,12.22
UNION ALL SELECT 7,3,1.77
;WITH Tally (n) AS (
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2)
,Wts AS (
SELECT pkg_id, location, weight
,@res*CEILING(weight/@res) AS maxwt
,@res*CEILING(weight/@res)-@res AS minwt
FROM @w
)
SELECT x.location, x.minwt, x.maxwt, SUM(ISNULL(x.weight, 0)) As Weight, COUNT(x.weight) As QTY
FROM Wts w
CROSS APPLY (
SELECT n, w.location
,CASE WHEN w.weight >= n and w.weight < n + @res THEN weight ELSE NULL END as weight
,CAST(n AS DECIMAL(9,2)) AS minwt
,CAST(n + @res AS DECIMAL(9,2)) as maxwt
FROM Tally
WHERE n BETWEEN 0 AND w.minwt) x
GROUP BY x.location, x.minwt, x.maxwt
ORDER BY x.location, x.minwt
Note that I've used local variable @res to be the resolution, so this is what you'd be passing into your SP.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply