Derive start date and end date from one date column.

[mbp706]

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

 

[billnted]

...and your problem is what exactly? Have you tried Report > Query > Profile tab > SQL radio button > Edit and pasting in your code?

 

[coggerm]

Hey, sometimes we need only the simple function "minimum" and "maximum". But it depends on the report.