?
Solved

Compare date in current record with date in previous record

Posted on 2004-11-25
7
Medium Priority
?
394 Views
Last Modified: 2008-03-10
Hi Experts,

I have an SQL table as follows:

Ord #      Item #      Dept      Step      Step_Completion_Date
123         ABC          Packing  1             20041115
123         ABC          QC         2             20041120
123         ABC          QA         3             20041125
456         DEF          Filling     10           20041116
456         DEF          QC         20           20041117
456         DEF          QA         30           20041121

I am looking for the difference between the date field in the current record and date field in the previous record so I can measure 'turnaround' time for the departments. I have been trying various subselect statements but cannot figure it out.

Ideally I would like a view that contained:
Ord#, Item#, Dept, Step_compl_date, Previous_step_comp_date.

Any help greatly appreciated.

TIA
Tom
0
Comment
Question by:tomasdebrun
7 Comments
 
LVL 16

Assisted Solution

by:muzzy2003
muzzy2003 earned 400 total points
ID: 12674059
Something like this might do what you want. You will need to fix the columns in the subquery's join to the main query - don't know if you'd want to include [Item#] as well.

SELECT c.[Ord#], c.[Item#], c.Dept, c.Step_compl_date,
(SELECT MAX(Step_compl_date) FROM Table WHERE [Ord#] = c.[Ord#] AND Step_compl_date < c.Step_compl_date) Previous_step_compl_date
FROM Table
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12674061
Sorry - typo:

SELECT c.[Ord#], c.[Item#], c.Dept, c.Step_compl_date,
(SELECT MAX(Step_compl_date) FROM Table WHERE [Ord#] = c.[Ord#] AND Step_compl_date < c.Step_compl_date) Previous_step_compl_date
FROM Table c
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12674179

select y.ord, y.item, y.dept, y.Step_completion_date, z.Step_completion_date as prev_completion_date
from (
select x.ord, x.Item, x.Dept, x.Step_Completion_date, max(x.Step) as Step
from (
select a.Ord, b.Item, b.Dept, b.Step_completion_date , c.Step
from ( select distinct ord, max(step) as step from #sometable group by ord ) as a
join #sometable b on (b.ord = a.ord and b.step = a.step )
join #sometable c on (c.ord = a.ord and c.Step < a.Step)
) as x
group by x.ord, x.item, x.dept, x.Step_Completion_date
)  as y
join #sometable  z on (z.ord = y.ord and z.Step = y.Step)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:tomasdebrun
ID: 12674788
Thanks Muzzy2003 for your help, the select statement worked a charm for me, the only issue i found with the statement occured when two consecutive steps had the same completion date. The value returned was NULL,I resolved this using the 'Step' field in the statement as the preceding step is alway less then the current step i.e.

SELECT c.[Ord#], c.[Item#], c.Dept, c.Step_compl_date,
(SELECT MAX(Step_compl_date) FROM Table WHERE [Ord#] = c.[Ord#] AND Step< c.Step) Previous_step_compl_date
FROM Table c

I also tried KarinLoos suggestion, unfortunately I kept getting error 'Views or Functions are not allowed on temporary tables'.

Kind Rgds,
Tom
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 400 total points
ID: 12676067
if you want to line a record up with it's previous record try this :


select * from #MyTable curr left outer join #MyTable prev
on curr.ord = prev.ord
and curr.item = prev.item
and prev.step = (select max(step) from #MyTable where ord = curr.ord and item = curr.item and step < curr.step)

if you only want the time stamp, you can get it somewhat simpler,

select *,
(select Step_Completion_date from #MyTable Prev where curr.ord = prev.ord
and curr.item = prev.item and  prev.step = (select max(step) from #MyTable where ord = curr.ord and item = curr.item and step < curr.step))
from #MYTable curr
0
 

Author Comment

by:tomasdebrun
ID: 12676342
This really is excellent stuff, I have a comprehensive list now to work with, just a small digression regarding BillAn1's 2nd suggestion, the statement needs an AS XYZ added to it i.e.

select *,
(select Step_Completion_date from #MyTable Prev where curr.ord = prev.ord
and curr.item = prev.item and  prev.step = (select max(step) from #MyTable where ord = curr.ord and item = curr.item and step < curr.step))

AS XYZ

from #MYTable curr

thank you all for your help, I have decided to split the points between BillAn1 and Muzzy2003 I hope this is appropriate

Thanks
Tom
 
0
 
LVL 11

Expert Comment

by:sparab
ID: 15078789
Good one
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question