Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Group uninterrupted days for min Balance?

User_ZR3PEDec 3 2022

I have a BalanceTable like below. I want to take min Balance but if the days in the uninterrupted holiday I want to take min Balance in holiday.
Why I want to to that?
Because, I will make calculation based on min Balance but holidays take into account different than other days. If 18.11.2022-19.11.2022-20.11.2022-21.11.2022 are holiday for me they ara not seperate for days instead they are like 1 super day lasted 96 hours and has power of the 4 days .
Oracle version :21c

image.pngCREATE TABLE BalanceTable(
Balance_Day DATE,
Balance NUMERIC(6, 2)
);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('29.11.2022','DD.MM.YYYY'),500);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('28.11.2022','DD.MM.YYYY'),400);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('27.11.2022','DD.MM.YYYY'),425);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('26.11.2022','DD.MM.YYYY'),670);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('25.11.2022','DD.MM.YYYY'),780);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('24.11.2022','DD.MM.YYYY'),355);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('23.11.2022','DD.MM.YYYY'),255);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('22.11.2022','DD.MM.YYYY'),873);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('21.11.2022','DD.MM.YYYY'),990);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('20.11.2022','DD.MM.YYYY'),1020);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('19.11.2022','DD.MM.YYYY'),200);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('18.11.2022','DD.MM.YYYY'),560);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('17.11.2022','DD.MM.YYYY'),1090);
INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('16.11.2022','DD.MM.YYYY'),129);

CREATE TABLE CalendarTable(
Calendar_Day DATE,
IsHoliday VARCHAR(3)
);

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('29.11.2022','DD.MM.YYYY'),'no');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('28.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('27.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('26.11.2022','DD.MM.YYYY'),'no');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('25.11.2022','DD.MM.YYYY'),'no');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('24.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('23.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('22.11.2022','DD.MM.YYYY'),'no');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('21.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('20.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('19.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('18.11.2022','DD.MM.YYYY'),'yes');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('17.11.2022','DD.MM.YYYY'),'no');
INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('16.11.2022','DD.MM.YYYY'),'no');

This post has been answered by mathguy on Dec 3 2022
Jump to Answer
Comments
Post Details
Added on Dec 3 2022
7 comments
94 views