compare child records - t-sql

I have four records, each containing a date.
LineNo ShipDate RefLine
--
1 1/30/05 <null>
2 1/1/05 1
3 1/15/05 1
I want to compare the date in line 1 to 2, then 2 to 3
Basically, the lines belong to the same order. What is going on is
when we want to change a ship date for an order line, we make a copy of
the original line (line 1 in this case), close the copied line out
(line 2 or 3) and reference the original line in it using a
user-defined field (labeled RefLine). The user then goes back to the
original line and changes the ship date. We are trying to track how
many times an orderline is pushed out, and calculate the days between.
The reason we cant just make a new line and set the date there has to
do with links in the original that can't be easily changed. basically
I want my query to look like:
LineNo ShipDate RefLine DaysMoved
---
1 1/30/05 <null> 15
2 1/1/05 1 <null> (this was org ship date)
3 1/15/05 1 15
---
Total 30
I can get the group total just by getting the difference between the
current and min dates, but in my report if I sum the over all using
this method I get 60 days because there are two detail lines.
Anyone help is appreciated...I figured it out. Here is the query I used:
SELECT
co.CUSTOMER_ID
, co.ORDER_DATE
, c.NAME AS 'CUSTOMER_NAME'
, cl.CUST_ORDER_ID AS 'ORDER_ID'
, cl.LINE_NO
, cl.ORDER_QTY
, cl.PART_ID
, m.LINE_NO AS 'MOVE_LINE_NO'
, m.DESIRED_SHIP_DATE
, m.REASON_CODE
, m.REASON_CODE_DESCRIPTION
, (SELECT TOP 1 DESIRED_SHIP_DATE FROM RV_MOVED_SHIP_DATES WHERE
order_id = cl.CUST_ORDER_ID AND
line_no > m.line_no AND
COPIED_FROM_LN = cl.LINE_NO AND
m.DESIRED_SHIP_DATE <
DESIRED_SHIP_DATE) AS 'NEXT_SHIP_DATE'
, cl.DESIRED_SHIP_DATE AS 'CURRENT_DATE'
, m.COPIED_FROM_LN
FROM
dbo.CUSTOMER_ORDER co
INNER JOIN
dbo.CUST_ORDER_LINE cl ON co.ID = cl.CUST_ORDER_ID
INNER JOIN
dbo.CUSTOMER c ON co.CUSTOMER_ID = c.ID
INNER JOIN
dbo.RV_MOVED_SHIP_DATES m ON cl.LINE_NO =
m.COPIED_FROM_LN AND
cl.CUST_ORDER_ID = m.ORDER_ID
WHERE
co.ORDER_DATE BETWEEN @.START_DATE AND @.END_DATE
ORDER BY
co.CUSTOMER_ID, cl.CUST_ORDER_ID, cl.LINE_NO, m.LINE_NO
Stephen wrote:
> I have four records, each containing a date.
> LineNo ShipDate RefLine
> --
> 1 1/30/05 <null>
> 2 1/1/05 1
> 3 1/15/05 1
> I want to compare the date in line 1 to 2, then 2 to 3
> Basically, the lines belong to the same order. What is going on is
> when we want to change a ship date for an order line, we make a copy of
> the original line (line 1 in this case), close the copied line out
> (line 2 or 3) and reference the original line in it using a
> user-defined field (labeled RefLine). The user then goes back to the
> original line and changes the ship date. We are trying to track how
> many times an orderline is pushed out, and calculate the days between.
> The reason we cant just make a new line and set the date there has to
> do with links in the original that can't be easily changed. basically
> I want my query to look like:
> LineNo ShipDate RefLine DaysMoved
> ---
> 1 1/30/05 <null> 15
> 2 1/1/05 1 <null> (this was org ship date)
> 3 1/15/05 1 15
> ---
> Total 30
> I can get the group total just by getting the difference between the
> current and min dates, but in my report if I sum the over all using
> this method I get 60 days because there are two detail lines.
> Anyone help is appreciated...