September 13, 2016 at 2:36 am
I think this will not run on SQL 2008, Can we achieve this in 2008?
September 13, 2016 at 2:37 am
Yes, I would like to show all iteration, recursion as well. Final recursion/iteration should look like what you shown.
September 13, 2016 at 2:40 am
ChrisM@Work (9/9/2016)
This should be a step in the right direction:
DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0 ) ,
( '2', 80, 0 ) ,
( '3', 75, 0 ) ,
( '4', 70, 0 ) ,
( '5', 50, 0 ) ,
( '6', 40, 0 );
DROP TABLE #Filler
CREATE TABLE #Filler (FillerID INT, Filler INT);
INSERT INTO #Filler
VALUES ( '1', 90 ) ,
( '2', 40 ) ,
( '3', 70 ) ,
( '4', 50 ) ,
( '5', 40 ) ,
( '6', 30 ) ,
( '7', 35 );
WITH ProcessedDebits AS (
SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to]
FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d
),
ProcessedCredits AS (
SELECT FillerID, Filler, [from] = ([to] - Filler), [to]
FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d
)
SELECT
bucketID, FullCapacity,
DebitBalance = CASE
WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to])
WHEN dr.[to] < cr.[to] THEN 0
ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END,
FillerID, Filler,
CreditBalance = CASE
WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to])
WHEN cr.[to] < dr.[to] THEN 0
ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END
FROM ProcessedDebits dr
FULL OUTER JOIN ProcessedCredits cr
ON cr.[from] < dr.[to]
AND cr.[to] > dr.[from]
ORDER BY bucketID, FillerID
OPTION (MAXDOP 1);
I think this will not run on SQL 2008, Can we achieve this in 2008?
September 13, 2016 at 2:48 am
ashishkumarrai (9/13/2016)
ChrisM@Work (9/9/2016)
This should be a step in the right direction:
DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0 ) ,
( '2', 80, 0 ) ,
( '3', 75, 0 ) ,
( '4', 70, 0 ) ,
( '5', 50, 0 ) ,
( '6', 40, 0 );
DROP TABLE #Filler
CREATE TABLE #Filler (FillerID INT, Filler INT);
INSERT INTO #Filler
VALUES ( '1', 90 ) ,
( '2', 40 ) ,
( '3', 70 ) ,
( '4', 50 ) ,
( '5', 40 ) ,
( '6', 30 ) ,
( '7', 35 );
WITH ProcessedDebits AS (
SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to]
FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d
),
ProcessedCredits AS (
SELECT FillerID, Filler, [from] = ([to] - Filler), [to]
FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d
)
SELECT
bucketID, FullCapacity,
DebitBalance = CASE
WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to])
WHEN dr.[to] < cr.[to] THEN 0
ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END,
FillerID, Filler,
CreditBalance = CASE
WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to])
WHEN cr.[to] < dr.[to] THEN 0
ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END
FROM ProcessedDebits dr
FULL OUTER JOIN ProcessedCredits cr
ON cr.[from] < dr.[to]
AND cr.[to] > dr.[from]
ORDER BY bucketID, FillerID
OPTION (MAXDOP 1);
I think this will not run on SQL 2008, Can we achieve this in 2008?
SUM() OVER... works in 2008 (https://msdn.microsoft.com/en-us/library/ms187810.aspx), however the window frame is I think from 2012. Try removing them:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
then run the test batch.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 13, 2016 at 2:58 am
ChrisM@Work (9/13/2016)
ashishkumarrai (9/13/2016)
ChrisM@Work (9/9/2016)
This should be a step in the right direction:
DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0 ) ,
( '2', 80, 0 ) ,
( '3', 75, 0 ) ,
( '4', 70, 0 ) ,
( '5', 50, 0 ) ,
( '6', 40, 0 );
DROP TABLE #Filler
CREATE TABLE #Filler (FillerID INT, Filler INT);
INSERT INTO #Filler
VALUES ( '1', 90 ) ,
( '2', 40 ) ,
( '3', 70 ) ,
( '4', 50 ) ,
( '5', 40 ) ,
( '6', 30 ) ,
( '7', 35 );
WITH ProcessedDebits AS (
SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to]
FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d
),
ProcessedCredits AS (
SELECT FillerID, Filler, [from] = ([to] - Filler), [to]
FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d
)
SELECT
bucketID, FullCapacity,
DebitBalance = CASE
WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to])
WHEN dr.[to] < cr.[to] THEN 0
ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END,
FillerID, Filler,
CreditBalance = CASE
WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to])
WHEN cr.[to] < dr.[to] THEN 0
ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END
FROM ProcessedDebits dr
FULL OUTER JOIN ProcessedCredits cr
ON cr.[from] < dr.[to]
AND cr.[to] > dr.[from]
ORDER BY bucketID, FillerID
OPTION (MAXDOP 1);
I think this will not run on SQL 2008, Can we achieve this in 2008?
SUM() OVER... works in 2008 (https://msdn.microsoft.com/en-us/library/ms187810.aspx), however the window frame is I think from 2012. Try removing them:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
then run the test batch.
still not working the error is "The Parallel Data Warehouse (PDW) features are not enabled."
September 13, 2016 at 3:15 am
ashishkumarrai (9/13/2016)
ChrisM@Work (9/13/2016)
ashishkumarrai (9/13/2016)
ChrisM@Work (9/9/2016)
This should be a step in the right direction:
DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0 ) ,
( '2', 80, 0 ) ,
( '3', 75, 0 ) ,
( '4', 70, 0 ) ,
( '5', 50, 0 ) ,
( '6', 40, 0 );
DROP TABLE #Filler
CREATE TABLE #Filler (FillerID INT, Filler INT);
INSERT INTO #Filler
VALUES ( '1', 90 ) ,
( '2', 40 ) ,
( '3', 70 ) ,
( '4', 50 ) ,
( '5', 40 ) ,
( '6', 30 ) ,
( '7', 35 );
WITH ProcessedDebits AS (
SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to]
FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d
),
ProcessedCredits AS (
SELECT FillerID, Filler, [from] = ([to] - Filler), [to]
FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d
)
SELECT
bucketID, FullCapacity,
DebitBalance = CASE
WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to])
WHEN dr.[to] < cr.[to] THEN 0
ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END,
FillerID, Filler,
CreditBalance = CASE
WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to])
WHEN cr.[to] < dr.[to] THEN 0
ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END
FROM ProcessedDebits dr
FULL OUTER JOIN ProcessedCredits cr
ON cr.[from] < dr.[to]
AND cr.[to] > dr.[from]
ORDER BY bucketID, FillerID
OPTION (MAXDOP 1);
I think this will not run on SQL 2008, Can we achieve this in 2008?
SUM() OVER... works in 2008 (https://msdn.microsoft.com/en-us/library/ms187810.aspx), however the window frame is I think from 2012. Try removing them:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
then run the test batch.
still not working the error is "The Parallel Data Warehouse (PDW) features are not enabled."
OK got it - running totals using ORDER BY with SUM() OVER was introduced with SQL 2012.
The running totals will have to be calculated by an alternative method.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 13, 2016 at 10:35 am
It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:
After 1st iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 85
2 | 80 | 5
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After second iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 45
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After third iteration and so on......
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 80
3 | 75 | 35
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
September 13, 2016 at 10:47 am
ashishkumarrai (9/13/2016)
It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 85
2 | 80 | 5
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After second iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 45
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After third iteration and so on......
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 80
3 | 75 | 35
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
sorry...but at the moment i am at a loss as to why you wish to store each iteration....can you please explain the business requriement behind this?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 13, 2016 at 12:06 pm
This is a direct cut and paste from "SQL for Smarties", you will have to figure out where the data in the tables and stuff should go. But it looks like what you are after is a T join. This does not require any of the window functions. We have now so will probably run in SQL-2008.
26.07. Dr. Codd's T-Join
In the Second Version of the relational model in 1990, Dr. E. F. Codd introduced a set of new theta operators, called T-operators, which were based on the idea of a best-fit or approximate equality (Codd 1990). The algorithm for the operators is easier to understand with an example modified from Dr. Codd.
The problem is to assign the classes to the available classrooms. We want (class_size < room_size) to be true after the assignments are made. This will allow us a few empty seats in each room for late students. We can do this in one of two ways. The first way is to sort the tables in ascending order by classroom size and the number of students in a class. We start with the following tables and load them with the data that follows the DDL.
CREATE TABLE Rooms
(room_nbr CHAR(3) NOT NULL PRIMARY KEY,
room_size INTEGER NOT NULL);
Classes
class_nbr
class_size
'c1'
80
'c2'
70
'c3'
65
'c4'
55
'c5'
50
'c6'
40
CREATE TABLE Classes
(class_nbr CHAR(3) NOT NULL PRIMARY KEY,
class_size INTEGER NOT NULL);
Rooms
room_nbr
room_size
r1
70
r2
40
r3
50
r4
85
r5
30
r6
65
r7
55
The goal of the T-JOIN problem is to assign a class which is smaller than the classroom given it (class_size < room_size). Dr. Codd gives two approaches to the problem.
1) Ascending Order Algorithm
Sort both tables into ascending order. Reading from the top of the Rooms table, match each class with the first room that will fit.
Classes
class_nbr
class_size
c6
40
c5
50
c4
55
c3
65
c2
70
c1
80
Rooms
room_nbr
room_size
r5
30
r2
40
r3
50
r7
55
r6
65
r1
70
r4
85
This gives us
Results
Classes
Rooms
class_nbr
class_size
room_nbr
room_size
c2
70
r4
85
c3
65
r1
70
c4
55
r6
65
c5
50
r7
55
c6
40
r3
50
2) Descending Order Algorithm
Sort both tables into descending order. Reading from the top of the Classes table, match each class with the first room that will fit.
Classes
Rooms
class_nbr
class_size
room_nbr
room_size
c1
80
r4
85
c2
70
r1
70
c3
65
r6
65
c4
55
r7
55
c5
50
r3
50
c6
40
r2
40
NULL
NULL
r5
30
Results
class_nbr
class_size
room_nbr
room_size
c1
80
r4
85
c3
65
r1
70
c4
55
r6
65
c5
50
r7
55
c6
40
r3'
50
Notice that the answers are different! Dr. Codd has never given a definition in relational algebra of the T-Join, so I proposed that we need one. Informally, for each class, we want the smallest room that will hold it, while maintaining the T-JOIN condition. Or for each room, we want the largest class that will fill it, while maintaining the T-JOIN condition. These can be two different things, so you must decide which table is the driver. But either way, I am advocating a "best fit" over Codd's "first fit" approach.
Other theta conditions can be used in place of the "less than" shown here. If "less than or equal" is used, all the classes are assigned to a room in this case, but not in all cases. This is left to the reader as an exercise.
The first attempts in Standard SQL are versions of grouped by queries. They can, however, produce some rows that would be left out of the answers Dr. Codd was expecting. The first JOIN can be written as
SELECT class_nbr, class_size, MIN(room_size)
FROM Rooms, Classes
WHERE Classes.class_size < Rooms.room_size
GROUP BY class_nbr, class_size;
This will give a result table with the desired room sizes, but not the room numbers. You cannot put the other columns in the SELECT list, since it would conflict with the GROUP BY clause. But also note that the classroom with 85 seats ('r4') is used twice, once by class 'c1' and then by class 'c2':
class_size
class_size
MIN(room_size)
Notes
c1
80
85
? room r4
c2
70
85
? room r4
c3
65
70
c4
55
65
c5
50
55
c6
40
50
If you do a little arithmetic on the data, you find that we have 360 students and 395 seats, 6 classes and 7 rooms. Do you want to use the smallest number of rooms?
As it works out, the best fit of rooms to classes will leave the smallest room empty and pack the other rooms to capacity, thus:
SELECT class_nbr, class_size, MIN(room_size)
FROM Rooms, Classes
WHERE Classes.class_size <= Rooms.room_size
GROUP BY class_nbr, class_size;
26.07.01. A Procedural Approach
The place to start is with all the possible legal room assignments for a class. We have already seen this query:
SELECT R.room_nbr, C.class_nbr
FROM Rooms AS R, Classes AS C
WHERE C.class_size <= R.room_size
ORDER BY R.room_nbr, C.class_nbr;
At the extreme, if all the rooms and classes are the same size, then your have (n!) solutions. If all the rooms are different sizes, we can save ourselves a combinatorial explosions, so let us agree that we will juggle the data to get that condition. This query will give us the pairs that are an exact fit, but we know that there will not be any ties for room size.
SELECT R.room_nbr, C.class_nbr
FROM Rooms AS R, Classes AS C
WHERE C.class_size <= R.room_size
GROUP BY R.room_nbr, C.class_nbr
HAVING MIN(R.room_size - C.class_size) = 0;
r1
c2
r6
c3
r7
c4
r3
c5
r2
c6
This leaves us with class {c1} and rooms {r4, r5} yet to be used. We can then repeat a limited version of the basic Pairs query.
SELECT R.room_nbr, C.class_nbr
FROM Rooms AS R, Classes AS C
WHERE C.class_size <= R.room_size
AND R.room_nbr IN ('r4', 'r5')
AND C.class_nbr IN ('c1');
r4
c1
We can now union these result sets and have an answer. I took some extra time to show the details to demonstrate how we can implement a best-fit, greedy algorithm. Let us get a bigger data set and work with it.
INSERT INTO Classes (class_nbr, class_size)
VALUES
('c01', 106), ('c02', 105), ('c03', 104), ('c04', 100), ('c05', 99),
('c06', 90), ('c07', 89), ('c08', 88), ('c09', 83), ('c10', 82),
('c11', 81), ('c12', 65), ('c13', 50), ('c14', 49), ('c15', 30),
('c16', 29), ('c17', 28), ('c18', 20), ('c19', 19);
INSERT INTO Rooms (room_nbr, room_size)
VALUES
('r01', 102), ('r02', 101), ('r03', 95), ('r04', 94), ('r05', 85),
('r06', 70), ('r07', 55), ('r08', 54), ('r09', 35), ('r10', 34),
('r11', 25), ('r12', 18);
To see how this will work, let's add another column to the table of legal (class_nbr, room_nbr) pairs to see how well they fit.
WITH Pairs (room_nbr, class_nbr, fit)
AS
(SELECT R.room_nbr, C.class_nbr,
(R.room_size - C.class_size)
FROM Rooms AS R, Classes AS C
WHERE C.class_size <= R.room_size)
SELECT P1.room_nbr, P1.class_nbr, fit
FROM Pairs AS P1
WHERE P1.fit
= (SELECT MIN(P2.fit)
FROM Pairs AS P2
WHERE P1.room_nbr = P2.room_nbr);
r01
c04
2
r02
c04
1
r03
c06
5
r04
c06
4
r05
c09
2
r06
c12
5
r07
c13
5
r08
c13
4
r09
c15
5
r10
c15
4
r11
c18
5
This time we did not have an exact fit, so let's look for (fit = 1) into a working table and remove 'r02' and 'c04' from their tables. The second step is
r02
c04
1
We can now remove the best fit rooms and classes, and look the next set of remaining best fits.
WITH Pairs (room_nbr, class_nbr, fit)
AS
(SELECT R.room_nbr, C.class_nbr,
(R.room_size - C.class_size)
FROM Rooms AS R, Classes AS C
WHERE C.class_size <= R.room_size)
(SELECT P1.room_nbr, P1.class_nbr, fit
FROM Pairs AS P1
WHERE P1.fit = 2);
r05
c09
2
Here is the step for a fit of 3, 4, 5 and 6
r01
c05
3
r04
c06
4
r08
c13
4
r10
c15
4
r06
c12
5
r11
c18
5
r03
c07
6
r07
c14
6
r09
c16
6
At this point, the original tables of rooms and classes cannot be paired. Notice that as we removed rooms and classes, the fit numbers changed. This is a characteristic of greedy algorithms; taking the “big bites” can leave sub-optimal leftovers on the plate.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 14, 2016 at 2:23 am
J Livingston SQL (9/13/2016)
ashishkumarrai (9/13/2016)
It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 85
2 | 80 | 5
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After second iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 45
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After third iteration and so on......
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 80
3 | 75 | 35
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
sorry...but at the moment i am at a loss as to why you wish to store each iteration....can you please explain the business requriement behind this?
Sure, actually it is a matrix. I want to preserve the matrix and store each cell as there are calculation to be done on each cell. Imagine it as a matrix where bucket table is on X axis and Filler table is on Y axis. After each iteration we will fill one or more cell. hope that explains.
September 14, 2016 at 2:34 am
ashishkumarrai (9/14/2016)
J Livingston SQL (9/13/2016)
ashishkumarrai (9/13/2016)
It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 85
2 | 80 | 5
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After second iteration
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 45
3 | 75 | 0
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
After third iteration and so on......
BucketID | FullCapacity | CurrentAmount
---+--------------+--------------
1 | 85 | 0
2 | 80 | 80
3 | 75 | 35
4 | 70 | 0
5 | 50 | 0
6 | 40 | 0
---+--------------+--------------
sorry...but at the moment i am at a loss as to why you wish to store each iteration....can you please explain the business requriement behind this?
Sure, actually it is a matrix. I want to preserve the matrix and store each cell as there are calculation to be done on each cell. Imagine it as a matrix where bucket table is on X axis and Filler table is on Y axis. After each iteration we will fill one or more cell. hope that explains.
What would certainly help would be a data structure with before and after figures.
The post above shows the same rows & columns of the same table processed in cycles. If this is what you want, then it's back to the original, looping through the values in the filler table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply