I'm using Impromptu 7.3 and need to implement an SQL statement that uses self joins in the where clause to find the start date and end date for a model. A sample of the SQL:
SELECT
a.asset_id
,a.asset_identification_number
,aac.asset_attribute_constant_value AS model
,aaa.effective_date AS start_date
,aaa3.effective_date-'1 DAY' AS end_date
FROM
asset_ a
INNER JOIN
asset_asset_attribute_ aaa
ON a.asset_id = aaa.asset_id
INNER JOIN
asset_asset_attribute_ aaa3
ON a.asset_id = aaa3.asset_id
INNER JOIN
asset_attribute_ aa
ON aaa.asset_attribute_id = aa.asset_attribute_id
INNER JOIN
asset_attribute_type_ aat
ON aat.asset_attribute_type_id = aa.asset_attribute_type_id
INNER JOIN
asset_attribute_constant_ aac
ON aac.asset_attribute_id = aa.asset_attribute_id
WHERE
aat.asset_attribute_type_code = 'MODEL'
AND aaa3.effective_date =
(
SELECT
MIN(aaa2.effective_date)
FROM
asset_asset_attribute_ aaa2
WHERE
aaa2.asset_id = aaa.asset_id
AND a.asset_id = aaa.asset_id
AND aaa2.effective_date > aaa.effective_date
)
thanks
SELECT
a.asset_id
,a.asset_identification_number
,aac.asset_attribute_constant_value AS model
,aaa.effective_date AS start_date
,aaa3.effective_date-'1 DAY' AS end_date
FROM
asset_ a
INNER JOIN
asset_asset_attribute_ aaa
ON a.asset_id = aaa.asset_id
INNER JOIN
asset_asset_attribute_ aaa3
ON a.asset_id = aaa3.asset_id
INNER JOIN
asset_attribute_ aa
ON aaa.asset_attribute_id = aa.asset_attribute_id
INNER JOIN
asset_attribute_type_ aat
ON aat.asset_attribute_type_id = aa.asset_attribute_type_id
INNER JOIN
asset_attribute_constant_ aac
ON aac.asset_attribute_id = aa.asset_attribute_id
WHERE
aat.asset_attribute_type_code = 'MODEL'
AND aaa3.effective_date =
(
SELECT
MIN(aaa2.effective_date)
FROM
asset_asset_attribute_ aaa2
WHERE
aaa2.asset_id = aaa.asset_id
AND a.asset_id = aaa.asset_id
AND aaa2.effective_date > aaa.effective_date
)
thanks