I want to generate newspaper bill according to the following criteria.
In a newspaper publisher, the cost of newspaper on Tuesday and Thursday is Rs.3/- and on other days it is Rs.2/-. So my requirement is to generate billing of newspaper as per the choice of user input at runtime using Oracle Pl/SQL programming.
NOTE:
The billing can be as Daily/Weekly/Monthly/Yearly/Any specific duration based on the requirement.(For Ex. i want billing from August 2015 to March 2016)
Oracle Scheduler, Stored Procedures/Functions, Cursor, Loops can be used.
Any help will be appreciated.
Thank You
There are many ways you could do this. But I'd lean towards creating a table that stores the price for every day. For example:
create table prices (
price_date date primary key
check (price_date = trunc(price_date)),
unit_cost number
);
This has many advantages:
- Finding the cost for a range of dates is a simple sum:
select sum(unit_cost) from prices
where price_date between date'2016-01-01' and date'2016-01-31';
- You can handle price changes using DML - no code changes necessary!
update prices
set unit_cost = 4
where price_date = date'2016-07-01';
- Related to the above, it's easy to audit changes to prices. Either through your own solution or a combination of Oracle auditing and Flashback Data Archive (free 11.2.0.4+)