Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
I'm not sure about your example either, but if you want to look at something from another row in a given query subset you can use the lead/lag analytic tools. Here are two links that describes the lead/lag functions as they relate to the partition by clause.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Start time and end time is stored seperately in two different records and I would need to calculate time difference. Shipment_Id values identifies relation between records.
QUestion is,
I need to know SQL statement to generate this result. Can you give me a sample SQL.
Start time and end time is stored seperately in two different records and I would need to calculate time difference. Shipment_Id values identifies relation between records.
QUestion is,
I need to know SQL statement to generate this result. Can you give me a sample SQL.
Write the query without the lead/lag, is the row that you are looking for immediately preceding or following the row that you are comparing to? Is the offset always the same? According to the links you need to know the row offset.
Okay... I'm not doing your job but I'll gave you a jump start...
Code:
select a.shipment_id as SHIPMENT,
a.created_dttm as TYPE_5,
b.created_dttm as TYPE_10
from your_table a,
your_table b
where a.shipment_id = b.shipment_id
and a.message_type = 5
and b.message_type = 10
order by a.shipment_id
/
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.