Hi
I am having a little trouble with aggregate functions.
In this scenario an item always has a slot for each period. Periods are sequential.
What I want is that if an item changes slot, the number of periods in slot resets to one even if it has been in that slot before and the start period in slot be the last time it moved into that slot rather than demonstrated below.
CREATE TABLE tmp_table (item VARCHAR2(10),period NUMBER,slot VARCHAR2(10));
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',1,'A');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',2,'A');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',3,'A');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',4,'B');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',5,'B');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',6,'C');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',7,'A');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',8,'A');
INSERT INTO tmp_table (item,period,slot) VALUES ('abc123',9,'C');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',1,'D');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',2,'D');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',3,'E');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',4,'E');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',5,'D');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',6,'D');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',7,'E');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',8,'E');
INSERT INTO tmp_table (item,period,slot) VALUES ('def456',9,'D');
SELECT
item
, period
, slot
, FIRST_VALUE(period)
OVER (PARTITION BY item, slot
ORDER BY period) start_period_at_slot
, ROW_NUMBER()
OVER (PARTITION BY item, slot
ORDER BY period) periods_in_slot
FROM tmp_table
ORDER BY item,period
gives me
ITEM PERIOD SLOT START_PERIOD_AT_SLOT PERIODS_IN_SLOT
---------- ---------- ---------- -------------------- ---------------
abc123 1 A 1 1
abc123 2 A 1 2
abc123 3 A 1 3
abc123 4 B 4 1
abc123 5 B 4 2
abc123 6 C 6 1
abc123 7 A 1 4
abc123 8 A 1 5
abc123 9 C 6 2
def456 1 D 1 1
def456 2 D 1 2
def456 3 E 3 1
def456 4 E 3 2
def456 5 D 1 3
def456 6 D 1 4
def456 7 E 3 3
def456 8 E 3 4
def456 9 D 1 5
18 rows selected.
Notice when the item abc123 moves back into slot A it picks up where it left off with periods in slot of 4 but I would like to find a way to manipulate this to reset to zero and the start period in the slot be 7 rather than 1.
and subsequent rows ordered on period to increment appropriately.
Any help much appreciated.
Thanks
Ian