Creating date ranges

[praburaj]

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?

[jerome_rajan]

Use 2 stage variables.

Code: Select all

svStart = svEnd
svEnd = lnkSrc.Date (initialize to 0001-01-01)

Assuming that your output columns are as below

Code: Select all

Constraint --> svStart<>0001-01-01

StartDate --> svStart
EndDate -->DateOffsetByComponents(svEnd,0,0,-1)

Hopefully, this should work.

[BI-RMA]

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.

[praburaj]

Thanks for your input. Your input will work only in parallel. I am working server 7.5. Please let me know how to do the same in server

[chulett]

Convert the dates to internal format using IConv() and then compare those internal values. That number is a number of days so to back up one day simply subtract 1 from the internal value and then convert back using OConv().

[ray.wurlod]

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.