Home » SQL & PL/SQL » SQL & PL/SQL » DATE functions in a weekly report
DATE functions in a weekly report [message #242929] Tue, 05 June 2007 09:29 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I have a batch job that runs everyday. I've been tasked to modify the batch job to produce a report every Monday morning.
The report should produce a summary of data for the previous week. i.e. the week before the Monday (Monday to Sunday).

I tried to do this and would like some feedback from you experts. I have a feeling there is a better/easier way to do this. So please if you can suggest a more easier way then please let me know.

Here is what i've done..

- Everyday the batch job runs it will check whether the current day is a Sunday. If it is then it will produce the report
- The end date will be the current sysdate i.e. Sunday being the end of the week.
- The start date is calculated by subtracting 6 from the end date. (To get the first day of the week - i.e. Monday)
- Subtracting 6 will be a problem if we are on the first week of any month because we might get the date for the previous month. The report should only produce the summary for the week in the current month so If we are in the first week of the month the start date will be the first day of the month regardless of what date the begining of the week is.

Being new to PL/SQL (and programming) im wondering whether you think this will work and will it work everyday every month and every year (including leap years).

Could you have a look and let me know if i should change anything or you think something might cause problems later in teh future.

Thanks in advance.

PROCEDURE weekly report
IS  
lv_start_date DATE;
lv_end_date DATE;
BEGIN
    IF To_Char(To_Date(SYSDATE),'DAY')='SUNDAY' THEN  --Check for end of week
                 
        -- Get last day of the week         
        lv_end_Date:=SYSDATE;        
        -- Get first day of the week (check to be within current month)
        
        IF TRUNC((TO_DATE(SYSDATE)-6),'MM')=TRUNC((TO_DATE(SYSDATE)),'MM') THEN
          -- not gone to previous month
          lv_start_date:=TO_DATE(SYSDATE)-6);
        ELSE
          -- gone to previous month so just use first day of the month
          lv_start_date:=TRUNC(SYSDATE,'MONTH');
        END IF;
        
      -- Run report
      stats_package.producestats(lv_start_date,lv_end_Date);
    ELSE
      -- Not ready to produce report
    END IF;
END weekly report;
Re: DATE functions in a weekly report [message #242956 is a reply to message #242929] Tue, 05 June 2007 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is less readable but I'd wrote it:
IF ... THEN
  stats_package.producestats(greatest(trunc(sysdate,'MM'),trunc(sysdate)-6),sysdate);
END IF;
...

Regards
Michel
Re: DATE functions in a weekly report [message #242957 is a reply to message #242929] Tue, 05 June 2007 10:58 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
function trunc(sysdate,'MONTH') returns first day of the month.
function next_day(trunc(sysdate),'MON')-7 returns the date of previous Monday

You can make use of these for your requirement

Assuming report_date is the colum for date filter,
add the following conditions to the original repot's where clause

report_date between greatest(trunc(sysdate,'MONTH'),next_day(trunc(sysdate),'MON')-7) and trunc(sysdate) and
to_char(sysdate,'DAY') ='MONDAY' and
(next_day(trunc(sysdate),'MON')-7) -greatest(trunc(sysdate,'MONTH')) > 0




Now If you run the report any time it runs only on Monday and returm only previos weeks data


Regards

Techno
Re: DATE functions in a weekly report [message #243003 is a reply to message #242929] Tue, 05 June 2007 13:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ziggy25 wrote on Tue, 05 June 2007 10:29


TRUNC((TO_DATE(SYSDATE)-6),'MM')=TRUNC((TO_DATE(SYSDATE)),'MM')



Please note that SYSDATE is already a DATE. Using it in a case like this can cause unwanted side-effects or incorrect evaluations.
Re: DATE functions in a weekly report [message #243146 is a reply to message #242956] Wed, 06 June 2007 04:22 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Michel Cadot wrote on Tue, 05 June 2007 10:58
It is less readable but I'd wrote it:
IF ... THEN
  stats_package.producestats(greatest(trunc(sysdate,'MM'),trunc(sysdate)-6),sysdate);
END IF;
...

Regards
Michel



Hi,

Could you explain this further. Thanks
Re: DATE functions in a weekly report [message #243150 is a reply to message #243146] Wed, 06 June 2007 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which part don't you understand?

Regards
Michel
Re: DATE functions in a weekly report [message #243151 is a reply to message #242929] Wed, 06 June 2007 04:31 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Quote:

function trunc(sysdate,'MONTH') returns first day of the month.
function next_day(trunc(sysdate),'MON')-7 returns the date of previous Monday

You can make use of these for your requirement



I can see how these can be used.

Quote:


Assuming report_date is the colum for date filter,
add the following conditions to the original repot's where clause

report_date between greatest(trunc(sysdate,'MONTH'),next_day(trunc(sysdate),'MON')-7) and trunc(sysdate) and
to_char(sysdate,'DAY') ='MONDAY' and
(next_day(trunc(sysdate),'MON')-7) -greatest(trunc(sysdate,'MONTH')) > 0

Now If you run the report any time it runs only on Monday and returm only previos weeks data




This has confused me slightly as im unsure how to add it. You mentioned to add it on the original reports's where clause but i dont understand what you mean by that.

The report that gets called is in another package and is called using the following

stats_package.producestats(lv_start_date,lv_end_Date);


It takes in two dates in the parameters. This same report get used from other places to produce the same report with different dates e.g. monthly or yearly report. To produce the report taht i want i have to put in the two dates between a week.

If i understood you correctly you want me to change the actual report and add the "WHERE" clause. I dont think this is possible as it will mean the report will only produce weekly reports.
Re: DATE functions in a weekly report [message #243152 is a reply to message #243003] Wed, 06 June 2007 04:35 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
joy_division wrote on Tue, 05 June 2007 13:03
ziggy25 wrote on Tue, 05 June 2007 10:29


TRUNC((TO_DATE(SYSDATE)-6),'MM')=TRUNC((TO_DATE(SYSDATE)),'MM')



Please note that SYSDATE is already a DATE. Using it in a case like this can cause unwanted side-effects or incorrect evaluations.


Thanks for that. Ill change it.
Re: DATE functions in a weekly report [message #243168 is a reply to message #243150] Wed, 06 June 2007 05:46 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Michel Cadot wrote on Wed, 06 June 2007 04:28
Which part don't you understand?

Regards
Michel




Im assuming that the IF statement will include a check that its sunday. so it would be

IF To_Char(To_Date(SYSDATE),'DAY')='SUNDAY' THEN  --Check for end of week
  stats_package.producestats(greatest(trunc(sysdate,'MM'),trunc(sysdate)-6),sysdate);
END IF;



A few questions
what does the greatest function do?
How does it check that the trunc(sysdate)-6 doesnt go past the current month at that it should use the first day of teh month if it does.

Thanks in advance
Re: DATE functions in a weekly report [message #243169 is a reply to message #243168] Wed, 06 June 2007 05:49 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Could someone also help with the following

How can i check that if the sunday falls on the first day of the month? If this happens the above code wont produce the report for that week. For example, The last Sunday in June is 24th. The next Sunday will be in July which means i will miss the data for 25th to 30th of June.

Thanks
Re: DATE functions in a weekly report [message #243172 is a reply to message #243168] Wed, 06 June 2007 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what does the greatest function do?

When I posted I assumed you are able to read the documentation: GREATEST:
Quote:
GREATEST returns the greatest of the list of one or more expressions.


Quote:
How does it check that the trunc(sysdate)-6 doesnt go past the current month at that it should use the first day of teh month if it does

This is the purpose of GREATEST.

Regards
Michel

Re: DATE functions in a weekly report [message #244637 is a reply to message #243169] Wed, 13 June 2007 10:17 Go to previous message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
ziggy25 wrote on Wed, 06 June 2007 05:49
Could someone also help with the following

How can i check that if the sunday falls on the first day of the month? If this happens the above code wont produce the report for that week. For example, The last Sunday in June is 24th. The next Sunday will be in July which means i will miss the data for 25th to 30th of June.

Thanks


anyone?
Previous Topic: Difference between 2 database objects
Next Topic: Different day result from PL/SQL and SQL
Goto Forum:
  


Current Time: Fri Dec 02 12:45:31 CST 2016

Total time taken to generate the page: 0.11674 seconds