I have a scenario which needs to be handled in Oracle SQL – that is a requirement.
This is a book lending scenario which I am trying to create.
- Each student is entitled only so many number of books which is based on a credit system. In this sample Student ID ‘100’ is entitled for 4 books.
- Need to generate number of books that the student has checked in till date.
- The data under column header “#Active Books under this student” is what I am trying to achieve through SQL
- Some cases the records may be invalid due to invalid bar code scan data – in which cases we need to maintain the same count that the student is entitled to. This is available in the column header “Recurring Update” and “Update Status”
Could any one please help me building an Oracle SQL for achieving the data under column header “#Active Books under this student” ?
Thanks Lakshminarasu Chenduri
+------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+ | Student ID | Date | Book ID | Check Out? | Total Books that can be issued | #Active Books under this student | Recurring Update | Update Status | +------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+ | 101 | 14-Apr-20 | 2 | N | 4 | 1 | | | | 101 | 17-Apr-20 | 2 | Y | 4 | 0 | | | | 101 | 17-Apr-20 | 4 | N | 4 | 1 | | | | 101 | 24-Apr-20 | 1 | N | 4 | 2 | | | | 101 | 24-Apr-20 | 3 | N | 4 | 3 | | | | 101 | 24-Apr-20 | 4 | Y | 4 | 2 | | | | 101 | 27-Apr-20 | 1 | Y | 4 | 1 | | | | 101 | 27-Apr-20 | 3 | Y | 4 | 0 | | | | 101 | 27-Apr-20 | 4 | N | 4 | 1 | | | | 101 | 1-May-20 | 1 | N | 4 | 2 | | | | 101 | 1-May-20 | 2 | N | 4 | 3 | | | | 101 | 1-May-20 | 4 | Y | 4 | 2 | | | | 101 | 3-May-20 | 3 | N | 4 | 3 | | | | 101 | 3-May-20 | 4 | N | 4 | 4 | | | | 101 | 4-May-20 | 2 | Y | 4 | 3 | | | | 101 | 4-May-20 | 4 | Y | 4 | 2 | | | | 101 | 8-May-20 | 1 | Y | 4 | 1 | | | | 101 | 10-May-20 | 2 | N | 4 | 2 | | | | 101 | 10-May-20 | 3 | Y | 4 | 1 | | | | 101 | 17-May-20 | 1 | N | 4 | 2 | | | | 101 | 18-May-20 | 1 | Y | 4 | 1 | | | | 101 | 18-May-20 | 2 | Y | 4 | 0 | | | | 101 | 18-May-20 | 4 | N | 4 | 1 | | | | 101 | 19-May-20 | 3 | N | 4 | 2 | | | | 101 | 19-May-20 | 4 | Y | 4 | 1 | | | | 101 | 22-May-20 | 3 | Y | 4 | 0 | | | | 101 | 22-May-20 | 4 | N | 4 | 1 | | | | 101 | 27-May-20 | 3 | N | 4 | 2 | | | | 101 | 27-May-20 | 4 | Y | 4 | 1 | | | | 101 | 28-May-20 | 2 | N | 4 | 2 | | | | 101 | 28-May-20 | 3 | Y | 4 | 1 | | | | 101 | 7-Jun-20 | 1 | N | 4 | 2 | | | | 101 | 7-Jun-20 | 2 | N | 4 | 3 | | | | 101 | 9-Jun-20 | 4 | N | 4 | 4 | | | | 101 | 14-Jun-20 | 1 | N | 4 | 4 | 5 | INVALID UPDATE | | 101 | 14-Jun-20 | 4 | N | 4 | 4 | 6 | INVALID UPDATE | | 101 | 15-Jun-20 | 1 | Y | 4 | 3 | | | | 101 | 15-Jun-20 | 4 | N | 4 | 4 | | | | 101 | 28-Jun-20 | 3 | N | 4 | 4 | 5 | INVALID UPDATE | | 101 | 28-Jun-20 | 4 | Y | 4 | 3 | | | | 101 | 29-Jun-20 | 2 | N | 4 | 4 | | | | 101 | 29-Jun-20 | 3 | Y | 4 | 3 | | | | 101 | 2-Jul-20 | 1 | N | 4 | 4 | | | | 101 | 2-Jul-20 | 2 | N | 4 | 4 | 5 | INVALID UPDATE | | 101 | 6-Jul-20 | 1 | Y | 4 | 3 | | | | 101 | 6-Jul-20 | 2 | N | 4 | 4 | | | | 101 | 23-Jul-20 | 2 | N | 4 | 4 | 5 | INVALID UPDATE | | 101 | 24-Jul-20 | 1 | N | 4 | 4 | 6 | INVALID UPDATE | | 101 | 24-Jul-20 | 2 | N | 4 | 4 | 7 | INVALID UPDATE | | 101 | 28-Jul-20 | 1 | Y | 4 | 3 | | | | 101 | 28-Jul-20 | 2 | N | 4 | 2 | | | +------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
Advertisement
Answer
You can do this using a running SUM. There is one piece of information missing. You are assuming that the data is stored in the order you provided, but that is not captured in any column. So for my example I added a column transaction_id. Now I can guarantee the same order in the select. If the date column would contain a time portion (which it probably does, but that is not included in the sample data) then the ORDER BY dt) would be enough.
create table so_students
(student_id NUMBER,
dt DATE,
book_id NUMBER,
checkout VARCHAR2(1),
total_books NUMBER,
active_books NUMBER,
recurring_update NUMBER,
update_status VARCHAR2(100),
transaction_id NUMBER
);
create sequence so_students_s;
INSERT INTO so_students values (101,TO_DATE('14-Apr-20','DD-Mon-YY'), 2,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('17-Apr-20','DD-Mon-YY'), 2,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('17-Apr-20','DD-Mon-YY'), 4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'), 1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'), 3,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'), 4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'), 1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'), 3,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'), 4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'), 1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'), 2,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'), 4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('3-May-20','DD-Mon-YY'), 3,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('3-May-20','DD-Mon-YY'), 4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('4-May-20','DD-Mon-YY'), 2,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('4-May-20','DD-Mon-YY'), 4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('8-May-20','DD-Mon-YY'), 1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('10-May-20','DD-Mon-YY'), 2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('10-May-20','DD-Mon-YY'), 3,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('17-May-20','DD-Mon-YY'), 1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'), 1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'), 2,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'), 4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('19-May-20','DD-Mon-YY'), 3,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('19-May-20','DD-Mon-YY'), 4,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('22-May-20','DD-Mon-YY'), 3,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('22-May-20','DD-Mon-YY'), 4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-May-20','DD-Mon-YY'), 3,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-May-20','DD-Mon-YY'), 4,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-May-20','DD-Mon-YY'), 2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-May-20','DD-Mon-YY'), 3,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('7-Jun-20','DD-Mon-YY'), 1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('7-Jun-20','DD-Mon-YY'), 2,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('9-Jun-20','DD-Mon-YY'), 4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('14-Jun-20','DD-Mon-YY'), 1,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('14-Jun-20','DD-Mon-YY'), 4,'N', 4, 4,6,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('15-Jun-20','DD-Mon-YY'), 1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('15-Jun-20','DD-Mon-YY'), 4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-Jun-20','DD-Mon-YY'), 3,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-Jun-20','DD-Mon-YY'), 4,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('29-Jun-20','DD-Mon-YY'), 2,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('29-Jun-20','DD-Mon-YY'), 3,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('2-Jul-20','DD-Mon-YY'), 1,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('2-Jul-20','DD-Mon-YY'), 2,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('6-Jul-20','DD-Mon-YY'), 1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('6-Jul-20','DD-Mon-YY'), 2,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('23-Jul-20','DD-Mon-YY'), 2,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Jul-20','DD-Mon-YY'), 1,'N', 4, 4,6,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Jul-20','DD-Mon-YY'), 2,'N', 4, 4,7,'INVALID UPDATE',so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-Jul-20','DD-Mon-YY'), 1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-Jul-20','DD-Mon-YY'), 2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
SELECT SUM(
CASE update_status
WHEN 'INVALID UPDATE' THEN
0
ELSE
CASE checkout
WHEN 'N' THEN
1
ELSE
- 1
END
END
) OVER(
ORDER BY dt,transaction_id) AS running_sum,
active_books,
s.*
FROM so_students s;