Date Diff.......Different Line Items

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Diff.......Different Line Items

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-10-25 : 09:08:48

Hello all. I could really use some help with this one.
I've pasted two queries below.
The first returns the date the part was ordered
The second returns the date the part was received
Both will have the same repair x_repair_id or [Repair Order]

I'm trying to calculate (in days) the amount of time that passed between ordering the part and receiving the part.

The problem is, I can't use tmp or parameter tables

SELECT DISTINCT table_x_repair_header.x_id_number AS [Repair Order],
table_x_repair_detail.x_serial_no AS [Serial No],
table_x_data_audit_3.x_to_value AS [Status Chaged To],
table_x_data_audit_3.x_rec_insert_date AS [Status Change Date]
FROM table_x_repair_header WITH (NOLOCK)
INNER JOIN table_x_repair_detail WITH (NOLOCK) ON table_x_repair_header.objid = table_x_repair_detail.x_repair_detail2x_repair_header
INNER JOIN table_x_work_order WITH (NOLOCK) ON table_x_repair_detail.objid = table_x_work_order.x_work_order2x_repair_detail
INNER JOIN table_mod_level WITH (NOLOCK) ON table_x_work_order.x_work_order2mod_level = table_mod_level.objid
INNER JOIN table_part_num WITH (NOLOCK) ON table_mod_level.part_info2part_num = table_part_num.objid
INNER JOIN table_x_data_audit_3 WITH (NOLOCK) ON table_x_work_order.objid = table_x_data_audit_3.x_data_audit_32x_work_order
WHERE (table_x_data_audit_3.x_field_name = 'x_status') AND (table_x_data_audit_3.x_object_name = 'x_work_order')
AND (table_x_repair_header.x_company LIKE '%sanofi%') AND (table_x_data_audit_3.x_to_value) = 'Ordered'

SELECT DISTINCT table_x_repair_header.x_id_number AS [Repair Order],
table_x_repair_detail.x_serial_no AS [Serial No],
table_x_data_audit_3.x_to_value AS [Status Chaged To],
table_x_data_audit_3.x_rec_insert_date AS [Status Change Date]
FROM table_x_repair_header WITH (NOLOCK)
INNER JOIN table_x_repair_detail WITH (NOLOCK) ON table_x_repair_header.objid = table_x_repair_detail.x_repair_detail2x_repair_header
INNER JOIN table_x_work_order WITH (NOLOCK) ON table_x_repair_detail.objid = table_x_work_order.x_work_order2x_repair_detail
INNER JOIN table_mod_level WITH (NOLOCK) ON table_x_work_order.x_work_order2mod_level = table_mod_level.objid
INNER JOIN table_part_num WITH (NOLOCK) ON table_mod_level.part_info2part_num = table_part_num.objid
INNER JOIN table_x_data_audit_3 WITH (NOLOCK) ON table_x_work_order.objid = table_x_data_audit_3.x_data_audit_32x_work_order
WHERE (table_x_data_audit_3.x_field_name = 'x_status') AND (table_x_data_audit_3.x_object_name = 'x_work_order')
AND (table_x_repair_header.x_company LIKE '%sanofi%') AND (table_x_data_audit_3.x_to_value) = 'Received'

I really apreciate any help

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-10-26 : 03:47:22

Please post DDL, sample data, and expected result.
Also please explain why you can't use tmp or parameter tables.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 04:09:11

make them both as derived tables.
join over necessary columns.
use DATEDIFF to calcute timespan.

E 12°55'05.25"
N 56°04'39.16"

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-10-26 : 08:58:08

I would like to calculate the difference between the

x_rec_insert_date ""when the x_to_value changed to 'Ordered'""
and the
x_rec_insert_date ""when the x_to_value changed to 'Received'""

The problem is a new record is inserted every time the x_to_value changes

I can't calculate the difference because they are different line items

I can't use TMP or Parameter tables because the "web login" for the application is not allowed to create tables.

Example
0000063 | HP TC4200 KEYBOARD(SCRAP) | Ordered | THenderson | 4/3/2007 12:53:35 PM

0000063 | HP TC4200 KEYBOARD(SCRAP) | Received | KeWilliams | 4/4/2007 11:56:29 AM

The expected result would be the difference between the two dates in red

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-10-26 : 08:58:57

Peso,

Thank you, I'm not familiar with derived tables. I appreciate your patience

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-10-28 : 21:38:38

SELECT DISTINCT A.x_id_number AS [Repair Order],
B.x_serial_no AS [Serial No],
E.x_to_value AS [Status Chaged To],
E.x_rec_insert_date AS [Status Change Date],
F.x_to_value AS [Status Chaged To],
F.x_rec_insert_date AS [Status Change Date],
DATEDIFF(day, F.x_rec_insert_date, E.x_rec_insert_date) AS [Date Difference]
FROM table_x_repair_header A
INNER JOIN table_x_repair_detail B ON A.objid = B.x_repair_detail2x_repair_header
INNER JOIN table_x_work_order C ON B.objid = C.x_work_order2x_repair_detail
INNER JOIN table_mod_level D ON C.x_work_order2mod_level = D.objid
INNER JOIN table_part_num ON D.part_info2part_num = table_part_num.objid
INNER JOIN table_x_data_audit_3 E ON C.objid = E.x_data_audit_32x_work_order AND (E.x_field_name = 'x_status') AND (E.x_object_name = 'x_work_order')
AND (E.x_to_value) = 'Ordered'
INNER JOIN table_x_data_audit_3 F ON C.objid = F.x_data_audit_32x_work_order AND (F.x_field_name = 'x_status') AND (F.x_object_name = 'x_work_order')
AND (F.x_to_value) = 'Received'
WHERE A.x_company LIKE '%sanofi%'

You may want to change the last join to LEFT OUTER JOIN.
In this case you have to take care of NULL value for F.x_rec_insert_date.

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-11-02 : 11:29:10

Thank you very much for your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources