i have a table (see below) with ord

[ktucci]

i have a table (see below) with orderids and dates...the orderids have multiple occurences...i need the date diff between the current record in that orderid and the preceeding record in the orderid for each orderid record within the that orderid group...i am trying to build a report in crystal to do this and i am thing i will probably need to restructure the table before i can get any usefull data out of this

orderid que_level prior_que_level dt
-----------------------------------------------------
1042 200 0 3/9/2001
1042 5 0 2/23/2001
1042 4 0 2/22/2001

1043 8 0 2/23/2001
1043 2 0 2/23/2001
1044 300 0 2/24/2001
1044 6 0 2/23/2001
1044 5 0 2/23/2001
1044 4 0 2/22/2001

 

[tlbroadbent]

Here is on possibility. It involves creating a view that finds the maximum date per order. Although not necesary it makes the next query simpler.

Create View vMaxOrderDates AS

select OrderID, max(dt) As MaxDate From OrderTbl
Group By OrderID
Go

The following query selects the current (or maximum date) and date prior to that date for each order. It also calculates the difference between the dates.

Select a.OrderID, a.MaxDate As CurrDate, b.PrevDate,
datediff(day,b.PrevDate,a.MaxDate) As DtDiff
From vMaxOrderDates a Inner Join
(Select OrderID, max(dt) As PrevDate From OrderTbl c
Where dt<(Select d.MaxDate From vMaxOrderDates d Where d.OrderID=c.OrderID)
Group By OrderID) b On a.OrderID=b.OrderID Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[ktucci]

thanks...very close...but i think i misworded my explanantion a bit...i need the diff between the previous record int hat same order...something like this

the idea was to see how many days it takes to get from one level to another level within any given orderid


orderid level dt diff
---------------------------------------------
2 1 3/1/01 0
2 2 3/5/01 4
2 7 3/15/01 10

4 1 3/7/01 0
4 3 3/10/01 3
4 5 3/21/01 11

 

[tlbroadbent]

The query example that I provided should do that unless there is something more than OrderID that identifies an order. Not the joins on OrderID as well as the Where clause in the correlated subquery that has OrderID as criteria. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[ktucci]

this is the outout from your query...it returns the diff between the maxdate and the next date...but there may be 10 occurences is one order with different dates and i need the difference between the occurence 1 and 2 then 2 and 3 then 3 and 4 and so on for each order...then startover for the next orderid...the output i am looking for should look like my last reply...thanks again...you are very helpful...


OrderID CurrDate PrevDate DtDiff
----------- --------------------------- --------------------------- -----------
2 2001-02-22 00:00:00.000 2001-02-16 00:00:00.000 6
15 2001-02-23 00:00:00.000 2001-01-17 00:00:00.000 37
16 2001-01-29 00:00:00.000 2001-01-25 00:00:00.000 4
17 2001-01-23 00:00:00.000 2001-01-22 00:00:00.000 1
18 2001-03-14 00:00:00.000 2001-02-13 00:00:00.000 29

 

[tlbroadbent]

I think I understand the requirement now. Here is my latest suggestion.

Select a.OrderID, a.Lvl, Dt,
isnull(Datediff(day,(select max(b.Dt)
from OrderTbl b
where b.OrderID=a.OrderID
and b.Lvl<a.Lvl
and b.Dt<a.Dt
group by b.OrderID),Dt),0) As Diff
From OrderTbl a Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[ktucci]

works great...thanks a million, your help was greatly appreciated

keith