By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
item Varchar(8),
`date` Date,
quantity Float,
trasactionid int);
INSERT INTO test 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-01-02',13,7),
('Part1','2023-01-02',1,8),
('Part1','2023-01-02',22,10),
('Part1','2023-01-02',5,12);
SELECT * FROM test ORDER BY 4;
Records: 8 Duplicates: 0 Warnings: 0
| item | date | quantity | trasactionid |
|---|---|---|---|
| Part1 | 2023-01-01 | 17 | 2 |
| Part1 | 2023-01-01 | 10 | 3 |
| Part1 | 2023-01-01 | 15 | 5 |
| Part1 | 2023-01-01 | 13 | 6 |
| Part1 | 2023-01-02 | 13 | 7 |
| Part1 | 2023-01-02 | 1 | 8 |
| Part1 | 2023-01-02 | 22 | 10 |
| Part1 | 2023-01-02 | 5 | 12 |
SELECT item, `date`, MIN(trasactionid) opening, MAX(trasactionid) closing
FROM test
GROUP BY 1,2
| item | date | opening | closing |
|---|---|---|---|
| Part1 | 2023-01-01 | 2 | 6 |
| Part1 | 2023-01-02 | 7 | 12 |
SELECT item, `date`,
MAX(CASE WHEN first_last.opening = test.trasactionid THEN test.trasactionid END) OpeningId,
MAX(CASE WHEN first_last.opening = test.trasactionid THEN quantity END) OpeningBal,
MAX(CASE WHEN first_last.closing = test.trasactionid THEN test.trasactionid END) ColsingId,
MAX(CASE WHEN first_last.closing = test.trasactionid THEN quantity END) ClosingBal
FROM test
JOIN (
SELECT item, `date`, MIN(trasactionid) opening, MAX(trasactionid) closing
FROM test
GROUP BY 1,2
) first_last USING (item, `date`)
GROUP BY 1,2
| item | date | OpeningId | OpeningBal | ColsingId | ClosingBal |
|---|---|---|---|---|---|
| Part1 | 2023-01-01 | 2 | 17 | 6 | 13 |
| Part1 | 2023-01-02 | 7 | 13 | 12 | 5 |