Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NiceMan331
NiceMan331

ASKER

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 ?
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 ...
great
the query return all values
but , how to create table from that query ?
CREATE TABLE your_calendar_table AS <select>
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.
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 ?
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

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
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
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
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
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.
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

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 ...
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 ?
count(*) will never return NULL, but 0
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
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?
what do you want to do with the procedure? return data?

exactly
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
that one will take more time from me
but i feel shame to let you stay too much
i have to accept your solution