February 12, 2010 at 10:00 am
Problem:
For each repair order I need to reference the previous repair for the same part and serial number. I need to calculate the usage hours that have elapsed since the last repair. Usage fields are not shown in the query
I'm using 2 sub-selects with row_number() function and doing a left join on the key fields plus the row_number with the second sub-select having the row_number incremented by 1.
The results of the left join is acting like an inner join. I can't figure out why looking for a suggestion.
See both result sets below the second shows what the results should look like, this was obtained by using temp tables that were populated with the results of each sub-select then joined using a left join on the same fields.
SELECT
*
FROM
(SELECT
RepairSentDate
, partcodesid
, RepairSerialNumber
, ROW_NUMBER() OVER (PARTITION BY partcodesid, RepairSerialNumber
ORDER BY partcodesid, RepairSerialNumber,
RepairSentDatekey) AS rownum
FROM
ro.factRepairOrders
WHERE
RepairSerialNumber Is NOT NULL) cur
LEFT JOIN (SELECT
repairsentdate
,partcodesid
,RepairSerialNumber
,ROW_NUMBER() OVER (PARTITION BY partcodesid, RepairSerialNumber
ORDER BY partcodesid, RepairSerialNumber
, RepairSentDatekey) + 1 AS rownum
FROM
ro.factRepairOrders
WHERE
RepairSerialNumber Is NOT NULL) prev
ON cur.PartCodeSID = prev.PartCodeSID
AND cur.RepairSerialNumber = prev.RepairSerialNumber
AND cur.rownum = prev.rownum
From the query above - Results shown here should have a rownum #1 in the left and show a null in the right.
2001-11-12 00:00:00.0001177558240-722000-08-10 00:00:00.0001177558240-72
2004-04-14 00:00:00.0001177558240-732001-11-12 00:00:00.0001177558240-73
2005-05-16 00:00:00.0001177558240-742004-04-14 00:00:00.0001177558240-74
When I take each subquery and load into temp tables then do the exact same join I get the following correct result
2000-08-10 00:00:00.0001177558240-71NULLNULLNULLNULL
2001-11-12 00:00:00.0001177558240-722000-08-10 00:00:00.0001177558240-72
2004-04-14 00:00:00.0001177558240-732001-11-12 00:00:00.0001177558240-73
2005-05-16 00:00:00.0001177558240-742004-04-14 00:00:00.0001177558240-74
February 12, 2010 at 10:54 am
Update:
The reason I couldn't use the temp tables is because I needed to update the table.
What I did is created a temp table for the right side of the join and used my sub-select for the left and then the left join worked properly and I'm also able to do the update.
Would still like to know why the left join didn't work with 2 sub-selects....
February 12, 2010 at 2:36 pm
Never mind.
Apparently when you don't sort your query you can't expect the results to always come back in the same order.
All the records I was missing were at the bottom of the result set.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply