DATE IN NEXT ROW ADDED TO CURRENT ROW

[psivyer]

example ........
oc_id issue_id issue_date issue_date (next row date)
10 1234 01\01\2006 10\01\2006
10 1235 10\01\2006 04\02\2006
10 1236 04\02\2006
25 2461 15\01\2006 25\02\2006
25 2462 25\02\2006 01\04\2006
25 2463 01\04\2006

the above is an example where I only want the NEXT issue_date for each oc_id (when the oc_id changes I do not need the next issue_date)
My basic SQL is insufficient to solve this problem

Regards
Phil

 

[bborissov]

SELECT Tbl1.Oc_Id, Tbl1.Issue_Id, Tbl1.IssueDate,
       ISNULL(Tbl2.Oc_Id,Tbl1.Oc_Id)         AS Tbl2_Oc_Id,
       ISNULL(Tbl2.Issue_Id, Tbl1.Issue_Id)  AS Tbl2_Issue_Id,
       ISNULL(Tbl2.IssueDate,Tbl1.IssueDate) AS Tbl2_IssueDate
FROM MyTable Tbl1
LEFT JOIN MyTable Tbl2 ON Tbl1.Oc_Id = Tbl2.Oc_Id AND Tbl1.IssueId = Tbl2.IssueId+1

not tested at all


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[psivyer]

Thanks for the quick response - not sure how Tbl1 - Tbl2 works?
This would be my starting sql to retrieve the first three columns .....

select oc.oc_id, issue_id, issue_date, from oc
inner join issue on oc.oc_id = issue.oc_id

How does this work with your Tbl1 & Tbl2

Many thanks

Phil

 

[bborissov]

OK, can you post real tables with example data, becuase from question I think that you have only one table.If you have two, maybe this select can be easier to perform

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[psivyer]

There are two tables
one is called oc and the second table is called issue.

oc.oc_id issue.issue_id issue.issue_date "next_row_date"
10 1234 01\01\2006 10\01\2006
10 1235 10\01\2006 04\02\2006
10 1236 04\02\2006
25 2461 15\01\2006 25\02\2006
25 2462 25\02\2006 01\04\2006
25 2463 01\04\2006

For the first three columns my sql is ....
select oc.oc_id, issue_id, issue_date
from oc inner join issue on oc.oc_id = issue.oc_id

It's the 4th column data set that's required
Hope this is clearer

Thanks

Phil

 

[bborissov]

SELECT Tbl1.Oc_Id, Tbl1.Issue_Id, Tbl1.Issue_Date, ISNULL(Tbl2.Issue_Date,'') AS Next_Row_Date,
       Tbl2.Issue_Id AS TblIss
FROM 
    (select  oc.oc_id, issue_id, issue_date
             from oc
             inner join issue on oc.oc_id = issue.oc_id) Tbl1
LEFT JOIN Issue Tbl2 ON Tbl1.Oc_Id = Tbl2.Oc_Id AND 
                        Tbl1.Issue_Id+1 = Tbl2.Issue_Id

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[psivyer]

Thanks for this - it works\ish - but when the issue.issue_id does not have a sequencial number then the next_row_date column returns incorrect date - below is an extract of the data returned from your script ........

oc_id issue_id issue_date next_row_date TblIss
124 5332 1/10/07 1/11/07 5333
124 5333 1/11/07 1/12/07 5334
124 5334 1/12/07 1/1/00 NULL
125 5383 1/2/04 1/5/04 5384
125 5384 1/5/04 1/8/04 5385

... the issue_id 5334 row needs to return 1/2/04 in the next_row_date_column.

Thanks for your help thus far.

Phil

 

[bborissov]

SELECT Tbl1.Oc_Id, Tbl1.Issue_Id, Tbl1.Issue_Date, 
	  (SELECT TOP 1 Issue.Issue_Date FROM Issue 
                  WHERE Oc_Id = Tbl1.Oc_Id AND Issue_Id > Tbl1.issue_Id
                  ORDER BY Issue_Date) AS NewDate
FROM
    (select  oc.oc_id, issue_id, issue_date
             from oc
             inner join issue on oc.oc_id = issue.oc_id) Tbl1

Now I am thinking about other solution

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bborissov]

You could change ORDER BY with Issue_Id if that way will works better.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[psivyer]

Unfortunately - either way still has the same problem.
So near!!

Regards
Phil