By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PAY_IN_OUT
(
EMP_CODE NUMBER,
ATT_DATE DATE,
ITEM NUMBER
)
CREATE TABLE GAZZETED_DAYS
(
GAZZETED_DATE DATE NOT NULL,
DESCRIPTION VARCHAR2(40 BYTE)
)
insert into PAY_IN_OUT(ATT_DATE)
with dates as (
select date'2020-12-31'+level dt
from dual
connect by level <= 31
)
select dt
from dates
;
31 rows affected
UPDATE PAY_IN_OUT SET EMP_CODE = 100;
31 rows affected
UPDATE PAY_IN_OUT SET ITEM = floor(dbms_random.value(25, 40));
31 rows affected
INSERT ALL
INTO GAZZETED_DAYS(GAZZETED_DATE,DESCRIPTION)
VALUES (to_date('2021-01-09','yyyy-mm-dd'),'Its Holyday two')
INTO GAZZETED_DAYS(GAZZETED_DATE,DESCRIPTION)
VALUES (to_date('2021-01-13','yyyy-mm-dd'),'Its Holyday three')
INTO GAZZETED_DAYS(GAZZETED_DATE,DESCRIPTION)
VALUES (to_date('2021-01-16','yyyy-mm-dd'),'Its Holyday four')
INTO GAZZETED_DAYS(GAZZETED_DATE,DESCRIPTION)
VALUES (to_date('2021-01-21','yyyy-mm-dd'),'Its Holyday five')
INTO GAZZETED_DAYS(GAZZETED_DATE,DESCRIPTION)
VALUES (to_date('2021-01-26','yyyy-mm-dd'),'Its Holyday six')
SELECT * FROM dual;
5 rows affected
select * from GAZZETED_DAYS;
| GAZZETED_DATE | DESCRIPTION |
|---|---|
| 09-JAN-21 | Its Holyday two |
| 13-JAN-21 | Its Holyday three |
| 16-JAN-21 | Its Holyday four |
| 21-JAN-21 | Its Holyday five |
| 26-JAN-21 | Its Holyday six |
select * from PAY_IN_OUT;
| EMP_CODE | ATT_DATE | ITEM |
|---|---|---|
| 100 | 01-JAN-21 | 36 |
| 100 | 02-JAN-21 | 35 |
| 100 | 03-JAN-21 | 36 |
| 100 | 04-JAN-21 | 32 |
| 100 | 05-JAN-21 | 34 |
| 100 | 06-JAN-21 | 29 |
| 100 | 07-JAN-21 | 38 |
| 100 | 08-JAN-21 | 27 |
| 100 | 09-JAN-21 | 31 |
| 100 | 10-JAN-21 | 33 |
| 100 | 11-JAN-21 | 31 |
| 100 | 12-JAN-21 | 36 |
| 100 | 13-JAN-21 | 31 |
| 100 | 14-JAN-21 | 36 |
| 100 | 15-JAN-21 | 29 |
| 100 | 16-JAN-21 | 31 |
| 100 | 17-JAN-21 | 35 |
| 100 | 18-JAN-21 | 31 |
| 100 | 19-JAN-21 | 31 |
| 100 | 20-JAN-21 | 31 |
| 100 | 21-JAN-21 | 33 |
| 100 | 22-JAN-21 | 34 |
| 100 | 23-JAN-21 | 39 |
| 100 | 24-JAN-21 | 25 |
| 100 | 25-JAN-21 | 36 |
| 100 | 26-JAN-21 | 30 |
| 100 | 27-JAN-21 | 32 |
| 100 | 28-JAN-21 | 32 |
| 100 | 29-JAN-21 | 25 |
| 100 | 30-JAN-21 | 36 |
| 100 | 31-JAN-21 | 32 |
select sum(item) from pay_in_out;
| SUM(ITEM) |
|---|
| 1007 |
DECLARE
a number;
B DATE;
CURSOR GZDT IS
SELECT GAZZETED_DATE FROM GAZZETED_DAYS
WHERE GAZZETED_DATE between date '2021-01-01' and date '2021-01-20';
begin
OPEN GZDT;
LOOP
FETCH GZDT INTO B;
EXIT WHEN GZDT%notfound;
END LOOP;
CLOSE GZDT;
SELECT SUM(ITEM) into a from pay_in_out
where --TO_CHAR(att_date,'DY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SUN')
ATT_DATE != B
AND att_date between date '2021-01-01' and date '2021-01-20'
AND EMP_CODE = 100;
dbms_output.put_line('the sum of item is :'||a) ;
end;
/
1 rows affected
dbms_output:
the sum of item is :622