calculating an end date in a query 2

[robo100]

I have spent ages looking at this and it is starting to bug me.

Does anyone know of a way I can calculated the end date in a table with a date and end date field so the data would return as follows.

Date End Date
27/9/03 28/9/03
29/9/03 29/9/03
30/9/03 1/10/03
2/10/03

As you can see the end date for the prior record is the date before this record and on the maximum date the end date field is null.

Any help with this would be gratefully appreciated.

 

[sem]

SELECT "Date"
, (LEAD( "Date", 1) OVER (ORDER BY "Date&quot)-1 AS "Next Date"
FROM <table>

Regards, Dima

 

[robo100]

works like a charm. thank you very much

 

[BJCooperIT]

Very helpful, sem!

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[robo100]

ok just to elaborate a bit. I am trying to bring in other fields to group by and it starts going a bit wrong.

The data should look like this

date item_code ean store end_date
26/6 12345 345 1
27/6 12345 346 1 28/6
29/6 12345 346 1 30/6
1/7 12345 346 1

When I add group by it puts an end date on the first item even though it is a different ean and when I try to do it in a table for multiple stores it puts an end date on every field.

I am going to have a look at it but any help would again be greatfully appreciated.

Many thanks

 

[sem]

Can you provide table structure with some sample data as well as query that fails? I'm not sure about your task, but maybe this one may solve it:


select &quot;date&quot;
, item_code
, ean
, (lead(&quot;date&quot;,1) over (partition by item_code, ean order by &quot;date&quot)-1 end_dt
from <table>

Regards, Dima

 

[robo100]

here is the table structure

date date/time
store number
ean varchar(2)
end_date date/time

obviously all the fields apart from end date are populated.

some sample data would be

date store ean end_date
1/11/03 1 123
1/11/03 1 124 1/11/03
2/11/03 1 124 4/11/03
5/11/03 1 124
30/10/03 2 345 1/11/03
2/11/03 2 345
3/11/03 2 346 5/11/03
6/11/03 2 346 7/11/03
8/11/03 2 346

To sum up we need the end date to be null for the last ean on every store and before that it needs to be the date before for the previous records.

Hope this is of assistance.

Thanks very much for your help.

 

[sem]

Can you explain a bit more about item field? Is it within the same table? According to your last post it is not. Do you need it in result? If so, where can you get it?

Regards, Dima

 

[robo100]

the item field would be included in the table but the actual calculation should be based on the ean. An item can have more than one ean, therefore as ean is the unique field I thought I should only include that. I can include item as a reference field only later.

 

[sem]

So are you not satisfied with query provided?

select &quot;date&quot;
, store
, ean
, (lead(&quot;date&quot;,1) over (partition by store, ean order by &quot;date&quot)-1 end_dt
from <table>

Regards, Dima

 

[robo100]

i am just about to try it