get the missed day

hi
i have table daily_sales
ep_date  date
ep_amount  number

how to select the missed day (if any) between start_date and end_date
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this article should help:
http://www.experts-exchange.com/Database/Miscellaneous/A_3952-ranges-gaps-overlaps-for-numbers-and-date-ranges.html

in short: if you have a table with all possible date values (also called a calendar table), you issue a query like this:
SELECT ct.cd_date
FROM calendar_table tc
LEFT JOIN daily_sales ds 
  ON ds.ep_date = ct.cd_date
WHERE ds.ep_date IS NULL 

Open in new window


if you don't have such a table, you can build it dynamically if needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
if you don't have such a table, you can build it dynamically if needed.

i don't have ,
i will create it
but , is there any procedure could add new value for all dates since starting ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
plenty of examples in the internet ...
here is one nice for oracle:
http://www.perpendulum.com/2012/06/calendar-table-script-for-oracle/

instead of stopping at 2013, you can put year 9999 (for oracle), so you are save ...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
great
the query return all values
but , how to create table from that query ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE TABLE your_calendar_table AS <select>
0
Shaju KumbalathDeputy General Manager - ITCommented:
select  missing_date from
(select to_date('01-sep-2013','dd-mon-yyyy')+level-1 missing_date from dual  
connect by to_date('01-sep-2013','dd-mon-yyyy')+level-1<=to_date('11-sep-2013','dd-mon-yyyy')) md
left outer join
daily_sales ds ON md.missing_date=DS.ep_date  where  ds.ep_date is  null;


in the above example 01-sep-2013 &11-sep-2013 are start and end dates respectively.
0
NiceMan331Author Commented:
ok , done
but regarding the main table
i should group it 1st on field ep_date
should i create view 1st group by ep_date , then have the query you menstiobed above ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do that "inline":
SELECT ct.cd_date
FROM calendar_table tc
LEFT JOIN (select ep_date from daily_sales group by ep_date ) ds 
  ON ds.ep_date = ct.cd_date
WHERE ds.ep_date IS NULL  

Open in new window


or you do it like this:
SELECT ct.cd_date
FROM calendar_table tc
WHERE NOT EXISTS( select NULL from daily_sales ds 
 WHERE ds.ep_date = ct.cd_date ) 

Open in new window

0
NiceMan331Author Commented:
S
ELECT tc.DATE_TIME_START
FROM calendar_table tc
WHERE NOT EXISTS( select NULL from daily-sales ds 
 WHERE ds.ep_date = tc.DATE_TIME_START

Open in new window


run this code gives all dates
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
only if the daily_sales date has also some time portion.
the code is correct, it's now about data.
without seeing some data, impossible to fix is quickly
0
NiceMan331Author Commented:
i repair the code to the following

 
  SELECT tc.DATE_TIME_START
FROM calendar_table tc
where tc.date_time_start between '01-mar-13' and '31-aug-13'
and tc.date_time_start
not IN
(select ds.ep_date from daily_sales ds where
ds.cnt_str_id ='2001'  
and ds.rp_date between '01-mar-13' and '31-aug-13'
group by ds.ep_date)

Open in new window


it works well now
0
NiceMan331Author Commented:
angell , your answer is accepted for me
but before i accept the solution and close the issue
is there any way to run this select daily bases , i mean before i run the query or the report of the transactions , i wish to automatically alarm me that there are missing days
i think have to use function , but i didn't get how to call it before calling my query
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can run this indeed "any" day, using a scheduled job.
the "alarm" part is what you need to decide on how you want to get the alarm.
0
NiceMan331Author Commented:
yes
i need something like this



procedure print_data
if ( --- if missing data ) then 
message ('there are 2 days missing
else
select * from tran_data
end if

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, presuming that the function "message" is already existing:
create or replace procedure print_data
as
  v_count_missing number;
begin

  select count(*)
     into v_count
  from  ( <your select above> )
 ;

 if ( v_count > 0 ) then 
    message ('there are ' ||v_count ||' days missing ' );
else
  select * from tran_data;
end if; 

Open in new window


but the SELECT in the procedure will not work like this ...
0
NiceMan331Author Commented:
if ( v_count > 0 ) then 

Open in new window


is this will handle the null ?
mean if there are no data , it will return 0 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
count(*) will never return NULL, but 0
0
NiceMan331Author Commented:
it gives me this errors

message ('there are ' ||v_count ||' days missing ' );

Open in new window

i repair it to dbms_output.putline

and this one :

select * from tran_data;

Open in new window


an INTO caluse is expected in this select statement
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I write above, SELECT cannot be used inside a stored procedure (unless for a CURSOR statement, as part of INSERT statement, in short a subselect, or, like you posted, with a INTO <var1, var2 ...> clause.

what do you want to do with the procedure? return data?
0
NiceMan331Author Commented:
what do you want to do with the procedure? return data?

exactly
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you cannot use a procedure, unless you return the data via a cursor:
http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php
0
NiceMan331Author Commented:
that one will take more time from me
but i feel shame to let you stay too much
i have to accept your solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.