|
View previous topic :: View next topic
|
| Author |
Message |
ak47va47
New User
Joined: 23 Dec 2009
Posts: 3
Location: Hyderabad
|
|
|
|
Hi,
I have a daily job which runs from Mon to frid, it checks for any updates in a table, if any would send it to a daily report. We use a PARAM which updates the current date. My query now is to check if any updates are done on sat and sun while running the job on monday with the current date, can anybody suggest me how to check for the prvs to days updates in the table which running the monday job with the current date
Thanks
Kumar. |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Your post does not explain much about your problem and what exactly you are looking for.
| Quote: |
| We use a PARAM which updates the current date |
I believe you meant "PARMeter"?
| Quote: |
| suggest me how to check for the prvs to days updates in the table |
Does the bold text mean,"the previous two days"?
Does the table have any "date column"? |
|
| Back to top |
|
|
ak47va47
New User
Joined: 23 Dec 2009
Posts: 3
Location: Hyderabad
|
|
|
|
Hi,
Yes it means parameters and its prvs two days. The table does have a date column.
Thanks
Kumar. |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
In the start of program SET host-variable TO CURRENT DATE-2 DAYS (please check the exact syntax yourself).
Then select all rows updated on that date or later. |
|
| Back to top |
|
|
ak47va47
New User
Joined: 23 Dec 2009
Posts: 3
Location: Hyderabad
|
|
|
|
Hi Kjeld,
Your suggestion works good on Monday, but from tues to frid, we need only current date, so how would it work on these days? |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| On other days don't subtract 2 from CURRENT DATE ... |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
Hi Anuj,
You beat me to it...  |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Beating a System Analyst wasn't easy, Kjeld ... |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
Maybe for Michael Jackson.  |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
Yeah, he beat me to the final resting place  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
You could use dayofweek()
| Code: |
| current_date - case when dayofweek(current date) = 2 then 2 else 0 end days |
|
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
Should it not be:
| Code: |
| current_date - case when dayofweek(current date) = 6 then 2 else 0 end days |
as dayofweek function returns 1 for Sundays. An alternative could be using dayofweek_iso, which returns 1 for Mondays, and in which case the test value should be 5.
Which function you use will depend on your site's preferences, I guess most European sites will prefer dayofweek_iso. |
|
| Back to top |
|
|
Kylash V
New User
Joined: 13 Feb 2010
Posts: 5
Location: Chennai
|
|
|
|
Hi ,
I guess the logic explained above would work fine if Monday was a working day . Consider a scenario where Monday is a holiday and the batch actually would start only on Tuesday for that week.
So, you would require to report updates that happened on Sat and Sun to your table only on Tuesday's batch.
I do not have a work-around for this currently. But, wanted to point out this if applicable to your system |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Many organizations (especially manufacturing and service organizations) have a "calendar subsystem" for this kind of requirement.
The calendar "knows" all of the "business days" and this is used to determine if "today" is a business day and how many non-business days preceeded "today". This calendar is also used to calculate how many business days there were/are between "today" and some past/future date.
These calendar processes are implemented using database, vsam, or other means. They are usually a/some callable routine(s) rather than included inline. They usually include the capability to add non-business days to accomodate things like a blizzard, hurricane, or other situation that causes some day(s) to become non-business days.
This is not done at an individua program level but rather for an entire application or may even be for the whole organization (rare). |
|
| Back to top |
|
|
|
|
