Hi Everyone
I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.
There is a task date, shift and volume column in table.
I want to sum the volume from the task_date 10 PM to next day 6 AM and display the total for each night shift with the previous day date(i.e the date on which the shift starts at 10 PM )
This needs to be done for a period of date ranges which will be input as part of this SQL query.
for example: Total picked volume for one night shift from 13-07-2022 22:00 to 14-07-2022 02:00 will be 110 for the date 13-jul-2022 and so on.
Please find the create/insert scripts for the sample case. The Shift needs to be created in the query itself.
create table duumy_t
(
task_end_date date,
picked_volume number
);
insert into duumy_t values ('12-jul-2022 01:00:00',20);
insert into duumy_t values ('12-jul-2022 02:00:00',20);
insert into duumy_t values ('12-jul-2022 22:00:00',30);
insert into duumy_t values ('12-jul-2022 23:00:00',40);
insert into duumy_t values ('12-jul-2022 23:00:00',200);
insert into duumy_t values ('13-jul-2022 01:00:00',400);
insert into duumy_t values ('13-jul-2022 01:00:00',20);
insert into duumy_t values ('13-jul-2022 02:00:00',20);
insert into duumy_t values ('13-jul-2022 22:00:00',30);
insert into duumy_t values ('13-jul-2022 23:00:00',40);
insert into duumy_t values ('14-jul-2022 01:00:00',20);
insert into duumy_t values ('14-jul-2022 02:00:00',20);
insert into duumy_t values ('14-jul-2022 22:00:00',20);
insert into duumy_t values ('14-jul-2022 23:00:00',20);
insert into duumy_t values ('14-jul-2022 14:30:00',20);
insert into duumy_t values ('14-jul-2022 10:30:00',20);
commit;
Thanks
Gautam