June 26, 2013 at 9:24 pm
Hello,
I need to distribute total usage into specified intervals. Please find below an example
Total Usage: 1,000
Specified Intervals:
Block 1 - 0 To 100 ---->100 (First 100 units will be allocated into block #1)
Block 2 - 101 To 250 ---->150 (Next 150 units allocated into block #2) etc
Block 3 - 251 To 800 ---->550
Block 4 - 900 To 1,000 -->200
Block 5 - More than 1,000
100+150+550+200= 1,000
Blocks intervals is stored in a table and it can change based on geography.
Appreciate if anyone can please send me function or SQL code which can do the above.
Thanks
June 27, 2013 at 12:00 am
This sounds fairly straightforward, but it's impossible to provide working code without some additional help from you.
Specifically, we need consumable DDL and sample data (in the form of INSERT statements), so that we have something to code against. Please see the link in my signature for a detailed explanation of how to do this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 27, 2013 at 2:42 am
Add a rownumber to your resultset. Create the blocks of the resultset with the values in your interval table:
SELECT
...
WHERE {rownumber} > (SELECT low_value FROM interval_table)
AND {rownumber} <= (SELECT high_value FROM interval_table)
June 27, 2013 at 4:01 am
Sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT TOP 10000 IDENTITY(INT,1,1) AS ID,
(ABS(CHECKSUM(NEWID())) % 1000) + 1 AS usage
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
That's 10,000 pseudo-random rows of data where the usage is between 1 and 1000.
Based on that, you could do something like this: -
SELECT
SUM(ISNULL([Block 1 - 0 To 100],0)) AS [Block 1 - 0 To 100],
SUM(ISNULL([Block 2 - 101 To 250],0)) AS [Block 2 - 101 To 250],
SUM(ISNULL([Block 3 - 251 To 800],0)) AS [Block 3 - 251 To 800],
SUM(ISNULL([Block 4 - 800 To 1,000],0)) AS [Block 4 - 800 To 1,000],
SUM(ISNULL([Block 5 - More than 1,000],0)) AS [Block 5 - More than 1,000],
COUNT(1) AS Total
FROM #testEnvironment a
OUTER APPLY (SELECT 1 WHERE usage >= 0 AND usage <= 100)b([Block 1 - 0 To 100])
OUTER APPLY (SELECT 1 WHERE usage >= 101 AND usage <= 250)c([Block 2 - 101 To 250])
OUTER APPLY (SELECT 1 WHERE usage >= 251 AND usage <= 800)d([Block 3 - 251 To 800])
OUTER APPLY (SELECT 1 WHERE usage >= 800 AND usage <= 1000)e([Block 4 - 800 To 1,000])
OUTER APPLY (SELECT 1 WHERE usage > 1000)f([Block 5 - More than 1,000]);
or this: -
SELECT
SUM(CASE WHEN usage >= 0 AND usage <= 100 THEN 1 ELSE 0 END) AS [Block 1 - 0 To 100],
SUM(CASE WHEN usage >= 101 AND usage <= 250 THEN 1 ELSE 0 END) AS [Block 2 - 101 To 250],
SUM(CASE WHEN usage >= 251 AND usage <= 800 THEN 1 ELSE 0 END) AS [Block 3 - 251 To 800],
SUM(CASE WHEN usage >= 800 AND usage <= 1000 THEN 1 ELSE 0 END) AS [Block 4 - 800 To 1,000],
SUM(CASE WHEN usage > 1000 THEN 1 ELSE 0 END) AS [Block 5 - More than 1,000],
COUNT(1) AS Total
FROM #testEnvironment;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply