View previous topic :: View next topic |
Author |
Message |
srini_igsi Beginner
Joined: 12 May 2006 Posts: 41 Topics: 16 Location: Mumbai
|
Posted: Tue Aug 01, 2006 5:05 am Post subject: How to get the Current Date while Unloading data ? |
|
|
Hi,
Our Client is a Health Insurance sponsorer and more than 2 million members are registered under diff health plans.
Our client is having two groups (Group A and Group B).
Group A benefit period is 1/1/2006 (start dt) - 12/31/2006 (end dt).
Group B benefit period is 2/1/2006 start dt) - 1/31/2007 (end dt).
The requirement is 'Allow reporting of prior and current benefit periods for a given member during a "transition" period (Jan - March, 90 days from the expiration of the benefit period)'.
Lets say that we have entered into the month of Feb 2007 (transition period), so in this month inorder to extract the records up to feb of 2007, the following condition is suposed to be given.
'Benefit start date <current>= current date' will extract all the records up to Feb of 2007.
We have an Unload JCL, which is supposed to extract the records satisfying the said condition. So how can I include the said condition in the control card of the JCL. _________________ Thanks,
-Srini |
|
Back to top |
|
|
shekar123 Advanced
Joined: 22 Jul 2005 Posts: 528 Topics: 90 Location: Bangalore India
|
Posted: Tue Aug 01, 2006 6:44 am Post subject: |
|
|
srini_igsi,
Can you not hard code the current date in the query ?
Code: |
Benefit_start_date = '2006-08-01'
|
_________________ Shekar
Grow Technically |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
|
Back to top |
|
|
srini_igsi Beginner
Joined: 12 May 2006 Posts: 41 Topics: 16 Location: Mumbai
|
Posted: Tue Aug 01, 2006 7:23 am Post subject: |
|
|
The condition should be as follows.
Benefit start date <= current date
and
(Benefit end date + 90 days) >= current date
Pl let me know if there is an alternative to achieve the same. _________________ Thanks,
-Srini |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Tue Aug 01, 2006 7:50 am Post subject: |
|
|
srini_igsi,
You can code the condition as in in your unload jcl. For some reason the sql gets truncated here . So I am not able to post it. Sorry
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
srini_igsi Beginner
Joined: 12 May 2006 Posts: 41 Topics: 16 Location: Mumbai
|
Posted: Tue Aug 01, 2006 11:55 am Post subject: |
|
|
Kolusu,
All that I wanted to know is converting the said pseudocode into logic.
Benefit start date <= current date and
(Benefit end date + 90 days) >= current date.
I have to place this logic inside the control card of the unload JCL, which extracts
all the records satsfying the criteria.. _________________ Thanks,
-Srini |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Tue Aug 01, 2006 12:57 pm Post subject: |
|
|
Srini,
As I mentioned earlier you can simply code it as shown below in your control card. (assuming that benefit_start_date and benefit_end_date columns are defined as Date Columns)
Code: |
SELECT *
FROM TABLE
WHERE Benefit_start_date <= Current date
AND (Benefit_end_date + 90 days) >= Current date
|
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
srini_igsi Beginner
Joined: 12 May 2006 Posts: 41 Topics: 16 Location: Mumbai
|
Posted: Wed Aug 02, 2006 1:27 am Post subject: |
|
|
Thanq very much Kolusu.
It's working perfectly.
The reason for posting this Q is, initially I thought that the function 'Current Date' may not be allowed inside the control card. _________________ Thanks,
-Srini |
|
Back to top |
|
|
|
|