Hi,
In one of my job my date format like below
2011-07-03
2011-08-03
2011-09-03
2011-10-03
2011-11-03
Now I need to compare the data between the current and previous data.
I need to take first row date(2011-07-03) as my start date and the next row date need to load as a end date before that I have minus one date for that (2011-08-02)
so output will be
Start Date End Date
2011-07-03 2011-08-02
2011-08-03 2011-09-02
2011-09-03 2011-10-02
2011-10-03 2011-11-02
2011-11-03 2011-11-03
Can anyone help me how to do this logic?
Creating date ranges
Moderators: chulett, rschirm, roy
Creating date ranges
prabakaran.v
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Use 2 stage variables.
Assuming that your output columns are as below
Hopefully, this should work.
Code: Select all
svStart = svEnd
svEnd = lnkSrc.Date (initialize to 0001-01-01)
Code: Select all
Constraint --> svStart<>0001-01-01
StartDate --> svStart
EndDate -->DateOffsetByComponents(svEnd,0,0,-1)
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
The described logic does not work for the last row. Even in the example provided by praburaj I doubt that the suggested logic is really what he wants. I'd expect the last row to have an end date open infinitely.
But the job will need some special handling to deal with this condition. Use the LastRow() or LastRowInGroup()-functions to set the appropriate value.
But the job will need some special handling to deal with this condition. Use the LastRow() or LastRowInGroup()-functions to set the appropriate value.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Sort in reverse order and use stage variables to remember the previous row. Effecting your change is now straightforward. Sort back to ascending order if needed.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.