By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table table_name(Item VARCHAR(50), Date_ DATE, Quantity INT, transactionid INT);
Insert Into table_name Values('Part1','2023-01-01',10,3),
('Part1','2023-01-01',15,5),
('Part1','2023-01-01',17,2),
('Part1','2023-01-01',13,6),
('Part1','2023-02-01',13,7),
('Part1','2023-02-01',1,8),
('Part1','2023-02-01',22,10),
('Part1','2023-02-01',5,12);
Records: 8 Duplicates: 0 Warnings: 0
select Item, Date_, transactionid_1, OpeningBal, transactionid_2, ClosingBal
from
(
select Item, Date_,
min(transactionid) over (partition by Item, Date_) transactionid_1,
first_value(Quantity) over (partition by Item, Date_ order by transactionid) OpeningBal,
max(transactionid) over (partition by Item, Date_) transactionid_2,
last_value(Quantity) over (partition by Item, Date_ order by transactionid range between unbounded preceding and unbounded following) ClosingBal,
transactionid
from table_name
) t
where transactionid = transactionid_1
order by Item, Date_
| Item | Date_ | transactionid_1 | OpeningBal | transactionid_2 | ClosingBal |
|---|---|---|---|---|---|
| Part1 | 2023-01-01 | 2 | 17 | 6 | 13 |
| Part1 | 2023-02-01 | 7 | 13 | 12 | 5 |