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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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 ...
ASKER
great
the query return all values
but , how to create table from that query ?
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('1 1-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.
(select to_date('01-sep-2013','dd-
connect by to_date('01-sep-2013','dd-
left outer join
daily_sales ds ON md.missing_date=DS.ep_date
in the above example 01-sep-2013 &11-sep-2013 are start and end dates respectively.
ASKER
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 ?
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":
or you do it like this:
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
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 )
ASKER
S
run this code gives all dates
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
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
the code is correct, it's now about data.
without seeing some data, impossible to fix is quickly
ASKER
i repair the code to the following
it works well now
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)
it works well now
ASKER
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
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.
the "alarm" part is what you need to decide on how you want to get the alarm.
ASKER
yes
i need something like this
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
yes, presuming that the function "message" is already existing:
but the SELECT in the procedure will not work like this ...
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;
but the SELECT in the procedure will not work like this ...
ASKER
if ( v_count > 0 ) then
is this will handle the null ?
mean if there are no data , it will return 0 ?
count(*) will never return NULL, but 0
ASKER
it gives me this errors
and this one :
an INTO caluse is expected in this select statement
message ('there are ' ||v_count ||' days missing ' );
i repair it to dbms_output.putlineand this one :
select * from tran_data;
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?
ASKER
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
http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php
ASKER
that one will take more time from me
but i feel shame to let you stay too much
i have to accept your solution
but i feel shame to let you stay too much
i have to accept your solution
ASKER
i don't have ,
i will create it
but , is there any procedure could add new value for all dates since starting ?