Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Derive start date and end date from one date column.

Status
Not open for further replies.

mbp706

Programmer
Dec 2, 2006
1
US
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
 
...and your problem is what exactly? Have you tried Report > Query > Profile tab > SQL radio button > Edit and pasting in your code?
 
Hey, sometimes we need only the simple function "minimum" and "maximum". But it depends on the report.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top